preface
We use MySQL most likely will encounter deadlock problem, this is a very headache problem. This article will introduce deadlocks, analyze and discuss common deadlock cases, and give some suggestions on how to avoid deadlocks as much as possible.
What is a deadlock
Deadlock is a common problem in concurrent systems, and also occurs in the scenario of concurrent read and write requests of MySQL database. A “deadlock” occurs when two or more transactions are waiting for each other to release a lock they already hold or are waiting for resources to be locked in a loop because they are not locked in the same order. A common error message is “no Deadlock found when trying to get lock…” .
For example, transaction A holds the X1 lock and applies for the X2 lock, while transaction B holds the X2 lock and applies for the X1 lock. A deadlock occurs when transactions A and B hold the lock and apply the lock held by the other party to wait in A loop.
As shown in the figure above, it is the four cars on the right that are requesting resources that create a loop phenomenon, or an infinite loop, resulting in a deadlock.
From the definition of deadlock, the following factors can occur in MySQL:
A. Two or more transactions
B. Each transaction already holds a lock and requests a new lock
C. Lock resources can only be held by the same transaction or are incompatible
D. Transactions loop around each other because they hold locks and apply for locks
Note: The experimental environment for the subsequent content is version 5.7, and the isolation level is RR (repeatable).
InnoDB lock type —
To analyze deadlocks, it is necessary to have an understanding of InnoDB’s lock types.
MySQL InnoDB engine implements standard row-level locking: shared locking (S lock) and exclusive locking (X lock)
- Different transactions can lock the same row at the same time.
- If one transaction holds an X lock on a row, other transactions cannot hold an S lock or an X lock, resulting in a lock wait.
If transaction T1 holds the S lock on row R, then another transaction T2 requests the lock on row R and does the following:
- T2’s request for the S lock is immediately granted, resulting in both T1 and T2 holding the S lock on row R
- T2 requesting X lock was not allowed immediately
If T1 holds the X lock on R, then T2 cannot request the X and S locks on r immediately. T2 must wait for T1 to release the X lock, because the X lock is incompatible with any locks. The compatibility of shared and exclusive locks is as follows:
Gap lock
A gap lock locks a gap to prevent insertion. If the index column has 2,4, and 8 values, then the gaps (2,4) and (4,8) will also be locked if 4 is locked. Other transactions cannot insert records with index values between these two gaps. However, there is an exception to gap locking:
- If the index column is a unique index, only the record (row only) is locked, not the gap.
- For a federated index that is unique, a gap lock is still applied if the WHERE condition contains only a portion of the federated index.
next-key lock
Next-key lock is essentially a combination of the row lock and the gap lock that precedes the record. Given the index values 10,11,13, and 20, the possible next-key locks include:
(Minus infinity,10)
(10, 11]
(11, 13]
(13, 20]
(20, plus infinity)
InnoDB uses next-key lock in RR isolation to prevent phantom read problems.
Intention lock
InnoDB allows row and table locks in order to support multi-granularity locking. To support locking at different granularity, InnoDB supports an additional locking method called Intention Lock. Intent locking is to divide the locked object into multiple layers. Intent locking means that the transaction wants to be locked at a finer granularity. There are two types of intent locks:
- Intentional shared lock (IS) : A transaction intends to place a shared lock on some rows in a table
- Intentional exclusive lock (IX) : the transaction has the intention to apply exclusive locks on certain rows in the table
Because InnoDB storage engine supports row-level locks, intentional locks don’t actually block any requests other than full table scans. Table level intent locks are compatible with row level locks as follows:
Insert Intention lock
An insert intent lock is a gap lock that is set prior to an insert row operation. This lock releases an insert signal that multiple transactions inserted at the same index gap do not need to wait for each other unless they are inserted at the same place in the gap. Given that A column has index values 2,6, as long as the two transactions are inserted in different places (for example, transaction A is inserted into 3 and transaction B is inserted into 4), then they can be inserted simultaneously.
Lock mode compatibility matrix
Horizontal is the lock held, vertical is the lock being requested:
Read the deadlock log
Note: The Xmen platform allows you to view the deadlock log of the deadlock master library, which can be accessed as follows:
Login xmen.intra.ke.com/#/mysql/mys… Click Cluster Management -> Enter cluster port -> Tools Set -> View Deadlock Log Click Query to view the latest deadlock log.
Before doing a case study, let’s look at how to read the deadlock log and use the information in the deadlock log to help solve the deadlock problem as much as possible.
The database scenario for the following test cases is as follows:
MySQL 5.7 transaction isolation level is RR
Table structure and data are as follows:
For example:
You can run the show engine Innodb status command to view logs about the latest deadlock.
Log analysis is as follows:
*** (1) TRANSACTION:
TRANSACTION 2322, ACTIVE 6 sec starting index read
The transaction number is 2322, and the value is active for 6 seconds. Starting Index read indicates that the transaction state is read from the index. Other common states are:
Mysql tables in Use 1 indicates that the current transaction uses a table.
Locked 1 indicates that there is a table lock on the table, LOCK_IX for DML statements
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
2 LOCK struct(s) TRX ->trx_locks the length of the chain table is 2. Each chain node represents a LOCK structure for the transaction, including table locks, record locks, and increment locks. In this use case, 2LOCKS represents IX locks and lock_mode X (next-key lock)
1 Row Lock (s) Indicates 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 = show processList; MySQL thread ID 37 = show processList; Display ID)
Delete from student where stuno=5 indicates the SQL being executed by transaction 1. Show Engine Innodb status does not show the full SQL, and usually shows the SQL currently awaiting 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 item indicates that transaction 1 is waiting for the X Lock of IDx_STUNo on table Student, which in this case is actually a next-key Lock.
The log for transaction 2 is similar to the above analysis:
*** (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
Insert into Student (stuno,score) values(2,10) holds Lock mode X with a=5
| LOCK_gap, but we can’t see the transaction 2 implementation from the log delete from student where stuno = 5;
This is also the root cause of the difficulty for DBAs to analyze deadlock problems based on logs alone.
*** (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 X locks gap before rec insert intention waiting
Lock_mode X locks gap before REc INSERT intention waiting (LOCK_X + LOCK_REC_gap)
— Classic case analysis
Case 1: Deadlock caused by applying gap lock concurrently
The table structure and data are as follows:
The test uses the following example (in this test case scenario, two transactions delete nonexistent rows and then insert records):
The deadlock log looks like this:
Deadlock log analysis is as follows:
Lock_mode X locks GAP before REc
- T2:delete from t4 where kdt_id=15 and admin_id= 1 and biz=’retail’ and role_id=1; eligible
The record does not exist, causing T2 to be held first (lock_mode X locks gap before REC)
(2,20,1,1, ‘ratail’, 1, 0) – (3,30,1, ‘retail’, 1, 0) in the interval, prevent qualified record insertion.
- Delete on T1 is locked (lock_mode X locks gap but rec)
(2,20,1,’retail’,1,0)-(3,30,1,’retail’,1,0)
- Insert statement T1, insert intention lock and T2 hold X gap (lock_mode X locks gap before REc) conflict, wait for T2 gap lock release.
- T1 has X gap (lock_mode X locks GAP before REc), so T1 is waiting for the gap lock to be released.
In summary, T1 (INSERT) waits for T2 (delete), and T2 (insert) waits for T1 (delete), so a deadlock occurs.
Case 2: Transaction concurrent INSERT unique key conflict
The table structure and data are as follows:
For example:
The deadlock log is as follows:
Log analysis is as follows:
SQL > alter table T2 insert into t7(id,a) values (26,10
locks rec but no gap )
Insert T1 into t7(id,a) values (30,10);
Select * from T1 where a=10; select * from T1 where a=10; select * from T1 where a=10
This is a gap lock that will apply to the gap area between (,10],(10,20).
SQL > insert into t7(id,a) values (40,9
The second insert statement that requires transaction T2 will wait for transaction T1’s S-next-key Lock to be released.
In the log, lock_mode X locks gap before rec insert intention waiting is displayed.
Case 3: Regular indexes and primary keys compete with each other causing a circular wait
The table structure and data are as follows:
For example:
Deadlock log:
Deadlock log analysis:
The first thing to understand is that a lock on the same field is queued.
Secondly, the index IDx_C1 in table TX is a non-unique common index.
T2 PRIMARY of table test.tx TRX ID 2077 lock_mode X locks rec but not gap
(2). The update T1 statements by ordinary index idx_c1 update the c2, first get idx_c1 c1 = 5 X lock lock_mode X locks rec but not gap, and then to apply for the corresponding primary key id = 30 row locks, But T2 already holds the number of rows in the primary key, so T1 waits.
Select * from primary key where id=30; select * from primary key where id=30; Tx TRX ID 2077 lock_mode X locks REc but not gap waiting. Index IDx_C1 of table test.
T2(DELETE) waits for T1(Update), and T1(Update) waits for T2(Select for Update) in a loop, resulting in deadlock.
Case 4: Concurrent update and insert deadlock problem
The table structure is as follows, with no data:
For example:
The deadlock log is as follows:
Deadlock analysis:
You can see that two transaction update records that do not exist acquire gap locks. Gap locks are compatible with each other so that they do not block during the update process. Both hold gap locks and then compete to insert intent locks. When another session holds the GAP lock, the current session cannot apply for the intended lock, resulting in a deadlock.
You can get a copy of ali’s internal Java learning notes + Jin SAN Yin four interview question sharing.
How to avoid deadlocks as much as possible
1. Reasonably design the index, and put the columns with high differentiation in front of the composite index, so that the business SQL can locate as few rows as possible through the index
Lock contentions.
2. Adjust the execution sequence of service logical SQL statements to avoid update/ DELETE SQL statements that hold locks for a long time in front of transactions.
3. Avoid large transactions and try to split large transactions into multiple small transactions. Small transactions are less likely to have lock conflicts.
4. Access tables and rows in a fixed order. For example, if two transactions update data, transaction A updates data in the order of 1,2; things
Service B updates data in the order of 2,1. This is more likely to cause deadlocks.
5. In a system with high concurrency, do not explicitly lock, especially in transactions. Such as the select… for
Update statement, if it is in a transaction (run start transaction or set autoCOMMIT = 0),
The records found are locked.
6. Try to find records by primary key/index, range lookup increases the possibility of lock conflicts, and do not use the database to do some
Extra credit counts work. For example, some programs use “select… The where… Order by rand();”
Statements like this, which do not use indexes, will result in the entire table being locked.
7. Optimize SQL and table design to reduce the use of too many resources at the same time. For example, reducing joined tables would complicate SQL
Decomposed into multiple simple SQL.