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->connect($dsn, $DbConfig::username, $DbConfig::password, { RaiseError => 1, AutoCommit => 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:

1. What exactly about my situation is causing the error above?

2. 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.

Fortney

To see what table is being deadlocked and some of the data that's causing it, run SHOW ENGINE INNODB STATUS. – Lindly

Answer 1

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.

Fulgurous

What are the error codes that we need to catch then? Is catching 1205 alone sufficient? There are over 900 error codes in dev.mysql.com/doc/refman/5.7/en/error-messages-server.html . How do you know all the codes that we need to catch to implement a proper solution for your try/catch suggestion? – Pharyngo

Do this mean that other than InnoDB or any row-level transactional RDBMS don't have these problems? – Mattie

@Pacerier, I believe 1213 is also a must - it happens to me more frequently than 1205. See mariadb.com/kb/en/mariadb-error-codes . Maybe 3058 should be retried too. – Kerguelen

Answer 2

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->prepare($strsql);
my $db_res=0;
while($db_res==0)
{
   $db_res=1;
   try{$sth->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 -

Attractant

Answer 3

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

#

Kamp

This solved all of my issues with saving ActiveRecord associations in multithreaded environments. – Cantwell

Enabling innodb_locks_unsafe_for_binlog may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled. – Sustain

Answer 4

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
    }
}

Dereism

Unfortunately it is likely that most users encountering this are dealing with multiple machines or processes dumping data into a single MySQL instance. Row level locking in the application is simply not an option for most users. – Labannah