小笔记|MySQL造成更新死锁及插入死锁的几种常见原因数据库|database|mysql

更新死锁要了解更新死锁，首先要了解更新时加锁的顺序:

通过聚簇索引更新时，会在聚簇索引上加锁。
通过二级索引进行更新时，会先对二级索引加锁，然后对聚簇索引加锁。
使用聚簇索引更新二级索引时，会先对聚簇加锁，再对二级索引加锁。此结论的前提条件为结论4。
更新二级索引时，只有二级索引所在的列产生实际变化的更新，才会对二级索引加锁，否则仅会对聚簇索引加锁。
在REPEATABLE_READ级别下，对索引的加锁范围是索引所确定的范围，而不是最终结果集范围。也就是说需要回表查询才能剔除的行的聚簇索引依然会被加锁。而READ_COMMITTED级别下则不会。

更新时加锁顺序如果不一致，就可能导致死锁的发生：

对同一条数据，T1通过二级索引更新，T2通过聚簇索引更新T1作为更新条件二级索引，如果T1先拿到二级索引，T2先拿到聚簇索引。此时就会出现T1等T2的聚簇索引，T2等T1的二级索引的死锁。
对一批数据，T1的批量更新顺序是（A，B，C），T2的批量更新顺序是（C，B，A），如果T1先拿到A，B的锁，T2先拿到C的锁，那么就会出现T1等T2的C锁，T2等T1的A，B锁的情况。

锁合并引起的更新死锁 【小笔记|MySQL造成更新死锁及插入死锁的几种常见原因】MySQL5.0之前最好的情况下也只能使用最左的一个索引。MySQL5.0引入“索引合并(index merge)”策略，理念是通过取交集，并集，先交再并的方式将同一个表多个索引的范围进行合并。索引合并可以通过参数optimizer_switch关闭。
索引合并一定程度上可以使用多个单列索引定位指定的行，然而这种策略弊端很大,所带来的优化有时并不符合预期，因此尽量不要用这个机制来作为设计方案，如果EXPLAIN中的type列为index_merge，Extra列为:Using intersect/union/sort_union(index1,index2,index3...),说明这个查询使用了索引合并策略，此时要好好审查这个查询的索引设计合理性。
在官方声明中也强调应该避免在update语句中出现索引合并，因为这可能导致死锁。因为索引合并的场景下，加锁顺序为：二级索引A->A对应的聚簇索引->二级索引B->B对应的聚簇索引。
那么就有以下场景：

文章图片

解决方案很简单，建立name和age的联合索引就可以解决。
插入死锁

duplicate key error引发的死锁，这个场景主要发生在两个以上的事务同时进行唯一键值（唯一索引）相同的记录插入操作。假设TD正在执行R1的删除操作，此时T1和T2都执行R1的插入操作，他们都要加排他锁，但是这时间TD在执行删除，拿着排他锁，所以这两个插入事务都会先将排他锁转为共享锁，等待TD操作结束释放锁，TD结束后，因为记录已经被删除了，所以T1和T2都不会出现主键冲突，都会继续执行，此时就需要将自己的共享锁转为排他锁，但是要转为排他锁T1就要等T2释放共享锁，同样的T2也在等T1释放共享锁，就导致了死锁的发生。 TD并不一定是插入操作，也可以是一个非自动提交的插入事务T0，在T1和T2转为共享锁后，T0回滚，这样T1和T2也不会出现主键冲突，都会继续执行，同样产生死锁。
GAP锁引发的死锁。假设现在表中有条目 [1, 5, 10], 假设T1在 5-10之间加上GAP锁(间隙锁)，T2也在 5-10之间加上GAP锁(间隙锁)，然后此时T1插入一条数据8，那么就需要加排他锁，要加排他锁就要等待T2的GAP释放，但是如果T2后面有插入数据9的操作，也就需要要等待T1的GAP释放，也产生了死锁。