数据入库这块有离线和实时两套入库系统,写同一个db的同一批mysql表,两边用的都是insert into table on duplicate key update这种方式。实时一直运行,离线5分钟更新一次,当两套系统同时运行时出现了死锁问题,频率还挺高。事务的隔离级别是read committed 读提交。
这里面分两种情况,一种是带主键的insert duplicate key update,一种是没有主键带唯一索引的insert duplicate key update。
实时入库的batch大小是1w,离线入库的batch大小也是1w,为了提高入库效率 ,两边都开启了事务。当两边同一批插入的数据中包含了相同的数据,且顺序不一致,此时会出现死锁。比如说实时任务当前插入的数据包含id为1,2的数据,离线任务当前插入的数据包含2,1。此时实时任务获取了id为1数据的锁,等2的数据锁,离线任务获取了id为2的数据锁,等id为1数据锁,产生了死锁。
这种情况比较简单,处理方式有两种:
1、减少batch大小,减少了同一批数据中包含相同数据的概率,也就减少了死锁发生的概率
2、入库前对sql按照id排序,即使出现相同数据,只要保证顺序相同就不会出现死锁
https://developer.aliyun.com/article/727076 这篇文章讲的很清楚,这里我大致聊一下。 insert相关的有四种锁。
插入唯一索引时判断索引是否存在。如果不存在,会在数据所属的gap添加Insert Intention锁。由于意向锁是轻量gap锁,这时即使有同gap的其他数据插入,两者之间不会互相影响。
如果插入的索引已经存在,会在索引值位置插入next-key锁。next-key锁包含record锁和gap锁,gap锁会锁一个范围,锁住期间,其他事务不能向该范围插入数据。如果此时两个事务各自获取到了一个next-key锁,此时都需要向对方获取到的next-key锁范围插入数据,就会出现死锁。
举例: code字段有唯一索引。初始数据:insert into test2 (code, other) values(1,1),(3,3),(5,5) 事务1: insert into test2(code,other)values(3,3) on duplicate key update other=values(other); 由于code为3的数据已经存在,所以会在(1,3]这个范围加next-key锁。 事务2: insert into test2(code,other)values(5,5) on duplicate key update other=values(other); 由于code为5的数据已经存在,所以会在(3,5]这个范围加next-key锁。 如果此时事务1插入一条code为4的数据,那么此时会等待事务2 (3,5]的next-key锁释放; 如果同时事务2插入一条code为2的数据,那么此时会等待事务1 (1,3]的next-key锁释放; 形成死锁。
解决方案: 1、减少batch的大小,单个事务获取到的next-key锁的范围就会变少,减少死锁的概率。 2、重试。 3、插入数据时添加主键。如果插入数据时带上主键,那么就不会产生next-key锁,会退化到第一种情况(带主键的insert duplicate key update)。