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.

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

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

Solution 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