Preface

How should we investigate and analyze Mysql deadlock? There was an insert on duplicate deadlock problem on the line before. Based on this deadlock problem, this paper will share the process of investigation and analysis, hoping to be helpful to everyone.

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 automatic submission:

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)

Causes of deadlock:

In a concurrent environment, insert in... on duplicate key update... Causing deadlock

Deadlock simulation replication:

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 2
Query OK, 1 row affected (0.00 sec)

mysql> rollback;   //Step 7
Query OK, 0 rows affected (0.00 sec)

Transaction 2:

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:

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

Transaction 1, Transaction 2, Transaction 3:

step	Transaction I	Transaction II	Transaction III
First step	begin;
Step 2	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

Investigation and Analysis of Deadlock Solving Cases

What should we do when we encounter deadlock problems? Take a few steps.

1. View deadlock logs

When a deadlock occurs in a database, the deadlock log can be obtained by following commands:

show engine innodb status;

The log of the insert on duplicate deadlock problem for the example above 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. Analyzing deadlock logs

How to analyze deadlock logs? Share my thoughts.

• Deadlock log is split into transaction 1 and transaction 2
• Find out the SQL where the deadlock occurred
• Find out what locks the transaction holds and what locks it is waiting for
• Analysis of SQL Locking

Transaction log analysis

From the log, we can see that transaction 1 is executing SQL as follows:

insert into  song_rank(songId,weight) values(18,100) on duplicate key update  weight=weight+1

This statement is waiting for the insertion intentional exclusive lock of the index songId_idx:

lock_mode X locks gap before rec insert intention waiting

Transaction 2 Log Analysis

From the log, we can see that transaction 2 is executing SQL as follows:

insert into  song_rank(songId,weight) values(16,100) on duplicate key update  weight=weight+1

This statement holds a gap lock for the index songId_idx:

lock_mode X locks gap before rec

This statement is waiting for the insertion intentional exclusive lock of the index songId_idx:

lock_mode X locks gap before rec insert intention waiting

Lock-related concepts supplement (annex):

Considering that some readers may not be familiar with insert intention ing locks and so on, here's a little bit about lock concepts. Official Documents

InnoDB Lock Type Mind Map:

Let's focus on compatibility and lock mode types.

1. Shared locks and exclusive locks:

InnoDB implements standard row-level locks, including two types: shared locks (s locks) and exclusive locks (x locks).

• Shared Lock (S Lock): Allows lock-holding transactions to read a row.
• Exclusive Locks (X Locks): Allows lock-holding transactions to update or delete a row.

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

• T 2 requests s s s locks are immediately allowed, and as a result, T 1 T 2 holds s s s locks for r rows.
• T2 request x lock cannot be allowed immediately

If T1 holds the X lock of r, then the X and s locks of request r of 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 locks (IS locks): Transactions want to obtain shared locks for rows in a table
• Intentional exclusive locks (IX locks): Transactions want exclusive locks for certain rows in a table

For example, transaction 1 has S locks on table 1, transaction 2 needs to add IX locks to change a row record. Because of incompatibility, it needs to wait for S locks to be released. If transaction 1 has IS locks on table 1, IX locks added by transaction 2 are compatible with IS locks, it can be operated, which realizes finer-grained locking.

The compatibility of locks in InnoDB storage engine is as follows: Compatibility | IS | IX| S | X

• | :-: | :-: | :-: | :-: IS | Compatibility | Compatibility | Compatibility | Incompatibility| IX | Compatibility | Compatibility | Incompatibility | Incompatibility | Incompatibility| S | Compatibility | Incompatibility | Compatibility | Incompatibility| X | Incompatibility | Incompatibility | Incompatibility | Incompatibility | Incompatibility|

3. Record Locks

• Record locks are the simplest line locks, locking only one line. For example: SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
• Record locks are always added to the index. Even if a table does not have an index, InnoDB implicitly creates an index and uses the index to implement the record lock.
• Will block other transactions to insert, update, delete it

