mysql并发插入死锁_MySQL: 并发replace into的死锁问题分析-阿里云开发者社区

测试版本：MySQL5.6.23测试表：

create table t1 (a int auto_increment primary key, b int, c int, unique key (b));并发执行SQL：

replace into t1(b,c) values (2,3)  //使用脚本，超过3个会话

背景

replace 操作可以算是比较常用的操作类型之一，我们先来理一下，对于上例，一条简单的replace into操作的主要流程包括哪些。

Step 1. 正常的插入逻辑

首先插入聚集索引，在上例中a列为自增列，由于未显式指定，每次Insert前都会生成一个不冲突的新值

随后插入二级索引b，由于其是唯一索引，在检查duplicate key时，加上记录锁，类型为LOCK_X (对于普通的INSERT操作，当需要检查duplicate key时，加LOCK_S锁，而对于Replace into 或者 INSERT..ON DUPLICATE操作，则加LOCK_X记录锁) 。 由于uk记录已存在，返回错误DB_DUPLICATE_KEY。

Step 2. 处理错误

由于检测到duplicate key，因此第一步插入的聚集索引记录需要被回滚掉(row_undo_ins)

Step 3. 转换操作

从InnoDB层失败返回到Server层后，收到duplicate key错误，首先检索唯一键冲突的索引，并对冲突的索引记录(及聚集索引记录)加锁

随后确认转换模式以解决冲突：

#如果发生uk冲突的索引是最后一个唯一索引、没有外键引用、且不存在delete trigger时，使用UPDATE ROW的方式来解决冲突

#否则，使用DELETE ROW + INSERT ROW的方式解决冲突

Step 4. 更新记录

在该例中，对聚集索引和二级索引的更新，都是采用标记删除+插入新记录的方式

对于聚集索引，由于PK列发生变化，采用delete + insert 聚集索引记录的方式更新

对于二级uk索引，同样采用标记删除 + 插入的方式

我们知道，在尝试插入一条记录时，如果插入位置的下一条记录上存在记录锁，那么在插入时，当前session需要对其加插入意向锁，具体类型为LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION

是否能保证自增列的有序性：

默认情况下，参数innodb_autoinc_lock_mode的值为1，因此只在分配自增列时互斥(如果我们将其设为0的话，就会产生AUTO_INC类型的表级锁)

当分配完自增列值后，我们并不知道并发的replace into的顺序。

死锁分析：

回到死锁线程分析，从死锁日志我们大致可以推断出如下序列：

Session 1 执行到Step4, 准备更新二级Uk索引，因此持有uk上heap no 为5的X 行锁和PK上的X行锁

Session 2 检查到uk冲突，需要加X行锁

Session 1 在标记删除记录后，尝试插入新的uk记录，发现预插入点的下一条记录(heap no =5) 上有锁请求，因此尝试加插入意向X锁，产生锁升级， 死锁路径：Session1 => Session 2 => Session1到这里，其实问题已经很明显了。 我们考虑如下场景：假设当前表内数据为：

root@sb1 08:57:41>select * from t1;

+———+——+——+

| a | b | c |

+———+——+——+

| 2100612 | 2 | 3 |

+———+——+——+

1 row in set (0.00 sec)

由于不能保证自增列被更新的有序性，我们假定session 1获得自增列值为2100619， session 2 获得的自增列值为2100614， session 3获得的自增列值为2100616

为了便于阐述，我们假定表上总是只有一条记录

Session 1:

replace into t1 values (2100619, 2, 3); // uk索引上记录(2, 2100612)被标记删除，同时插入新记录(2, 2100619)Purge线程启动，(2, 2100612)被物理删除，Page上只剩下唯一的物理记录(2, 2100619)

Session 2:

replace into t1 values (2100614, 2, 3);

这里我们使用gdb的non-stop模式，使其断在row_update_for_mysql函数(insert尝试失败后，会转换成update)，此时session2持有(2, 2100619) 的X锁Tips：我们可以通过如下命令使用gdb的non-stop模式：

1. 以gdb启动mysqld

2. 设置：

set target-async 1

set pagination off

set non-stop on

3. 设置函数断点

Session 3:

replace into t1 values (2100616, 2, 3); // 检测到uk有冲突键，需要获取记录(2, 2100619) 的X锁，等待session 2

Session 2:

a)标记删除记录(2, 2100619)，同时插入新记录(2, 2100614)；

b) (2, 2100614) 比(2, 2100619) 要小，因此定位到该记录之前，也就是系统记录infimum

c)infimum记录的下一条记录(2, 2100619)上有锁等待，需要升级成插入意向X锁，导致死锁发生

假定Purge线程停止，会发生什么呢 ？

我们随便建一个表，然后执行FLUSH TABLE tbname FOR EXPORT来让purge线程停止，执行序列如下：root@sb1 10:26:05>select * from t1;

+———+——+——+

| a | b | c |

+———+——+——+

| 2100710 | 2 | 3 |

+———+——+——+

1 row in set (0.00 sec)session 1

session 1:

replace into t1 values (2100720, 2, 3);

此时Page上存在记录(infimum), (2, 2100710), (2, 2100720), (supremum)

session 2

replace into t1 values (2100715, 2, 3);

同上例，使用gdb断到函数row_update_for_mysql由于没有启动purge线程，因此老的被标记删除的记录还存在于page内，在扫描二级索引重复键时，也会依次给这些老记录加锁，因此session 2会持有 (2, 2100710)和 (2, 2100720)的X锁

session 3

replace into t1 values (2100718, 2, 3); // 被session2阻塞，等待(2,2100710)的X锁lock_mode:3, heap no:810

session 2

在标记删除二级索引记录，并进行插入时，选择的插入位置为 (2, 2100710), (2,2100720)之间，插入点的下一条记录(2,2100720)上没有其他线程锁等待，当前session锁升级成功

完成插入后，page上的记录分布为(infimum), (2, 2100710), (2, 2100715), (2, 2100720), (supremum)

session 3

完成插入，最终page内的记录为(infimum), (2, 2100710), (2, 2100715), (2, 2100718), (2, 2100720), (supremum)

其中只有用户记录(2, 2100718)未被标记删除