How to avoid mysql 'Deadlock found when trying to get lock; try restarting 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).


Deadlock happen when two transactions wait on each other to acquire a lock. Example:

  • Tx 1: lock A, then B
  • Tx 2: lock B, then A

There are numerous questions and answers about deadlocks. Each time you insert/update/or delete a row, a lock is acquired. To avoid deadlock, you must then make sure that concurrent transactions don't update row in an order that could result in a deadlock. Generally speaking, try to acquire lock always in the same order even in different transaction (e.g. always table A first, then table B).

Another reason for deadlock in database can be missing indexes. When a row is inserted/update/delete, the database needs to check the relational constraints, that is, make sure the relations are consistent. To do so, the database needs to check the foreign keys in the related tables. It might result in other lock being acquired than the row that is modified. Be sure then to always have index on the foreign keys (and of course primary keys), otherwise it could result in a table lock instead of a row lock. If table lock happen, the lock contention is higher and the likelihood of deadlock increases.


It is likely that the delete statement will affect a large fraction of the total rows in the table. Eventually this might lead to a table lock being acquired when deleting. Holding on to a lock (in this case row- or page locks) and acquiring more locks is always a deadlock risk. However I can't explain why the insert statement leads to a lock escalation - it might have to do with page splitting/adding, but someone knowing MySQL better will have to fill in there.

For a start it can be worth trying to explicitly acquire a table lock right away for the delete statement. See LOCK TABLES and Table locking issues.

Tags:

Mysql

Deadlock

Recent Posts