Working around MySQL error Deadlock found when trying to get lock try restarting transaction

Question

I have a MySQL table with about 5 000 000 rows that are being constantly updated in small ways by parallel Perl processes connecting via DBI   The table has about 10 columns and several indexes   One fairly common operation gives rise to the following error sometimes   DBD  mysql  st execute failed  Deadlock found when trying to get lock  try restarting transaction at Db pm line 276    The SQL statement that triggers the error is something like this   UPDATE file table SET a lock    process-1234  WHERE param1    X  AND param2    Y  AND param3    Z  LIMIT 47   The error is triggered only sometimes  I d estimate in 1  of calls or less  However  it never happened with a small table and has become more common as the database has grown   Note that I am using the a lock field in file table to ensure that the four near-identical processes I am running do not try and work on the same row  The limit is designed to break their work into small chunks   I haven t done much tuning on MySQL or DBD  mysql  MySQL is a standard Solaris deployment  and the database connection is set up as follows   my  dsn    DBI mysql database      DbConfig  database     host   DbConfig  hostname  port   DbConfig  port    my  dbh   DBI- gt connect  dsn   DbConfig  username   DbConfig  password    RaiseError   gt  1  AutoCommit   gt  1    or die  DBI  errstr    I have seen online that several other people have reported similar errors and that this may be a genuine deadlock situation   I have two questions    What exactly about my situation is causing the error above  Is there a simple way to work around it or lessen its frequency  For example  how exactly do I go about  restarting transaction at Db pm line 276     Thanks in advance

User · Answer

The idea of retrying the query in case of Deadlock exception is good, but it can be terribly slow, since mysql query will keep waiting for locks to be released. And incase of deadlock mysql is trying to find if there is any deadlock, and even after finding out that there is a deadlock, it waits a while before kicking out a thread in order to get out from deadlock situation.

What I did when I faced this situation is to implement locking in your own code, since it is the locking mechanism of mysql is failing due to a bug. So I implemented my own row level locking in my java code:

private HashMap<String, Object> rowIdToRowLockMap = new HashMap<String, Object>();
private final Object hashmapLock = new Object();
public void handleShortCode(Integer rowId)
{
    Object lock = null;
    synchronized(hashmapLock)
    {
      lock = rowIdToRowLockMap.get(rowId);
      if (lock == null)
      {
          rowIdToRowLockMap.put(rowId, lock = new Object());
      }
    }
    synchronized (lock)
    {
        // Execute your queries on row by row id
    }
}

User · Answer

If you are using InnoDB or any row-level transactional RDBMS  then it is possible that any write transaction can cause a deadlock  even in perfectly normal situations   Larger tables  larger writes  and long transaction blocks will often increase the likelihood of deadlocks occurring   In your situation  it s probably a combination of these   The only way to truly handle deadlocks is to write your code to expect them   This generally isn t very difficult if your database code is well written   Often you can just put a try catch around the query execution logic and look for a deadlock when errors occur   If you catch one  the normal thing to do is just attempt to execute the failed query again   I highly recommend you read this page in the MySQL manual   It has a list of things to do to help cope with deadlocks and reduce their frequency

User · Answer

Note that if you use SELECT FOR UPDATE to perform a uniqueness check before an insert  you will get a deadlock for every race condition unless you enable the innodb locks unsafe for binlog option  A deadlock-free method to check uniqueness is to blindly insert a row into a table with a unique index using INSERT IGNORE  then to check the affected row count   add below line to my cnf file  innodb locks unsafe for binlog   1     1 - ON 0 - OFF

User · Answer

The answer is correct  however the perl documentation on how to handle deadlocks is a bit sparse and perhaps confusing with PrintError  RaiseError and HandleError options   It seems that rather than going with HandleError  use on Print and Raise and then use something like Try Tiny to wrap your code and check for errors   The below code gives an example where the db code is inside a while loop that will re-execute an errored sql statement every 3 seconds   The catch block gets    which is the specific err message  I pass this to a handler function  dbi err handler  which checks    against a host of errors and returns 1 if the code should continue  thereby breaking the loop  or 0 if its a deadlock and should be retried      sth    dbh- gt prepare  strsql   my  db res 0  while  db res  0        db res 1     try  sth- gt execute  param1  param2       catch             print  caught    in insertion to hd item upc for upc  upc n           db res dbi err handler              if  db res  0  sleep 3            dbi err handler should have at least the following   sub dbi err handler       my  message            if  message   m DBD  mysql  st execute failed  Deadlock found when trying to get lock  try restarting transaction                 caught 1          retval 0    we ll check this value and sleep re-execute if necessary           return  retval      You should include other errors you wish to handle and set  retval depending on whether you d like to re-execute or continue    Hope this helps someone -

[mysql] Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction"

Examples related to mysql

Examples related to deadlock

Examples related to database-deadlocks