MySQL Error:deadlock found when trying to get lock; Try restarting transaction

When using MySQL, we sometimes encounter such an error: "Deadlock found when trying to get lock; Try restarting transaction ".

In 14.5.5.3 how to Minimize and Handle deadlocks There is such a sentence:

Deadlocks is not dangerous. Just try again.

The deadlock is not dangerous, just try again.

In fact, this suggestion is very practical.

Let's review the four conditions in which the deadlock occurs:

1. The exclusive nature of a resource (at most one transaction can be accessed at a time);
2. Request and hold (after the transaction gets to the lock, it is not released voluntarily);
3. Inalienable (a transaction cannot acquire a lock owned by another transaction);
4. The loop waits (that is, transaction a gets to the lock Lock1, waits for Lock2, while transaction B gets to the lock Lock2, waits for Lock1, at which point the transaction A and B loops to wait for each other to release the lock that it needs).

It is important to note that InnoDB does not roll back when most errors occur (for example, waiting for the lock timeout to not roll back);

There is only one exception, that is, when a deadlock occurs, InnoDB rolls back a transaction that has the least impact (directly destroying the 2nd above, the transaction becomes victim). At this point we just retry the victim transaction, and all of the transactions will be successfully committed.

At the same time, 14.5.5.3 how to Minimize and Handle deadlocks in another paragraph is important:

InnoDBUses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations was not really "atomic"; they automatically set locks on the (possibly several) I Ndex Records of the row inserted or deleted.

A insert/delete line can also cause a deadlock.

Because these operations are not atomic, they will be added to the index record lock in execution.

Examples are very well constructed.

For example, there is a table: CREATE TABLE t1 (id int PRIMARY key, name char () key);

There are two session concurrency:

Session 1:

INSERT into T1 (ID, name) VALUES (5, ' Branden ');

Session 2:

SELECT * from t1 where name > ' B ' and ID >3;

Session1 visits name> ' B ', will add X lock on Ben, Bob, and Gap lock between Alex and Ben, Ben and Bob, Bob and Cathy, plus x lock on clustered index ID 1 and 3; next to get clustered index on I D is 6, 7 X lock, and Gap lock between (3, 6), (6, 7).

Session2 first obtains gap lock on the clustered index (3, 6); next try to get gap lock between the name index (Ben, Bob), (Bob, Cathy).

Finally a deadlock occurs.

How to eliminate it? Let the WHERE clause in select first access the ID and then access name.

You can read this article: http://hedengcheng.com/?p=771#_Toc374698321.

In addition, it is necessary to understand the locks required for various statements: 14.5.3 Locks Set by Different SQL statements in InnoDB.

Resources:

MySQL 5.7 Reference Manual:

14.5.2.3 consistent nonlocking Reads

14.5.2.4 Locking Reads

14.5.5 Deadlocks in InnoDB

Stack Overflow:

How to avoid MySQL ' Deadlock found if trying to get lock; Try restarting transaction '

Working around MySQL error "Deadlock found when trying to get lock; Try restarting Transaction "

Understanding the InnoDB Lock (Record,gap,next-key Lock)

MySQL InnoDB lock mechanism gap Lock, Next-key lock, Record lock parsing

MySQL Error:deadlock found when trying to get lock; Try restarting transaction