Full-service Internet Marketing & Web Development
Recent Posts
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);
}
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);
}
|
|
Subscribe Now to receive new posts via Email as soon as they come out.
Comments
Post your comments