mysql死锁回滚策略_MySQL 死锁套路：三个事务插入有一个回滚

这个是非常经典的一个场景，在网上搜索插入意向锁造成的死锁，绝大部分都会指向这个例子

表结构

CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`a` varchar(5),

`b` varchar(5),

PRIMARY KEY (`id`),

UNIQUE KEY `uk_name` (`a`,`b`)

);

复制代码

三个事务的 insert 语句都是 insert ignore into t1(a, b)values("1", "1");

复现步骤如下：

t1

t2

t3

备注

begin

begin

begin

insert

成功

insert

把 t1 的隐式锁提升为 X 锁，t2 进入进入 S 锁等待

insert

t3 进入进入 S 锁等待

rollback;

t1 回滚以后，释放 X 锁，t2 和 t3 同时拿到了 S 锁

ok

deadlock

t2 和 t3 都想拿插入意向锁 X 锁，造成死锁条件

死锁日志如下

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

LATEST DETECTED DEADLOCK

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

181101 23:22:59

*** (1) TRANSACTION:

TRANSACTION 5032, ACTIVE 11 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1

MySQL thread id 5, OS thread handle 0x70000d736000, query id 125 localhost root update

insert ignore into t1(a, b)values("1", "1")

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 56 page no 4 n bits 584 index `uk_name` of table `d1`.`t1` trx id 5032 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 139 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 3; hex 313031; asc 101;;

1: len 3; hex 313031; asc 101;;

2: len 4; hex 800007b1; asc ;;

*** (2) TRANSACTION:

TRANSACTION 5033, ACTIVE 6 sec inserting

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1

MySQL thread id 6, OS thread handle 0x70000d779000, query id 126 localhost root update

insert ignore into t1(a, b)values("1", "1")

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 56 page no 4 n bits 584 index `uk_name` of table `d1`.`t1` trx id 5033 lock mode S locks gap before rec

Record lock, heap no 139 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 3; hex 313031; asc 101;;

1: len 3; hex 313031; asc 101;;

2: len 4; hex 800007b1; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 56 page no 4 n bits 584 index `uk_name` of table `d1`.`t1` trx id 5033 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 139 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 3; hex 313031; asc 101;;

1: len 3; hex 313031; asc 101;;

2: len 4; hex 800007b1; asc ;;

*** WE ROLL BACK TRANSACTION (2)

复制代码

这里我们第一次提到了插入意向锁(insert intention lock)

插入意向锁(insert intention lock)

对已有数据行的修改与删除，必须加强互斥锁 X 锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

插入意向锁是间隙锁(Gap Locks)的一种，它是专门针对 insert 操作的，也是为数不多的在 RC 级别下产生 Gap 锁情况