mysql replace into MYSQL的REPLACE INTO导致死锁问题

mysql replace into MYSQL的REPLACE INTO导致死锁问题

之前曾解决过Spark任务的不同同时更新MySQL导致死锁的问题，最近该同事遇到了这个问题的升级版：业务有两个不同的数据源分别用于实时计算和更新MySQL同一张表的不同列，目前这个是分别启动了两个Spark 任务，但是更新MySQL不时 出现死锁的问题，只能通过不断try/catch重试来暂时解决。

一开始听到这个问题我有点意外，因为更新操作从业务上讲应该只要求更新行的行锁mysql replace into MYSQL的REPLACE INTO导致死锁问题，即使是batch 也应该是逐行和，不会出现死锁必须条件中的”请求和保持条件”。当一个进程要写入某条记录时发现该记录被锁了，那么等待一下再写入应该是没问题的。但是查看日志，又确实出现了死锁：

com.mysql.jdbc..jdbc4.: found when to get lock

我检查了代码mysql replace into MYSQL的REPLACE INTO导致死锁问题，发现SQL并不是而是 into，问题很可能出在这里。 into先是执行，发生 key之后再去更新原有记录。这意味着从引擎的角度看操作并不是原子性的，有可能会要求多个行锁。

表的DLL类似如下：

TABLE ( a (32) NULL, b (32) NULL, c (32) NULL, (a,b) );

默认引擎是，没有主键，只有一个有可能其中某列为null的唯一索引。这意味着会给该表创建一个隐藏的自增主键(聚集索引)，但发生 key冲突的索引还是。这种情况下 into可以分为以下几步：

插入聚集索引，检查聚集索引上是否有冲突。由于总是分配一个新的主键，这步一定成功。

插入二级索引，检查二级唯一索引上是否有冲突。若是，则undo步骤1插入的聚集索引记录mysql replace into，转到步骤3；若否，转到步骤4。

处理冲突。通过索引定位冲突行并加锁，新记录成功后冲突行。

直接记录。

死锁就发生在步骤3的 + 中。

根据MySQL 5.7 ，在一行时Next-Key Lock会将该行在索引上的前一个区间锁住以防止幻读。

That is, a next-key lock is an index- lock plus a gap lock on the gap the index . If one has a or lock on R in an index, a new index in the gap R in the index order.

如果两个同时需要同一个主键的记录mysql replace into，并一条主键更小的记录，死锁就有可能发生。

假设表A有列(a, b, c)，其中a是自增主键，b上有唯一索引。现在有记录(2017,8,0)mysql replace into，有3个会话要分别 into三条记录：(2018,8,2)、(2021,8,3)、(2022,8,1)。

由于执行的顺序可能与分配主键顺序不同，假设顺序分别为(2022,8,1)、(2018,8,2)、(2021,8,3)：

会话1：更新成功，现在表的记录是(2022,8,1)。

会话2：插入二级索引时发生冲突，需要主键为2022的记录，在该记录上加X锁，同时为区间(, 2022

加Next-Key锁。

会话3：插入二级索引时发生充裕，需要主键为2022的记录，在该记录上尝试加锁时失败，等待会话2的锁释放。

会话2：inert记录(2018,8,2)，检测到区间(, 2022

上有锁等待，于是升级为插入意向锁，等待会话3，发生死锁。

问题关键点在于是以 + 的方式去更新记录，改变了聚集索引上的值。解决这个问题的方案是，更新的时候避免重新分配新的记录，具体可以使用 … on key …。该SQL遇到唯一索引冲突时，总是使用旧记录的方式来更新。