Welcome to WebmasterWorld Guest from 23.22.182.29

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)

Junior Member

10+ Year Member

joined:Nov 29, 2004
posts: 66
votes: 0


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)

Senior Member

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

joined:Dec 9, 2003
posts:3416
votes: 0


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)
  •  

    Join The Conversation

    Moderators and Top Contributors

    Hot Threads This Week

    Featured Threads

    Free SEO Tools

    Hire Expert Members