How to solve the MySQL deadlock problem (detailed examples)

This article brings you relevant knowledge about mysql, which mainly introduces the analysis and discussion of common deadlock cases, and how to avoid deadlocks as much as possible. Suggestions, hope it helps everyone.

Recommended study: mysql tutorial

1. What is deadlock

Deadlock is a concurrent system Common problems will also appear in the concurrent read and write request scenario of the database MySQL. A "deadlock" occurs when two or more transactions are waiting for each other to release the locks they already hold or are waiting for lock resources in a loop due to inconsistent locking sequences. The common error message is Deadlock found when trying to get lock....

For example, transaction A holds the X1 lock and applies for the X2 lock, transaction B holds the X2 lock and applies for the X1 lock. Transactions A and B hold locks and apply for the locks held by the other party and wait in a loop, causing a deadlock.

As shown in the picture above, the resource requests of the four cars on the right caused a loop phenomenon, that is, an infinite loop, resulting in a deadlock.

From the definition of deadlock, several factors for deadlock in MySQL are:

1. Two or more transactions

2. Each transaction already holds a lock and applies for a new lock

3. The lock resource can only be held by the same transaction at the same time or is incompatible

4. Transactions wait for each other in a loop due to holding locks and applying for locks

2. InnoDB lock type

In order to analyze deadlock, we have It is necessary to have an understanding of InnoDB's lock types.

MySQL InnoDB engine implements standard row-level locks: shared lock (S lock) and exclusive lock (X lock)

1. Different transactions can add S locks to the same row of records at the same time.

2. If a transaction adds an X lock to a certain row of records, other transactions cannot add an S lock or an X lock, resulting in lock waiting.

If transaction T1 holds the S lock of row r, then when another transaction T2 requests the lock of r, the following processing will be done:

1. T2 requested the S lock and was allowed immediately. As a result, T1 and T2 both hold the S lock of row r

2. T2 requested the X lock and could not be allowed immediately

If T1 holds the X lock of r, then T2's request for r's X and S locks cannot be allowed immediately. T2 must wait for T1 to release the The locks are not compatible. The compatibility of shared locks and exclusive locks is as follows:

2.1. Gap lock (gap lock)

Gap lock locks a gap to prevent insertion . Assume that the index column has three values ​​​​2, 4, and 8. If 4 is locked, the two gaps (2,4) and (4,8) will also be locked at the same time. Other transactions cannot insert records with index values ​​between these two gaps. However, there is an exception to the gap lock:

1. If the index column is a unique index, then only this record will be locked (only row locks will be added), not the lock. gap.

2. For a joint index and it is a unique index, if the where condition only includes part of the joint index, gap locks will still be added.

2.2, next-key lock

next-key lock is actually a combination of the gap lock in front of the row lock record. Assuming that there are index values ​​10, 11, 13 and 20, then the possible next-key locks include:

(negative infinity, 10], (10, 11], (11, 13], ( 13,20], (20, positive infinity)

Under the RR isolation level, InnoDB uses next-key lock mainly to prevent phantom reading problems.

2.3, Intention lock(Intention lock)

In order to support multi-granularity locking, InnoDB allows row locks and table locks to exist at the same time. In order to support locking operations at different granularities, InnoDB supports additional A locking method called Intention Lock. Intention lock divides the locked objects into multiple levels. Intention lock means that the transaction wants to lock at a finer granularity. Intention lock is divided into two types:

1. Intention shared lock (IS): The transaction intends to add shared locks to certain rows in the table

2. Intentional exclusive lock (IX): The transaction intends to add exclusive locks to certain rows in the table

Since the InnoDB storage engine supports row-level locks, intention locks do not actually Blocks any request except a full table scan. The compatibility of table-level intent locks and row-level locks is as follows:

2.4. Insert Intention lock (Insert Intention lock)

The insertion intention lock is a gap lock set before inserting a row of records. This lock releases a signal of the insertion method, that is, multiple When transactions are inserted into the same index gap, they do not need to wait for each other unless they are inserted into the same position in the gap. Assume that a column has index values ​​2 and 6. As long as the insertion positions of the two transactions are different (for example, transaction A inserts 3 and transaction B inserts 4), then they can be inserted at the same time.

2.5. Lock mode compatibility matrix

The horizontal direction is the lock that is held, and the vertical direction is the lock being requested:

3. Reading Deadlock log

Before conducting specific case analysis, let us first understand how to read the deadlock log, and use the information in the deadlock log as much as possible to help us solve the deadlock problem.

The database scenario of the following test cases is as follows:MySQL 5.7 transaction isolation level is RR

The table structure and data are as follows:

