preface

Mysql > select * from ‘Mysql’; There was an insert on Duplicate deadlock problem online before. Based on this deadlock problem, this paper will share the troubleshooting and analysis process, hoping it will be helpful to you.

Deadlock case restore

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;
Copy the code

Isolation level:

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
Copy the code

Database Version:

+ -- -- -- -- -- -- -- -- -- -- -- -- + | @ @ version | + -- -- -- -- -- -- -- -- -- -- -- -- + | 5.7.21 - log | + -- -- -- -- -- -- -- -- -- -- -- -- + 1 rowin set (0.00 sec)
Copy the code

To 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)
Copy the code

Table data:

mysql> select * from song_rank;
+----+--------+--------+
| id | songId | weight |
+----+--------+--------+
|  1 |     10 |     30 |
|  2 |     20 |     30 |
+----+--------+--------+
2 rows in set (0.01 sec)
Copy the code

Cause of deadlock:

In concurrent environments, perform insert into… On the duplicate key update… Lead to a deadlock

Deadlock simulation replay:

Transaction one execution:

mysql> begin; // 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; Query OK, 1 row affected (0.00 SEC) mysql> rollback; Query OK, 0 rows affected (0.00 SEC)Copy the code

Transaction 2 execution:

mysql> begin; // 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; Query OK, 1 row affected (40.83 SEC)Copy the code

Transaction 3 execution:

mysql> begin; // 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 6Copy the code

Transaction 1, transaction 2, transaction 3 execute:

steps A transaction Transaction 2 Transaction three
The first step begin;
The second step Insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; Query OK, 1 row affected (0.00 SEC)
The third step begin;
The fourth step Insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; / / is blocked
Step 5 begin;
Step 6 Insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1; / / is blocked
Step 7 rollback;
The results of Query OK, 1 row affected (40.83 sec) ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlocks surface:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Copy the code

Deadlock detection analysis

What should we do when we encounter a deadlock problem? There are several steps

1. View deadlock logs

When a database deadlock occurs, you can run the following command to obtain deadlock logs:

show engine innodb status;
Copy the code

Insert on duplicate deadlock problem logs 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     ;;
Copy the code

2. Analyze deadlock logs

How do you analyze deadlock logs? Share my thoughts

  • The deadlock log is split into transaction 1 and transaction 2
  • Find SQL to start life and death lock
  • Find out what locks the transaction holds and what locks it is waiting on
  • SQL lock analysis

Transaction 1 log analysis

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

Insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1Copy the code

This statement is waiting for an insert intent exclusive lock on index songId_idx:

lock_mode X locks gap before rec insert intention waiting
Copy the code

Transaction 2 log analysis

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

Insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1Copy the code

This statement holds a gap lock for index songId_idx:

lock_mode X locks gap before rec
Copy the code

This statement is waiting for an insert intent exclusive lock on index songId_idx:

lock_mode X locks gap before rec insert intention waiting
Copy the code

Lock related concepts supplement (Attached) :

Considering that some readers may not be familiar with insert intention lock and so on, here is a section related to lock. The official documentation

InnoDB lock type mind map:

We’ll focus on compatibility and locking mode types

1. Shared 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 a locking 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 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.

2. The intention of the lock

  • Intentional shared lock (IS lock) : a transaction wants to acquire a shared lock on rows of a table
  • Intentional exclusive locks (IX locks) : a transaction wants to acquire exclusive locks for rows in a table

For example, if transaction 1 adds S lock to table 1 and transaction 2 wants to change a row, it needs to add IX lock and wait for S lock to be released due to incompatibility. If transaction 1 has an IS lock on table 1, the IX lock added by transaction 2 IS compatible with the IS lock and can be operated, which enables more fine-grained locking.

InnoDB storage engine locks are compatible with the following table:

compatibility IS IX S X
IS Compatible with Compatible with Compatible with Are not compatible
IX Compatible with Compatible with Are not compatible Are not compatible
S Compatible with Are not compatible Compatible with Are not compatible
X Are not compatible Are not compatible Are not compatible Are not compatible

3. Record Locks

  • Record locks are the simplest row locks,Only one row is locked. Such as:SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
  • A record lock is always placed on an index. Even if a table does not have an index, InnoDB implicitly creates an index and uses that index to enforce a record lock.
  • Blocks other transactions from inserting, updating, or deleting it

