Mysql – How to avoid MySQL ‘Deadlock found when trying to get lock; try restarting transaction’

deadlockMySQL

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

Best Answer

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

Related Topic