How to avoid MySQL 'Deadlock found when trying to get lock; try restarting transaction'

deadlock, mysql

Solution

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:

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.

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

Problem

I have a innoDB table which records online users. It gets updated on every page refresh by a user to keep track of which pages they are on and their last access date to the site. I then have a cron that runs every 15 minutes to DELETE old records. I got a 'Deadlock found when trying to get lock; try restarting transaction' for about 5 minutes last night and it appears to be when running INSERTs into this table. Can someone suggest how to avoid this error? === EDIT === Here are the queries that are running: First Visit to site: ``` INSERT INTO onlineusers SET ip = 123.456.789.123, datetime = now(), userid = 321, page = '/thispage', area = 'thisarea', type = 3 ``` On each page refresh: ``` UPDATE onlineusers SET ips = 123.456.789.123, datetime = now(), userid = 321, page = '/thispage', area = 'thisarea', type = 3 WHERE id = 888 ``` Cron every 15 minutes: ``` DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND ``` It then does some counts to log some stats (ie: members online, visitors online).

Original source