homepage Welcome to WebmasterWorld Guest from 54.237.78.165
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Lock wait timeout exceeded; try restarting transaction
Mysql errors
eltreno

10+ Year Member



 
Msg#: 4091328 posted 11:11 am on Mar 4, 2010 (gmt 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

 

whoisgregg

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



 
Msg#: 4091328 posted 9:56 pm on Mar 23, 2010 (gmt 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)
  • Global Options:
     top home search open messages active posts  
     

    Home / Forums Index / Code, Content, and Presentation / Databases
    rss feed

    All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
    Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
    WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
    © Webmaster World 1996-2014 all rights reserved