The test examples are as follows:

You can view the log of the latest deadlock by executing show engine innodb status.

3.1. The log analysis is as follows:

1.***** (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read

The transaction number is 2322, Active for 6 seconds, starting index read indicates that the transaction status is reading data according to the index. Other common statuses are:

mysql tables in use 1 indicates that the current transaction uses a table.

locked 1 means there is a table lock on the table, for DML statements it is LOCK_IX

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

LOCK WAIT means it is waiting for the lock, 2 lock struct(s) means trx->trx_locks The length of the lock list is 2. Each linked list node represents a lock structure held by the transaction, including table locks, record locks, and auto-increment locks. In this use case, 2locks represents IX locks and lock_mode X (Next-key lock)

1 row lock(s) represents the number of row record locks/gap locks held by the current transaction.

MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

MySQL thread id 37 means that the thread ID that executes the transaction is 37 (that is, the ID displayed by show processlist;)

delete from student where stuno= 5 indicates the sql being executed by transaction 1. The uncomfortable thing is that show engine innodb status cannot view the complete sql. It usually displays the sql currently waiting for the lock.

 ***** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

RECORD LOCKS represents record locks. This content indicates that transaction 1 is waiting for the X lock of idx_stuno on table student. In this case, it is actually Next-Key Lock.

The log of transaction 2 is similar to the above analysis:

2.***** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

Shows that the insert of transaction 2 into student(stuno,score) values(2,10) holds a=5 Lock mode X

LOCK_gap, but we cannot see the delete from student where stuno=5 executed by transaction 2 from the log;

This also makes it difficult for the DBA to analyze based on the log alone. The root cause of the locking problem.

3.***** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw**** .****student trx id 2321 lock_mode

#4. Classic case analysis

4.1. Transaction concurrent insert unique key conflict

The table structure and data are as follows:

The test examples are as follows:

The log analysis is as follows:

Transaction T2 insert into t7(id,a) values ​​(26,10) statement insert is successful, holding a=10

Exclusive row lock (Xlocks rec but no gap)

1. Transaction T1 insert into t7(id,a) values ​​(30,10), because the first insert of T2 has already inserted the record a=10, transaction T1 insert a=10 will cause a unique key conflict, and you need to apply for a unique key for the conflict Index plus S Next-key Lock (ie lock mode S waiting) This is a
gap lock
2. will apply to lock the gap area between (,10], (10,20].

3. Transaction T2 insert into t7(id,a) values ​​(40,9) The value of a=9 inserted by this statement is between gap lock 4-10 applied for by transaction T1, so the second insert statement of transaction T2 needs to wait for the S-Next-key Lock lock of transaction T1 to be released, and the lock_mode X locks gap before rec insert intention waiting is displayed in the log.

4.2. Concurrency deadlock problem of updating first and then inserting

The table structure is as follows, no data:

Test examples are as follows:

Deadlock analysis:
You can see that two transaction update records that do not exist have obtained gap locks (gap locks) one after another. , gap locks are compatible so they will not block during the update process. Both hold gap locks and then compete to insert the intention lock. When there are other sessions holding gap locks, the current session cannot apply for the insertion intention lock, resulting in a deadlock.

5. How to avoid deadlocks as much as possible

1. Design the index reasonably, put the columns with high distinction in front of the composite index, so that business SQL can pass through the index as much as possibleLocate fewer rows and reduce lock contention.

2. Adjust the execution order of business logic SQL to avoid update/delete SQL that holds locks for a long time in front of the transaction.

3. AvoidLarge transactions and try to split large transactions into multiple small transactions for processing. The probability of lock conflicts in small transactions is also smaller.

4. Access tables and rows in a fixed order. For example, for two transactions that update data, transaction A updates data in the order 1, 2; transaction B updates data in the order 2, 1. This is more likely to cause deadlock.

5. In systems with relatively high concurrency, do not explicitly lock, especially in transactions. For example, the select ... for update statement, if it is in a transaction (start transaction is run or autocommit is set to equal 0) , then the found record will be locked.

6. Try to search for records by primary key/index. Range search increases the possibility of lock conflicts. Do not use the database to do additional quota calculations. For example, some programs will use statements such as "select ... where ... order by rand();". Since statements like this do not use indexes, the entire table's data will be locked.

7. Optimize SQL and table design to reduce the situation of occupying too many resources at the same time. For example, reduce the number of connected tables and decompose complex SQL into multiple simple SQLs.

Recommended learning:

mysql learning tutorial

The above is the detailed content of How to solve the MySQL deadlock problem (detailed examples). For more information, please follow other related articles on the PHP Chinese website!