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
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:
Two or more transactions
Each transaction already holds a lock and applies for a new lock
The lock resource can only be held by the same transaction at the same time or is incompatible
Transactions wait for each other in a loop due to holding locks and applying for locks
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)
Different transactions can add S locks to the same row of records at the same time.
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:
T2 requested the S lock and was allowed immediately. As a result, T1 and T2 both hold the S lock of row r
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:
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:
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.
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.
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.
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:
Intention shared lock (IS): The transaction intends to add shared locks to certain rows in the table
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:
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.
The horizontal direction is the lock that is held, and the vertical direction is the lock being requested:
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.
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 analysis4.1. Transaction concurrent insert unique key conflictThe 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=10Transaction 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.
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.
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
.
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.
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.
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.
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.
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.
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.
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!