preface
How should we troubleshoot and analyze Mysql deadlock? There is an insert on duplicate deadlock problem on the front line of. Based on this deadlock problem, this paper will share the troubleshooting and analysis process, hoping to be helpful to you.
Deadlock Case recovery
Table structure:
CREATE TABLE `song_rank` ( `id` int(11) NOT NULL AUTO_INCREMENT, `songId` int(11) NOT NULL, `weight` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `songId_idx` (`songId`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Isolation level:
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)
Database version:
+------------+ | @@version | +------------+ | 5.7.21-log | +------------+ 1 row in set (0.00 sec)
Turn off auto submit:
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec)
Data in the table:
mysql> select * from song_rank; +----+--------+--------+ | id | songId | weight | +----+--------+--------+ | 1 | 10 | 30 | | 2 | 20 | 30 | +----+--------+--------+ 2 rows in set (0.01 sec)
Cause of Deadlock:
In the concurrent environment, executing insert into... on duplicate key update... Causes deadlock
Deadlock simulation replay:
Transaction 1 execution:
mysql> begin; //First step Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; //Step two Query OK, 1 row affected (0.00 sec) mysql> rollback; //Step 7 Query OK, 0 rows affected (0.00 sec)
Transaction 2 execution:
mysql> begin; //Step 3 Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; // Step 4 Query OK, 1 row affected (40.83 sec)
Transaction 3 execution:
mysql> begin; //Step 5 Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1; //Step 6
Execution of transaction 1, transaction 2 and transaction 3:
| step | Transaction one | Transaction II | Transaction III |
|---|---|---|---|
| First step | begin; | ||
| Step two | insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; (Query OK, 1 row affected (0.00 sec) ) | ||
| Step 3 | begin; | ||
| Step 4 | insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; // Blocked | ||
| Step 5 | begin; | ||
| Step 6 | insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1; // Blocked | ||
| Step 7 | rollback; | ||
| result | Query OK, 1 row affected (40.83 sec) | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
Deadlock surfaced:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Deadlock investigation and analysis
What should we do when we encounter deadlock? There are several steps
1. View deadlock log
When a deadlock occurs in the database, you can obtain the deadlock log through the following command:
show engine innodb status;
The log of the deadlock problem of the above example insert on duplicate is as follows:
*** (1) TRANSACTION: TRANSACTION 27540, ACTIVE 19 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 23, OS thread handle 14896, query id 582 localhost ::1 root update insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27540 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) TRANSACTION: TRANSACTION 27539, ACTIVE 41 sec inserting, thread declared inside InnoDB 1 mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 22, OS thread handle 6976, query id 580 localhost ::1 root update insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;;
2. Analyze deadlock log
How to analyze deadlock logs? Share my thoughts
- Deadlock log is divided into transaction 1 and transaction 2
- Find out the SQL with deadlock
- Find out what locks the transaction holds and what locks it is waiting for
- SQL lock analysis
Transaction 1 log analysis
From the log, we can see that the executing SQL of transaction 1 is:
insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1
The statement is waiting for the index songid_ Insert intention exclusive lock of IDX:
lock_mode X locks gap before rec insert intention waiting
Transaction 2 log analysis
From the log, we can see that the executing SQL of transaction 2 is:
insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1
The statement holds an index songid_ Clearance lock of IDX:
lock_mode X locks gap before rec
The statement is waiting for the index songid_ Insert intention exclusive lock of IDX:
lock_mode X locks gap before rec insert intention waiting
Supplement to lock related concepts (attached):
Considering that some readers may not be familiar with the above insert intent lock, a section on lock related concepts is added here.
Official documents
InnoDB lock type mind map:
We mainly introduce compatibility and lock mode type locks
1. Shared lock and exclusive lock:
InnoDB implements standard row level locks, including two types: shared lock (s lock for short) and exclusive lock (x lock for short).
- Shared lock (S lock): allows a locked transaction to read a row.
- Exclusive lock (X lock): allows a locked transaction to update or delete a row.
If transaction T1 holds the s lock of row r, when another transaction T2 requests the lock of row r, it will do the following:
- T2 requests that the s lock be allowed immediately. As a result, both T1 and T2 hold the s lock of row r
- Lock request x cannot be allowed immediately
If T1 holds the X lock of r, the X and s locks requested by T2 cannot be allowed immediately. T2 must wait for T1 to release the X lock, because the X lock is incompatible with any lock.
2. Intention lock
- Intentional shared lock (IS lock): the transaction wants to obtain the shared lock of some rows in a table
- Intentional exclusive lock (IX lock): a transaction wants to obtain exclusive locks for certain rows in a table
For example, after transaction 1 adds S lock to table 1, transaction 2 needs to add IX lock to change a row record. Due to incompatibility, it needs to wait for the S lock to be released; If transaction 1 adds an IS lock to table 1, and the IX lock added by transaction 2 IS compatible with the IS lock, it can be operated, which realizes finer grained locking.
The compatibility of locks in InnoDB storage engine is shown in the following table:
| compatibility | IS | IX | S | X |
|---|---|---|---|---|
| IS | compatible | compatible | compatible | incompatible |
| IX | compatible | compatible | incompatible | incompatible |
| S | compatible | incompatible | compatible | incompatible |
| X | incompatible | incompatible | incompatible | incompatible |
3. Record Locks
- Record lock is the simplest row lock, which locks only one row. For example: SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
- The record lock is always added to the index. Even if a table has no index, InnoDB will implicitly create an index and use this index to implement the record lock.
- It will block other transactions from inserting, updating and deleting them
Record the transaction data of the lock (key words: lock_mode X locks rec but not gap), as follows:
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 10078 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000274f; asc 'O;; 2: len 7; hex b60000019d0110; asc ;;
4. Gap Locks
- A gap lock is a lock added between two indexes, or a gap added before the first index or after the last index.
- A gap lock locks an interval, not just every piece of data in the interval.
- Gap locks only prevent other transactions from inserting into the gap. They do not prevent other transactions from obtaining gap locks on the same gap, so gap x lock and gap s lock have the same function.
5.Next-Key Locks
- Next key lock is a combination of record lock and gap lock. It refers to the lock added to a record and the gap in front of the record.
6. Insert intent
- Insert intent lock is a gap lock set before inserting a row of records. This lock releases the signal of an insertion mode, that is, when multiple transactions are inserted in the same index gap, if they are not in the same position in the insertion gap, they do not need to wait for each other.
- Assuming that there are index values 4 and 7, several different transactions are ready to insert 5 and 6. Before obtaining the exclusive lock of the inserted row, each lock locks the gap between 4 and 7 with the insertion intention lock, but does not block each other, because the inserted rows do not conflict.
The transaction data is similar to the following:
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child` trx id 8731 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000066; asc f;; 1: len 6; hex 000000002215; asc " ;; 2: len 7; hex 9000000172011c; asc r ;;...
SQL lock analysis:
By analyzing the deadlock log, we can find the SQL in which the deadlock occurred and the related waiting locks. Then we analyze the corresponding SQL and the problem is solved.
OK, let's go back to the corresponding SQL, insert into song_ Rank (songid, weight) values (16100) on duplicate key update weight = weight + 1 what lock is added during execution? Official document of locking mechanism
insert locking policy:
The insert statement will add an exclusive record lock to the inserted record. Before adding a record lock, it will also add a GAP lock called insert intention lock. If there is a single key conflict, it will also add a shared record (S) lock.
(SQL lock analysis is very important. I recommend an article here, which is very good, The way to solve deadlock - lock analysis of common SQL statements)
insert on duplicate key lock verification
In order to verify the locking of the insert on duplicate key, let's take transactions 1 and 2 in the above demo and go through the process.
Transaction 1:
mysql> begin; //First step Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; //Step two Query OK, 1 row affected (0.00 sec)
Transaction 2 (separate window):
mysql> begin; //Step 3 Query OK, 0 rows affected (0.00 sec) mysql> insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; // Step 4
Use show engine innodb status to view the current lock request information, as shown in the figure:
Pictures are available:
Transaction 2 holds: IX lock (table lock), gap x lock, insert intent lock (waiting for the gap lock of transaction 1)
Therefore, these three locks will be locked during the execution of insert on duplicate.
Cause analysis of deadlock
Return to the deadlock incident simulation scene (transactions 1, 2, 3) and deadlock log scene introduced at the beginning of this article,
Lock of post crime transaction 1:
Lock of post crime transaction 2:
Recovery route:
1. First, executive services:
begin;
insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1;
Get the gap lock (10,20) and insert intent lock
2. Next, transaction 2 executes:
begin;
insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1;
Get the gap lock (10,20) and wait for the insert intent lock of transaction 1.
3. Then, transaction 3 executes:
begin;
insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1;
Get the gap lock (10,20) and wait for the insert intent lock of transaction 1.
4. Finally, transaction 1 rolls back and releases the insertion intent lock, causing transactions 2 and 3 to hold the gap lock at the same time and wait for the insert intent lock. A deadlock is formed!
Lock mode compatibility matrix (horizontal is the lock held, vertical is the lock being requested):
| compatibility | Gap | Insert Intention | Record | Next-Key |
|---|---|---|---|---|
| Gap | compatible | compatible | compatible | compatible |
| Insert Intention | conflict | compatible | compatible | conflict |
| Record | compatible | compatible | conflict | conflict |
| Next-Key | compatible | compatible | conflict | conflict |
How to avoid the deadlock of insert on duplicate
1. Change insert on duplicate to insert
try{
insert();
}catch(DuplicateKeyException e){
update();
}
Because the gap lock is not added to the insert, this problem can be avoided.
2. Change MySql version
Since this is Mysql 5 7, you can consider changing the Mysql version.
3. Minimize the use of unique index.
The gap lock is related to the index, and the unique key and foreign key will cause additional index checking, which requires more overhead, so we try to reduce the use of unnecessary indexes.
Summary of this paper (important)
This article introduces MySQL 5.0 7. A bug of deadlock. How should we check the deadlock problem?
- 1.show engine innodb status; View deadlock log
- 2. Find deadlock SQL
- 3.SQL locking analysis
- 4. Analyze the deadlock log (what lock is held and what lock is waiting for)
- 5. Be familiar with lock mode compatibility matrix and lock compatibility matrix in InnoDB storage engine.