mysql replace into 锁_并发replace into导致MySQL死锁

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

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

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock

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

表的DLL类似如下：

CREATE TABLE tbl_adv ( a VARCHAR(32) NULL, b VARCHAR(32) NULL, c VARCHAR(32) NULL, UNIQUE KEYidx_uk(a,b) );

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

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

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

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

4.直接insert记录。

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

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

That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

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

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

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

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

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

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

4.会话2：inert记录(2018,8,2)，检测到区间(infimum, 2022]上有锁等待，于是升级为插入意向锁，等待会话3，发生死锁。

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