blog:mysql:lock-scene-3

MySQL死锁场景【insert插入】

背景介绍

  • MySQL版本：5.7.16
  • 数据库的隔离级别为RR

场景模拟

CREATE TABLE `t6` (
       `id` INT(11) NOT NULL AUTO_INCREMENT,
       `a` INT(11) DEFAULT NULL,
       PRIMARY KEY (`id`),
       UNIQUE KEY `idx_a` (`a`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
 
mysql> INSERT INTO t6 VALUES(1,2),(2,8),(3,9),(4,11),(5,19);
Query OK, 5 ROWS affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM t6;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    8 |
|  3 |    9 |
|  4 |   11 |
|  5 |   19 |
+----+------+
5 ROWS IN SET (0.00 sec)
session 1 session 2session 3
step-1 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t6(id,a) values(6,15);
Query OK, 1 row affected (0.00 sec)
step-2 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t6(id,a) values(7,15);
step-3 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t6(id,a) values(8,15);
step-4 
mysql> rollback;
Query OK, 0 rows affected (2.05 sec)
step-5 
Query OK, 1 row affected (40.55 sec)
 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

问题分析

1、查看死锁日志

show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-21 23:51:17 0x7f021e294700
*** (1) TRANSACTION:
TRANSACTION 559658087, ACTIVE 41 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3647802, OS thread handle 139647067092736, query id 1766483291 localhost root update
insert into t6(id,a) values(7,15)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658087 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000013; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 559658453, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3649318, OS thread handle 139647072683776, query id 1766485729 localhost root update
insert into t6(id,a) values(8,15)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658453 lock mode S locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000013; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658453 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000013; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
---TRANSACTION 559658087, ACTIVE 180 sec
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3647802, OS thread handle 139647067092736, query id 1766483291 localhost root cleaning up
TABLE LOCK table `okami`.`t6` trx id 559658087 lock mode IX
RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658087 lock mode S
RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658087 lock mode S locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000013; asc     ;;
 1: len 4; hex 80000005; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000f; asc     ;;
 1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658087 lock_mode X locks gap before rec insert intention
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000013; asc     ;;
 1: len 4; hex 80000005; asc     ;;

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

2、加锁日志分析

事务1执行的SQL：insert into t6(id,a) values(7,15)
事务1等待的锁： （插入意向锁）RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658087 lock_mode X locks gap before rec insert intention waiting
事务1持有的锁： 
    （共享锁）RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658087 lock mode S
    （共享间隙锁）RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658087 lock mode S locks gap before rec
    （插入意向锁）RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658087 lock_mode X locks gap before rec insert intention
    

事务2执行的SQL：insert into t6(id,a) values(8,15)
事务2持有的锁：（共享间隙锁）RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658453 lock mode S locks gap before rec
事务2等待的锁：（插入意向锁）RECORD LOCKS space id 5477 page no 4 n bits 80 index idx_a of table `okami`.`t6` trx id 559658453 lock_mode X locks gap before rec insert intention waiting

事务1等待插入意向锁， 因为事务2持有间隙锁，插入意向锁和间隙锁之间冲突。所以事务1需要等待事务2释放间隙锁
事务2等待插入意向锁，因为事务1持有间隙锁，所以需要等待事务1释放间隙锁
造成死锁

3、SQL加锁分析

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

insert加锁策略

insert语句会对插入的这条记录加排他记录锁，在加记录锁之前还会加一种 GAP 锁，叫做插入意向(insert intention)锁，如果出现唯一键冲突，还会加一个共享记录(S)锁。
  • session1 执行SQL insert into t6(id,a) values(6,15);。 session1 会持有 一个共享间隙锁（区间为(5, +∞)）和一个插入意向锁
  • session2 执行SQL insert into t6(id,a) values(7,15);。由于Gap锁之间不冲突，session2 会持有 Gap锁 ，因为sesison1持有间隙锁，插入意向锁和间隙锁冲突，所以需要等待获取 insert intention lock（插入意向锁）
  • session3 执行SQL insert into t6(id,a) values(8,15);。 session2 和session3 同时等待获取insert intention lock（插入意向锁）。
  • session1 执行rollback， 释放间隙锁和插入意向锁。此时session2和session3之间形成死锁
session2持有间隙锁， 等待获取插入意向锁
session3持有间隙锁，等待获取插入意向锁

session2要获取插入意向锁，因为插入意向锁和间隙锁冲突，需要等待间隙锁释放。所以到等待session3的间隙锁释放
session3跟session2同理。形成死锁

4、解决方案

, , ,
  • blog/mysql/lock-scene-3.txt
  • 最后更改: 2022/04/22 09:23
  • 由 okami