Call us Toll-Free:
1-800-218-1525
Live ChatEmail us

 Sponsors

MySQL InnoDB Deadlocks and Duplicate key errors (1213, 1205, 1062)

Mike Peters, 05-05-2010
InnoDB is one of the best MySQL storage engines when you're looking for concurrent writes, transactions support and ACID reliability.

Switching from MyISAM to InnoDB is very straightforward (alter table MYTABLE engine=innodb), but there are a few pitfalls you should be prepared for.

Review the full list of InnoDB restrictions, understand them and make sure nothing collides with your existing design... Watch out for SELECT COUNT(*) on InnoDB - they require full table scans.

As part of this post I'd like to touch on three key issues with InnoDB that many developers fail to properly deal with:

#1062 Duplicate entry for key 'PRIMARY' error

This is bug 26316 that has been around for quite some time and is still alive and kicking as of version 5.1.43 of MySQL that we're using.

Not sure if it is really linked to triggers or not. Out of nowhere, MySQL will throw a 1062 duplicate key error on auto_increment keys. Something which by design is impossible.

To fix, you have to retry the transaction and it will go through on the second attempt.

#1213 Deadlock found when trying to get lock error

InnoDB locks rows and starts transactions internally as needed.

From time to time, particularly when concurrent threads are hitting the same rows, you're going to experience a deadlock.

Deadlocks happen when two transactions wait on each other to acquire a lock. For example:

Tx 1: lock A, then B
Tx 2: lock B, then A

Because InnoDB starts transactions on the internally, you -are- going to experience deadlocks.

No way of escaping it.

Fortunately when deadlocks do happen with this error 1213, all you have to do is retry the query until it goes through.

#1205 Lock wait timeout exceeded error

Similar to error 1213, this one will occur whether or not you are manually starting a transaction.

This error is more complicated to deal with -

If the transaction was triggered by InnoDB (atomic to the query you're running), you can simply retry the query and it will eventually go through.

If however this error shows up inside a transaction you started, it means any uncommitted inserts/updates are lost and will have to be resent in a new transaction block.

--

Below are two simple PHP MySQL wrapper functions you can use to gracefully handle the three issues I described, while logging all errors to a file.


function DBRead($query, $link_identifier=0)
{
 
// Read query (SELECT)
 
if ($link_identifier)
   
$result = mysql_query($query, $link_identifier);
  else
   
$result = mysql_query($query);

 
// Return result
 
return $result;
}

function
DBWrite($query, $link_identifier=0)
{
 
// If any of these error codes is returned by MySQL, we'll retry
 
$arr_need_to_retry_error_codes = array
                  (
                   
1213// Deadlock found when trying to get lock
                   
1205    // Lock wait timeout exceeded
                 
);

 
// Initialize
 
$cnt_retry = 0;
 
$error_str ="";

 
// Main loop
 
do
  {
   
// Initialize 'flag_retry' indicating whether or not we need to retry this transaction
   
$flag_retry = 0;

   
// Write query (UPDATE, INSERT)
   
if ($link_identifier)
    {
     
$result = mysql_query($query, $link_identifier);
     
$mysql_errno  = mysql_errno($link_identifier);
     
$mysql_error  = mysql_error($link_identifier);
    }
    else
    {
     
$result = mysql_query($query);
     
$mysql_errno  = mysql_errno();
     
$mysql_error  = mysql_error();
    }

   
// If failed,
   
if (!$result)
    {
       
// Determine if we need to retry this transaction -
        // If duplicate PRIMARY key error,
        // or one of the errors in 'arr_need_to_retry_error_codes'
        // then we need to retry
       
$flag_retry = (($mysql_errno==1062 &&
             
strpos($mysql_error,"for key 'PRIMARY'")!==false) ||
             
in_array($mysql_errno, $arr_need_to_retry_error_codes)) ;

       
// If this was error 1205, log it
       
if ($mysql_errno==1205)
       
DBLogError($query, "Error #1205 detected - review application logic", $link_identifier);
    }

   
// If successful or failed but no need to retry
   
if ($result || empty($flag_retry))
    {
     
// We're done
     
break;
    }

   
// If we're up to here this means that -
    // Error occured, wait 1 second before we try again
   
sleep(1);
   
$cnt_retry++;

   
// If we already retried 10 times, log error
   
if ($cnt_retry>=10)
    {
     
$result  = 0;
     
$error_str = "Retried $cnt_retry times due to error ".
           
$mysql_errno." and finally gave up";
      break;
    }

  } while (
1);

 
// If update query failed, log
 
if (!$result)
  {
   
DBLogError($query, $error_str, $link_identifier);
  }

 
// Return result
 
return $result;
}

function
DBLogError($query="", $msg="", $link_identifier=0)
{
 
// Set these for easier access
 
if (!empty($link_identifier))
  {
   
$mysql_errno = mysql_errno($link_identifier);
   
$mysql_error = mysql_error($link_identifier);
  }
  else
  {
   
$mysql_errno = mysql_errno();
   
$mysql_error = mysql_error();
  }

 
// Format 'msg' if we have it
 
if (!empty($msg)) $msg = "$msg";
  else
$msg = "Error #".$mysql_errno." $mysql_error";

 
// Log
 
$file = @fopen("/usr/tmp/dbwrite_error.log","a");
  @
fwrite($file, date("Y-m-d h:i:s")." ".$_SERVER['REQUEST_URI'].
 
": $msg; Query was: $query; ".
 
$_SERVER['SCRIPT_FILENAME'].")\r\n");
  @
fclose($file);
}

Matt, 03-19-2012
Great post. I recently began encountering lock wait timeouts and deadlock issues on a system that's been in production for two years. This was exactly what we needed to fix the problem. Well, this and better table indexing!
Enjoyed this post?

Subscribe Now to receive new posts via Email as soon as they come out.

 Comments
Post your comments












Note: No link spamming! If your message contains link/s, it will NOT be published on the site before manually approved by one of our moderators.



About Us  |  Contact us  |  Privacy Policy  |  Terms & Conditions