Welcome to WebmasterWorld Guest from 174.129.151.95

Forum Moderators: open

Message Too Old, No Replies

Lock wait timeout exceeded; try restarting transaction

Mysql errors

   
11:11 am on Mar 4, 2010 (gmt 0)

10+ Year Member



Hi all,

I'm getting this error:
Lock wait timeout exceeded; try restarting transaction

I have a table that I reuse PK rows (replace data that can be deleted) as to stop the PK growing when it's not necessary:


function checkForReusablePKid(){
mysql_query("START TRANSACTION");//start transaction here so another user cannot grab same ID

$sql = 'SELECT id FROM main_table WHERE etc etc LIMIT 1 FOR UPDATE';//find ID that can be reused


$result = mysql_query($sql) or showError();
$num_results = mysql_num_rows($result);
if ($num_results == 1){
while ($myrow = mysql_fetch_object($result)){

$PK_id = $myrow->id;

}

$sql = 'DELETE FROM child_table WHERE id = ' . $PK_id;
$result = mysql_query($sql) or showError();

//now delete from main_table so we can insert this ID again soon
$sql = 'DELETE FROM main_table WHERE id = ' . $PK_id;
$result = mysql_query($sql) or showError();

mysql_query("COMMIT");

return $PK_id;//to be used again

} else {

mysql_query("COMMIT");
return false;
}
}


Now intermittently I get the error "Lock wait timeout exceeded; try restarting transaction"

MySql 5
php 5

So I am starting the transaction to do the grabbing of the ID then delete data, then send ID back to be reused.

Any ideas why I am getting the intermittent error?
Thanks
9:56 pm on Mar 23, 2010 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



If you are getting timeouts, that may mean those queries are taking longer to run than you expect and subsequent users take too long to wait for their turn. A couple options:

  • Make sure you have proper indices in that initial SELECT (on the etc etc fields)
  • Increase the setting for innodb_lock_wait_timeout (or table_lock_wait_timeout, not sure which)
  •