MySQL死锁案例分:先delete，再insert，导致死锁

一、死锁案例

MySQL版本：Percona MySQL Server 5.7.19
隔离级别：可重复读（RR）
业务逻辑：并发下按某个索引字段先delete记录，再insert记录

比如：

1.  
   begin;
2.  
   delete from tb where order_id = xxx;
3.  
   insert into tb(order_id) values(xxx);
4.  
   commit;

二、MySQL锁基本概念

S：共享锁（行级锁）
X：排他锁（行级锁）
IS：意向共享锁（表级锁）
IX：意向排他锁（表级锁）

以上4种锁的兼容性见下表：

锁模式兼容性表

• gap锁与gap锁之间不冲突
• rec insert intention(插入意向锁）与gap锁冲突。

三、模拟复现死锁

打开参数，从innodb status获取更多的锁信息。
set GLOBAL innodb_status_output_locks=ON;

表结构：

1.  
   CREATE TABLE `tb` (
2.  
   `order_id` int(11) DEFAULT NULL,
3.  
   KEY `idx_order_id` (`order_id`)
4.  
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中数据：

1.  
   mysql> select * from tb;
2.  
   +----------+
3.  
   | order_id |
4.  
   +----------+
5.  
   | 10 |
6.  
   | 20 |
7.  
   +----------+
8.  
   2 rows in set (0.00 sec)

事务执行步骤：

session1	session2
begin
	begin
delete from tb where order_id=15;
	delete from tb where order_id=15;
	insert into tb select 15;(等待锁)
insert into tb select 15;(死锁)

1. 当session1执行delete from tb where order_id=15;，由于条件order_id=15的记录不存在，session1 获得2个锁结构，分别是意向排他锁IX（表级锁）、gap锁（行级锁），如下：

1.  
   ---TRANSACTION 1055191443, ACTIVE 20 sec
2.  
   2 lock struct(s), heap size 1136, 1 row lock(s)
3.  
   MySQL thread id 315642, OS thread handle 139960342456064, query id 150462030 localhost root
4.  
   TABLE LOCK table `db`.`tb` trx id 1055191443 lock mode IX
5.  
   RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec

1. 当session2执行delete from tb where order_id=15;，同样由于order_id=15的记录不存在，session2 也获得2个锁结构，分别是意向排他锁IX（表级锁）、gap锁（行级锁），如下：

1.  
   ---TRANSACTION 1055191444, ACTIVE 3 sec
2.  
   2 lock struct(s), heap size 1136, 1 row lock(s)
3.  
   MySQL thread id 315336, OS thread handle 139960562685696, query id 150462412 localhost root
4.  
   TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX
5.  
   RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec

1. 当session2执行insert into tb select 15;， session2 已经获取到IX锁，gap锁，等待 rec insert intention（插入意向锁）

1.  
   ---TRANSACTION 1055191444, ACTIVE 68 sec inserting
2.  
   mysql tables in use 1, locked 1
3.  
   LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
4.  
   MySQL thread id 315336, OS thread handle 139960562685696, query id 150462778 localhost root executing
5.  
   insert into tb select 15
6.  
   ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
7.  
   RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting
8.  
   ------------------
9.  
   TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX
10.  
    RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec
11.  
    RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting

1. 当session1执行insert into tb select 15;，session1 已获取到IX锁，gap锁， 等待rec insert intention（插入意向锁）， session1, session2 都在等待插入意向锁， 插入意向锁与gap锁冲突，双方都没有释放gap锁，又都在等待插入意向锁，死锁发生。

1.  
   LATEST DETECTED DEADLOCK
2.  
   ------------------------
3.  
   2018-11-03 17:15:11 0x7f4b0e7ea700
4.  
   *** (1) TRANSACTION:
5.  
   TRANSACTION 1055191444, ACTIVE 135 sec inserting
6.  
   mysql tables in use 1, locked 1
7.  
   LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
8.  
   MySQL thread id 315336, OS thread handle 139960562685696, query id 150462778 localhost root executing
9.  
   insert into tb select 15
10.  
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
11.  
    RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting
12.  
    *** (2) TRANSACTION:
13.  
    TRANSACTION 1055191443, ACTIVE 201 sec inserting, thread declared inside InnoDB 5000
14.  
    mysql tables in use 1, locked 1
15.  
    3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
16.  
    MySQL thread id 315642, OS thread handle 139960342456064, query id 150463172 localhost root executing
17.  
    insert into tb select 15
18.  
    *** (2) HOLDS THE LOCK(S):
19.  
    RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec
20.  
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
21.  
    RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec insert intention waiting
22.  
    *** WE ROLL BACK TRANSACTION (2)

四、案例扩展

以上死锁案例，业务代码逻辑是多线程并发下，有可能多个线程会执行相同order_id的job，比如两个线程执行的order_id 都是15。
另外一种情况，多个线程间，不会执行到相同order_id的情况，也可能发生死锁。比如一个线程order_id=15，另外一个线程order_id=16，如下所示：

事务执行步骤：

session1	session2
begin
	begin
delete from tb where order_id=15;
	delete from tb where order_id=16;
	insert into tb select 16;(等待锁)
insert into tb select 15;(死锁)

锁情况与上述相同，不再赘述，死锁信息如下：

1.  
   LATEST DETECTED DEADLOCK
2.  
   ------------------------
3.  
   2018-11-03 17:28:30 0x7f4b0e667700
4.  
   *** (1) TRANSACTION:
5.  
   TRANSACTION 1055191450, ACTIVE 18 sec inserting
6.  
   mysql tables in use 1, locked 1
7.  
   LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
8.  
   MySQL thread id 316221, OS thread handle 139960338228992, query id 150467652 localhost root executing
9.  
   insert into tb select 16
10.  
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
11.  
    RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191450 lock_mode X locks gap before rec insert intention waiting
12.  
    *** (2) TRANSACTION:
13.  
    TRANSACTION 1055191449, ACTIVE 28 sec inserting, thread declared inside InnoDB 5000
14.  
    mysql tables in use 1, locked 1
15.  
    3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
16.  
    MySQL thread id 316222, OS thread handle 139960340870912, query id 150467681 localhost root executing
17.  
    insert into tb select 15
18.  
    *** (2) HOLDS THE LOCK(S):
19.  
    RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec
20.  
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
21.  
    RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec insert intention waiting
22.  
    *** WE ROLL BACK TRANSACTION (2)
23.  
     

五、解决方案

1. 修改隔离级别为提交读（RC）
2. 修改业务代码逻辑，删除记录之前，先select，确认该记录存在，再执行delete删除该记录。