MySQL常见死锁分析

1、RC级别

1.1、并发insert导致死锁一

create table t1(id int primary key(id));
insert into t1(id) values(1), (10);

序号	trx 1	trx 2	trx 3
①	begin;
②		begin;
③			begin;
④	insert into t1(id) value(2);
⑤		insert into t1(id) value(2);
⑥			insert into t1(id) value(2);
⑦	rollback;

trx2、trx3出现死锁,这也是MySQL官网举的例子
死锁日志：show engine innodb status; 分析死锁日志可以发现，死锁的原因在于：

1. 事务2、3均持有记录id=10上的gap锁；
2. 事务2、3又分别请求记录id=10的X插入意向锁
3. 形成事务2等待事务3所持有的gap锁释放，事务3等待事务2所持有的gap锁释放，互相等待造成死锁

造成死锁的解释如下：

1. ④语句事务1执行时，此时检查下一记录id=10并无锁，成功加S插入意向锁。插入成功后，此时构建隐式锁，等待事务提交或者回滚；
2. ⑤语句事务2执行时，此时检查下一记录id=10仅有S插入意向锁，（S插入意向锁之间互相兼容），所以事务2插入意向锁也可以成功获得；下一步检查主键唯一性时，发现id=2记录已存在，且id=2聚簇索引上的事务ID=事务1,此时事务1依旧活跃，而且是insert事务，为事务1加上id=2的X记录锁（隐式锁转换为显式锁），同时为自己申请id=2的S记录锁，因为与事务1持有的X记录锁冲突，所以阻塞等待；
3. ⑥语句事务3执行时，同事务2；
4. ⑦语句回滚，释放id=2 X记录锁，同时唤醒事务2、3;
5. 事务2，重新来过，检查发现下一记录id=10上有间隙锁（事务3先前持有的S插入意向锁（插入意向锁也是一种间隙锁）$\color{red}{此步为自己推测，没有找到准确的资料文档}$），需要申请X插入意向锁；与事务3持有的S间隙锁冲突，阻塞等待；
6. 事务3，同事务2；至此死锁形成； MySQL官网文档还举了一个例子： |序号|trx 1|trx 2|trx 3| |---|---|---|---| |①|begin;||| |②| |begin;|| |③| | |begin;| |④|delete t1 where id = 2;| | | |⑤| |insert into t1(id) value(2);|| |⑥| | |insert into t1(id) value(2);| |⑦|commit;| | |

造成死锁的原因与上例一致

1.2、并发insert导致的死锁二

create table t3(
    id int not null auto_increment,
    a int not null,
    b int not null,
    primary key(id),
    unique_key uq_a(a)
);
insert into t3(a, b) values(1, 1), (10, 10);

序号	trx 1	trx 2	trx 3
①	begin;
②		begin;
③			begin;
④	insert into t3(a, b) value(5, 5);
⑤		insert into t3(a, b) value(5, 5);
⑥			insert into t3(a, b) value(5, 5);
⑦	rollback;

也会出现死锁，此例子与1.2的例子很相似，区别在于事务2发现事务1已经存在相同记录且为活跃事务时，申请的不是S记录锁，而是S next-key锁；其他流程都一致；

1.3、delete-delete导致的死锁

create table t4(
    id int not null auto_increment,
    a int not null,
    b int not null,
    primary key (id),
    key idx_a(a)
);
insert into t4(a, b) values(1, 1), (1, 2);
insert into t4(a, b) values(2, 1), (2, 2), (2, 3), (2, 4), (2, 5);
insert into t4(a, b) values(2, 6), (2, 7), (2, 8), (2, 9), (2, 10);

id	a	b
1	1	1
2	1	2
3	2	1
4	2	2
...	...	...

trx 1	trx 2
delete from t4 where a = 1;	delete from t4 where a = 2;

此例在并发场景下有一定概率发生死锁；

死锁分析：
事务1会走idx_a索引，而事务2并未命中索引，走全表扫描，这个大前提是促发死锁的关键； 二级索引的delete操作，会先对二级索引列加X记录锁，然后再对聚簇索引加X记录锁；而未命中索引走全表扫描，则是先对聚簇索引加X记录锁再对涉及的二级索引加X记录锁；两者的加锁顺序刚好相反，所以就有可能造成死锁的发生；例如

序号	trx 1	trx 2
①	对idx_a a=1加锁
②		对id=2 (2, 1, 2)加锁
③	对id=2 (2, 1, 2)加锁
④		对idx_a a=1加锁

如上图示意，互相等待持有的锁，导致死锁。

1.4、index merge导致的死锁

create table t5(
    id int not null auto_increment,
    a int not null,
    b int not null,
    c int not null,
    primary key(id),
    key idx_a(a),
    key idx_b(b)
);
insert into t5(a, b, c) values(1, 1, 1), (2, 1, 1), (3, 1, 1);
insert into t5(a, b, c) values(1, 2, 2), (2, 2, 2), (3, 2, 2);
insert into t5(a, b, c) values(1, 3, 3), (2, 3, 3), (3, 3, 3);

trx 1	trx 2
update t5 set c = 1 where a = 2 and b = 1;	update t5 set c = 1 where a = 3 and b =1;

在并发场景下有一定概率触发死锁；
explain update t5 set c = 1 where a =2 and b = 1; 通过执行计划可以发现的走的是index merge；加锁示意图如下 如果出现上诉图示的加锁顺序，事务1申请idx_b (1,1)的X记录锁，发现该记录锁被事务2持有，进入阻塞等待；而事务2申请聚簇索引id=1的X记录锁，发现该记录锁被事务1所持有，进入阻塞等待；此时出现两个事务互相等待各自所持有的锁，发生死锁；

index merge 且执行计划是intersect, 在锁定二级索引列，还是会锁住聚簇索引；理由是此处是update操作，聚簇索引也被其他二级索引所关联，此时要对聚簇索引加锁，防止被别的关联索引锁修改；

2、RR级别