记一次MySQL innodb insert 死锁问题

爱马士团团长 04-30 203

前言：

如今看官们对“mysqlbeforeinsert”大体比较讲究，看官们都需要知道一些“mysqlbeforeinsert”的相关文章。那么小编在网上搜集了一些有关“mysqlbeforeinsert””的相关资讯，希望朋友们能喜欢，你们快快来了解一下吧！

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

同一条插入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 insertingmysql tables in use 1, locked 1LOCK 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 updateinsert 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 waitingRecord 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 IXRECORD 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 waitingRecord 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 insertingmysql tables in use 1, locked 1LOCK 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 updateinsert 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 waitingRecord 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 IXRECORD 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 waitingRecord 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 sec5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1MySQL thread id 675670, OS thread handle 0x7fecaee69700, query id 228748549 10.10.1.1 testdata initshow engine innodb statusTABLE LOCK table `test`.`test_insert_deadlock` trx id 118519569 lock mode IXRECORD 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 gapRECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519569 lock mode SRecord 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 recRecord 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 gapRecord 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 insertingmysql tables in use 1, locked 1LOCK 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 updateinsert 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 waitingRecord 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 insertingmysql tables in use 1, locked 14 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 updateinsert 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 SRecord 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 waitingRecord 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 sec5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1MySQL thread id 675672, OS thread handle 0x7fec297e1700, query id 228672111 10.10.1.1 testdata cleaning upTABLE LOCK table `test`.`test_insert_deadlock` trx id 118519627 lock mode IXRECORD 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 gapRECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode SRecord 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 intentionRecord 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 recRecord 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 语句的加锁顺序是：意向排他锁(表级锁)、共享记录锁、插入意向锁(间隙锁的一种)、排他记录锁上面的死锁例子属于插入意向锁的死锁

本文地址：http://www.longkongtuishu.com/ca383BARsDFEHD1I.html

标签： #mysqlbeforeinsert