记一次MySQL innodb insert 死锁问题-技术圈

你知道的越多，不知道的就越多，业余的像一棵小草！

你来，我们一起精进！你不来，我和你的竞争对手一起精进！

编辑：业余草

blog.csdn.net/ignorewho

推荐：https://www.xttblog.com/?p=5306

前两天在视频号发了一个“价值百万的百度网盘限速核心代码”在不到 1 天内，浏览量达到了 10K，今天在文章开始之前，分享给公众号朋友！

如果是间隙锁、或者是行锁的话，那么就可能会导致死锁。但是最近公司的同事在进行单纯的插入意向锁的过程中，也导致死锁。也就是说单纯地插入操作也可能会导致死锁，所以也模拟下这种场景，以后遇到类似问题也不至于慌乱。

同一条插入sql引发的死锁

环境准备

1.创建表：

CREATE TABLE `test_user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  `status` int(10) DEFAULT NULL,
  `unqiue_id` bigint(20) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `index_unique` (`unqiue_id`) USING BTREE,
  KEY `index_user` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

2.插入数据：

INSERT INTO `test_user` VALUES ('1', 'a', '1', '1');
INSERT INTO `test_user` VALUES ('3', 'c', '2', '2');
INSERT INTO `test_user` VALUES ('5', 'e', '3', '3');

模拟死锁

1.启动事务 A。

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25);
Query OK, 1 row affected (0.00 sec)

2.启动事务 B(插入阻塞)。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25);

3.启动事务 C(插入阻塞)。

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25);

4.回滚事务 A。

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

结果

事务 A 正常回滚，事务 B 正常执行插入 sql，事务 C 发生死锁。

分析：

1.在模拟死锁步骤3时查看innodb状态信息：show engin innodb status\\G;

事务B在执行插入操作
---TRANSACTION 118519640, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 675673, OS thread handle 0x7feca6334700, query id 228664842 10.10.1.1 testdata update
insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519640 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000007107711; asc     w ;;
 2: len 7; hex 94000002040110; asc        ;;
 3: len 9; hex e5a4a7e58584e5bc9f; asc          ;;
 4: len 4; hex 80000019; asc     ;;

------------------
TABLE LOCK table `test`.`test_insert_deadlock` trx id 118519640 lock mode IX
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519640 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000007107711; asc     w ;;
 2: len 7; hex 94000002040110; asc        ;;
 3: len 9; hex e5a4a7e58584e5bc9f; asc          ;;
 4: len 4; hex 80000019; asc     ;;

事务C在执行插入操作
---TRANSACTION 118519627, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 675672, OS thread handle 0x7fec297e1700, query id 228664797 10.10.1.1 testdata update
insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25)
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000007107711; asc     w ;;
 2: len 7; hex 94000002040110; asc        ;;
 3: len 9; hex e5a4a7e58584e5bc9f; asc          ;;
 4: len 4; hex 80000019; asc     ;;

------------------
TABLE LOCK table `test`.`test_insert_deadlock` trx id 118519627 lock mode IX
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000007107711; asc     w ;;
 2: len 7; hex 94000002040110; asc        ;;
 3: len 9; hex e5a4a7e58584e5bc9f; asc          ;;
 4: len 4; hex 80000019; asc     ;;

事务A持有锁
---TRANSACTION 118519569, ACTIVE 324 sec
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 675670, OS thread handle 0x7fecaee69700, query id 228748549 10.10.1.1 testdata init
show engine innodb status
TABLE LOCK table `test`.`test_insert_deadlock` trx id 118519569 lock mode IX
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519569 lock mode S locks rec but not gap
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519569 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519569 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 00000710df31; asc      1;;
 2: len 7; hex f9000002180110; asc        ;;
 3: len 9; hex e5a4a7e58584e5bc9f; asc          ;;
 4: len 4; hex 80000019; asc     ;;

RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519569 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 00000710df31; asc      1;;
 2: len 7; hex f9000002180110; asc        ;;
 3: len 9; hex e5a4a7e58584e5bc9f; asc          ;;
 4: len 4; hex 80000019; asc     ;;

由上可以看出：
1.事务A(id:118519569)持有:意向排他锁(表级锁)、共享记录锁、插入意向锁(间隙锁的一种)、排他记录锁
2.事务B(id:118519640)等待获取共享记录锁，事务C(id:118519627)等待获取共享记录锁

3.发生死锁后，查看innodb状态信息：show engin innodb status\G;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-01-11 11:51:38 7feca6334700
*** (1) TRANSACTION:
TRANSACTION 118519627, ACTIVE 725 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 675672, OS thread handle 0x7fec297e1700, query id 228672111 10.10.1.1 testdata update
insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
事务C等待加：插入意向锁和排他记录锁
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 118519640, ACTIVE 719 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 675673, OS thread handle 0x7feca6334700, query id 228672122 10.10.1.1 testdata update
insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25)
事务B持有：共享锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519640 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
事务B等待加：插入意向锁和排他记录锁
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519640 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)


事务B锁状态信息：
---TRANSACTION 118519627, ACTIVE 731 sec
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 675672, OS thread handle 0x7fec297e1700, query id 228672111 10.10.1.1 testdata cleaning up
TABLE LOCK table `test`.`test_insert_deadlock` trx id 118519627 lock mode IX
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S locks rec but not gap
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock_mode X insert intention
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

由上可以看出：

事务 B(id:118519627)此时持有锁：意向排他锁、共享记录锁、插入意向排他锁、共享间隙锁
事务 A 回滚后，事务B和事务C竞争锁资源，首先事务B获取了共享记录锁，事务C也申请共享记录锁，因为共享锁之间是兼容的，所以申请成功，然后事务B、事务C再申请插入意向排他锁。

因为排他锁和共享锁之间是冲突的，所以事务 B 和事务 C 互相等待对方释放共享锁，这样就出现死锁了。

个人总结：

根据分析过程中，查看 innodb 锁状态信息，可以推出 insert 语句的加锁顺序是：意向排他锁(表级锁)、共享记录锁、插入意向锁(间隙锁的一种)、排他记录锁
上面的死锁例子属于插入意向锁死锁