Mysql – Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction

One easy trick that can help with most deadlocks is sorting the operations in a specific order.

You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:

• connection 1: locks key(1), locks key(2);
• connection 2: locks key(2), locks key(1);

If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.

Now, if you changed your queries such that the connections would lock the keys at the same order, ie:

• connection 1: locks key(1), locks key(2);
• connection 2: locks key(1), locks key(2);

it will be impossible to get a deadlock.

So this is what I suggest:

1. Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.

2. Fix your delete statement to work in ascending order:

Change

DELETE FROM onlineusers 
WHERE datetime <= now() - INTERVAL 900 SECOND

To

DELETE FROM onlineusers 
WHERE id IN (
    SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND 
    ORDER BY id
) u;

Another thing to keep in mind is that MySQL documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).

This is problem of lock contention, which ultimately result in a time-out on one of the lock. Here are a few suggestions:

• Make sure you have the correct indexes which result in row-level locks not table-level lock. This will reduce the contention.
• Make sure you have indexes on the foreign key constraints. To check the relational constraints during insert or update, some database lock the whole referenced table if there is no such index (don't know if this is the case of MySQL)
• If problem is still here, try to make the transaction faster/smaller. Again, this will reduce the contention on the database.
• Increase the timeout but keep the value reasonable

Also check if you are obtaining two connections. Because of that two transaction will get mixed and you will get above error!!!

Also check this link.. this might help you...