新知一下
海量新知
6 0 7 1 4 1 4

记一次MySQL innodb insert 死锁问题

业余草 | 面向开发者深度IT技术知识分享 2022/01/14 15:57

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

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

新知达人, 记一次MySQL innodb insert 死锁问题

同一条插入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 statusG

;

事务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 statusG;

------------------------

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

由上可以看出:

  1. 事务 B(id:118519627)此时持有锁:意向排他锁、共享记录锁、插入意向排他锁、共享间隙锁

  2. 事务 A 回滚后,事务B和事务C竞争锁资源,首先事务B获取了共享记录锁,事务C也申请共享记录锁,因为共享锁之间是兼容的,所以申请成功,然后事务B、事务C再申请插入意向排他锁。

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

个人总结:

  1. 根据分析过程中,查看 innodb 锁状态信息,可以推出 insert 语句的加锁顺序是:意向排他锁(表级锁)、共享记录锁、插入意向锁(间隙锁的一种)、排他记录锁

  2. 上面的死锁例子属于插入意向锁死锁


更多“MySQL”相关内容

更多“MySQL”相关内容

新知精选

更多新知精选