Lock_mode X locks REc but not GAP

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 ;;Copy the code

4. Gap Locks

  • A gap lock is a lock placed between two indexes, either before the first index or after the last index.
  • Using gap locks locks an interval, not just each piece of data in that interval.
  • Gap locks only prevent other transactions from inserting into the gap, they do not prevent other transactions from acquiring a gap lock on the same gap, so gap X lock and GAP S lock have the same effect.

5.Next-Key Locks

  • A next-key lock is a combination of a record lock and a gap lock. It refers to a lock placed on a record and the gap in front of that record.

6. Insert Intention

  • An insert intent lock is a gap lock that is placed before 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.
  • Suppose there are index values 4 and 7, and several different transactions are about to insert 5 and 6. Each lock locks the gap between 4 and 7 with insert intent before acquiring an exclusive lock on the inserted row, but does not block the other because the inserted rows do not conflict.

Transaction data looks like 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 ;; .Copy the code

SQL lock analysis:

By analyzing the deadlock log, we can find the SQL where the deadlock occurred, as well as the relevant waiting locks, and then we can perform lock analysis on the corresponding SQL, in fact, the problem is solved.

Insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1 Locking mechanism official documentation

Insert lock strategy:

The INSERT statement places an exclusive record lock on the inserted record, followed by a GAP lock called an Insert Intention lock, and a shared record (S) lock if a unique key conflict occurs.

(SQL lock analysis is very important, here to recommend you a very good article, to solve the deadlock of the road – common SQL statement lock analysis)

Insert on Duplicate key Lock verification

To verify insert on Duplicate key locks, let’s take transactions 1 and 2 from the above demo and run the process. Transaction 1:

mysql> begin; // 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; Query OK, 1 row affected (0.00 SEC)Copy the code

Transaction 2 (separate window) :

mysql> begin; // 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 4Copy the code

Use show Engine Innodb status to view current lock requests, as shown in the figure below:

Pictures are available:

Transaction 2 holds: IX lock (table lock), GAP X lock, Insert intention lock

Therefore, the insert on Duplicate execution will have these three locks.

Deadlock cause analysis

Returning to the deadlock simulation scene (transactions 1,2,3) and deadlock log scene introduced at the beginning of this article,

Lock of Event 1 after incident:

Post Incident Incident 2 lock:

Recovery route:

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

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

4. Finally, transaction 1 is rolled back to release the insert intention lock, so transaction 2 and 3 hold the GAP lock, waiting for the INSERT intention lock, deadlock formation!

Lock mode compatibility matrix (horizontal is held locks, vertical is requested locks) :

compatibility Gap Insert Intention Record Next-Key
Gap Compatible with Compatible with Compatible with Compatible with
Insert Intention conflict Compatible with Compatible with conflict
Record Compatible with Compatible with conflict conflict
Next-Key Compatible with Compatible with conflict conflict

This is a bug in MySql5.7

How to avoid the insert on Duplicate deadlock problem

1. Change insert on duplicate to INSERT

   try{
       insert();
   }catch(DuplicateKeyException e){
       update();
   }
Copy the code

Because INSERTS do not attach gap locks, this problem can be avoided.

2. Change the MySql version

Since this is a bug in MySql5.7, consider changing the Mysql version.

3. Keep unique Index to a minimum.

Gap locks are indexed, and unique keys and foreign keys can cause extra index checking and more overhead, so we try to minimize the use of unnecessary indexes.

Summary (Important)

This article describes a bug in MySql5.7 deadlocks. How do we troubleshoot deadlocks?

  • 1.show engine innodb status; View deadlock logs
  • 2. Find the deadlock SQL
  • 3.SQL lock analysis
  • 4. Analyze deadlock logs (what locks are held, what locks are waiting for)
  • 5. Familiar with the compatibility matrix of lock mode, including the compatibility matrix of lock in InnoDB storage engine.

Reference and thanks

  • An Insert on Duplicate murder
  • MySQL > INSERT lock procedure
  • The path to deadlock resolution – Learn about common lock types
  • MySQL InnoDB lock — official documentation
  • MySQL > alter database lock (record)
  • The path to deadlock resolution – Locking analysis of common SQL statements
  • MySQL Tech Insider

Personal public account

  • If you are a good boy who loves learning, you can follow my public account and study and discuss with me.
  • If you feel that this article is not correct, you can comment, you can also follow my public account, private chat me, we learn and progress together.