Record transaction data for locks (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

• Gap lock is a lock added between two indexes, or before or after the first index.
• The gap lock is used to lock an interval, not just every data in that interval.
• Gap lock s only prevent other transactions from inserting into the gap. They do not prevent other transactions from acquiring gap locks on the same gap, so gap x lock s and gap s lock s 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 Intention Lock

• The insertion intent lock is a gap lock set before inserting a row record operation, which releases an insertion mode signal, i.e. when multiple transactions insert in the same index gap, they do not need to wait for each other if they are not in the same position in the insertion gap.
• Assuming that there are index values 4 and 7, several different transactions are ready to insert 5 and 6. Each lock locks the gap between 4 and 7 with an insertion intention lock before obtaining the exclusive lock of the insertion row, but does not block the other because the insertion row does not conflict.

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 where the deadlock occurs and the related waiting locks, and then we lock the corresponding SQL, in fact, the problem will be solved.

OK, let's go back to the corresponding SQL, insert into song_rank (songId, weight) values (16,100) on duplicate key update weight = weight + 1 execution process, what locks are added? Official Documents of Locking Mechanism

insert locking strategy:

The insert statement adds an exclusive record lock to the inserted record, and a GAP lock, called an insert intention lock, before adding a record lock. If a unique key conflict occurs, a shared record (S) lock is added.

SQL lock analysis is very important. Here I recommend an article to you. It's very good. Deadlock Solution-Locking Analysis of Common SQL Statements)

insert on duplicate key lock verification

To verify the insert on duplicate key locking, we take transaction 1 and 2 of the demo above to 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 2
Query OK, 1 row affected (0.00 sec)

Transaction 2 (Open a 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 Figure 1:

There are pictures available:

Transaction 2 holds: IX lock (table lock), gap lock, insert intention lock (gap lock waiting for transaction 1)

So the insert on duplicate execution process has these three locks.

Cause Analysis of Deadlock

Return to the deadlock scenario (transaction 1, 2, 3) and the deadlock log scenario introduced at the beginning of this article.

Lock of transaction 1 after the incident: Lock of Post-incident Transaction 2:

The recovery route of the case:

1. First, execute transaction 1 to execute: begin; insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; gap locks (10,20), insert intention locks (insert intention locks)

2. Next, transaction 2 executes: begin; insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; A gap lock (10,20) is obtained while waiting for insert intention lock (insert intention 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; A gap lock (10,20) is obtained while waiting for insert intention lock (insert intention lock) of transaction 1.

4. Finally, transaction 1 rollback releases the insert intent lock, resulting in transaction 2,3 holding gap lock at the same time, waiting for insert intention ing lock, deadlock formation!

Lock Mode Compatibility Matrix (horizontal locks are held and vertical locks are requested): Compatibility | Gap | Insert Intention| Record | Next-Key

• | :-: | :-: | :-: | :-: Gap | Compatibility | Compatibility | Compatibility | Compatibility| Insert Intention | Conflict | Compatibility | Compatibility | Conflict| Record | Compatibility | Compatibility | Conflict | Conflict| Next-Key | Compatibility | Compatibility | Conflict | Conflict|

This is a bug in MySql 5.7

How to avoid the insert on duplicate deadlock problem

1. Change insert on duplicate to insert

   try{
       insert();
   }catch(DuplicateKeyException e){
       update();
   }

Because insert does not add a gap lock, this problem can be avoided.

2. Change the MySql version

Since this is a bug in MySql 5.7, consider changing the Mysql version.

3. Minimize the use of unique index.

gap locks are related to indexes, and unique key s and foreign key s cause additional index checks that require more overhead, so we try to minimize the use of unnecessary indexes.

Summary of this article (important)

This article introduces a bug in MySql5.7 deadlock. How should we solve the deadlock problem?

• 1.show engine innodb status; view deadlock log
• 2. Find deadlock SQL
• 3. Analysis of SQL Locking
• 4. Analyse deadlock logs (what locks are held and what locks are waiting for)
• 5. Familiar with lock mode compatibility matrix and lock compatibility matrix in InnoDB storage engine.

Reference and thanks

• A murder case triggered by Insert on duplicate
• Read MySQL source code and see INSERT lock process
• Deadlock Solution - Understanding Common Lock Types
• MySQL InnoDB Lock - Official Document
• MySQL Lock Mechanism - Record Lock, Clearance Lock, Proximity Lock
• Deadlock Solution-Locking Analysis of Common SQL Statements
• MySQL Technology Insider

Personal Public Number

• If you are a good student, you can pay attention to my public number and study and discuss together.
• If you think there are any inaccuracies in this article, you can comment on it, you can also pay attention to my public number, chat with me privately, and all of you can learn and progress together.