Lock wait timeout exceeded; try restarting transaction

Mysql errors

[eltreno]

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]

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)