背景
业务中有两个不同的业务,更新同一批数据时出现死锁问题,sql语句如下
update test set a= 1 where name = 'test';
update test set b = 2 where name = 'test' and time >0;
-- 这里的name是一个普通的索引
频繁出现死锁
锁分析
update锁的执行过程:
- 首先根据where条件查出符合条件的数据,这个查询是一条一条查询的
- Innodb引擎支持行锁,才会出现死锁,表锁不会出现死锁
- MySQL会首先取出来第一条符合的数据,并同时对这个数据加上共享锁
- 然后再对这个数据进行S锁升级到X锁
数据加锁的过程(如果sql没有用到索引,会加表锁):
- 首先查询这个数据的索引,如果是非主键索引,那么会先给索引加锁
- 然后会根据索引查找对应的主键,进行主键聚簇索引加锁
可能性分析
- 增加time条件导致的加锁异常
我首先猜测,是不是因为第二条sql语句中加了一个非索引条件,导致索引加锁顺序发生变化,先对主键索引进行加锁,然后再对普通索引进行加锁,后来查询资料,说加锁顺序是一定的,一定是先加普通索引的锁,所以这个排除了 - 多线程并发问题导致S锁异常
两个事务同时对一条数据进行更新的时候(这个是否为并发有待验证),两个update语句都拿到了数据的S锁,但是升级X锁的时候,出现问题,因为S锁升级X锁,在隔离等级是RC的情况下,了解到必须等所有的S锁解开,才能S锁升X锁,所以两个事务相互等待,死锁了。
经过分析,第二种可能性更大,但是系统是频繁报错,所以后续还会继续排查
结果处理
MySQL之上加了一层redis缓存锁,防止多个脚本同时更新一个数据,如果有其他的解决方法,希望大家评论回复下