并发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旧记录的方式来更新。
posted on 2018-12-19 20:16 bijian1013 阅读(1343) 评论(0) 编辑 收藏 举报