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 tướng 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 lập cập 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 tướng 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 tướng get a deadlock.

So this is what I suggest:

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

2. Fix your delete statement to tướng 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 tướng 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 tướng your client code. (Say, 3 retries on this particular error before giving up).

Deadlock happen when two transactions wait on each other to tướng 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 tướng 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 tướng kiểm tra the relational constraints, that is, make sure the relations are consistent. To vì thế so sánh, the database needs to tướng kiểm tra the foreign keys in the related tables. It might result in other lock being acquired than vãn the row that is modified. Be sure then to tướng 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.

In case someone is still struggling with this issue:

I faced similar issue where 2 requests were hitting the server at the same time. There was no situation lượt thích below:

T1:
    BEGIN TRANSACTION
    INSERT TABLE A
    INSERT TABLE B
    END TRANSACTION

T2:
    BEGIN TRANSACTION
    INSERT TABLE B
    INSERT TABLE A
    END TRANSACTION

So, I was puzzled why deadlock is happening.

Then I found that there was parent child relation ship between 2 tables because of foreign key. When I was inserting a record in child table, the transaction was acquiring a lock on parent table's row. Immediately after that I was trying to tướng update the parent row which was triggering elevation of lock to tướng EXCLUSIVE one. As 2nd concurrent transaction was already holding a SHARED lock, it was causing deadlock.

Refer to: https://blog.tekenlight.com/2019/02/21/database-deadlock-mysql.html

answered Mar 11, 2010 at 16:26

answered Jun 29, 2013 at 14:59

ArchieArchie

5,3411 gold badge33 silver badges38 bronze badges

@Omry Yadan's answer ( https:///a/2423921/1810962 ) can be simplified by using ORDER BY.

Change

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

to

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

to keep the order in which you delete items consistent. Also if you are doing multiple inserts in a single transaction, make sure they are also always ordered by id.

According to tướng the mysql delete documentation:

If the ORDER BY clause is specified, the rows are deleted in the order that is specified.

You can find a reference here: https://dev.mysql.com/doc/refman/8.0/en/delete.html

I have a method, the internals of which are wrapped in a MySqlTransaction.

The deadlock issue showed up for má when I ran the same method in parallel with itself.

There was not an issue running a single instance of the method.

When I removed MySqlTransaction, I was able to tướng lập cập the method in parallel with itself with no issues.

Just sharing my experience, I'm not advocating anything.

answered Dec 5, 2019 at 21:27

Rick JamesRick James

142k14 gold badges138 silver badges242 bronze badges