背景:
update op_message_shop_user_ref omref left join op_message om on omref.message_id = om.id set omref.click_state = '1', omref.update_time = now() WHERE omref.fk_shp_user_id = ? and omref.click_state = '0' and omref.del = '0' and om.type = ?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
线上bug,每天四点多的时候,很容易出现这种锁等待超时问题。
查询sql,功能模块是一键已读所有未读消息。涉及到的表为消息表。
由于出现了锁竞争,而且问题发生时间点非常均衡,基本都在4点到5点之间,
由此初步判断可能是定时任务执行时和用户操作起了锁冲突。
查询定时任务列表,发现确实存在一个定时任务会在4点开始往消息表里塞数据。
一开始以为只是简单的锁竞争,属于是正常现象,就没去管。
后来有空了清理线上bug时,发现这个问题非常规律且频繁的出现,就花了一点时间去研究。
代码优化方面:
查看了一键已读的代码逻辑,发现有两次update,询问相关同事得知,两次update更新的范围不同,但存在一部分重复数据,有可能是这里导致了锁竞争产生死锁。
优化为查询所有id后,一次性更改。
信息梳理:
定时任务的逻辑中:没有对旧数据的修改,只是新增数据。(有事务)
一键已读的逻辑中:按范围查找并更新的数据。(无事务两次更新)
Mysql版本8.0,innodb存储引擎,事务隔离级别是可重复读
场景中出现的锁:
定时任务的新增:使用了间隙锁来防止幻读情况,导致了锁竞争的出现。
间隙锁是一种范围型的锁,锁原理是基于索引给范围内的数据上锁。
由于一键已读的sql原本使用的select for update 的模式进行更新的,也会使用间隙锁,导致了间隙锁的出现。
总结:
尽量控制锁的范围,能够减小锁范围就减小。
最好避免select for update的出现,先select出查询的数据,在通过id去进行操作。
通过id进行更新时,mysql会使用记录锁来进行上锁。
记录锁是一种行级锁,锁原理是基于唯一性索引(包括主键索引)给索引所对应的一行记录上锁,锁粒度能减少很多。