巧用MySQL InnoDB引擎锁机制解决死锁问题[2]

索引KEY_TSKTASK_MONTIME (STATUS_ID MON_TIME)

分析涉及的两条语句应该不会涉及相同的TSK_TASK记录那为什么会造成死锁呢？

查询MySQL官网文档发现这跟MySQL的索引机制有关MySQL的InnoDB引擎是行级锁我原来的理解是直接对记录进行锁定实际上并不是这样的

要点如下:

不是对记录进行锁定而是对索引进行锁定

在UPDATEDELETE操作时MySQL不仅锁定WHERE条件扫描过的所有索引记录而且会锁定相邻的键值即所谓的nextkey locking

如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 会锁定所有主键大于等于的所有记录在该语句完成之前你就不能对主键等于的记录进行操作

当非簇索引(noncluster index)记录被锁定时相关的簇索引(cluster index)记录也需要被锁定才能完成相应的操作

再分析一下发生问题的两条SQL语句就不难找到问题所在了

当update TSK_TASK set STATUS_ID=UPDATE_TIME=now () where STATUS_ID= and MON_TIME

假设update TSK_TASK set STATUS_ID=UPDATE_TIME=now () where ID in ()几乎同时执行时本语句首先锁定簇索引(主键)由于需要更新STATUS_ID的值所以还需要锁定KEY_TSKTASK_MONTIME的某些索引记录

这样第一条语句锁定了KEY_TSKTASK_MONTIME的记录等待主键索引而第二条语句则锁定了主键索引记录而等待KEY_TSKTASK_MONTIME的记录在此情况下死锁就产生了

笔者通过拆分第一条语句解决死锁问题

先查出符合条件的IDselect ID from TSK_TASK where STATUS_ID= and MON_TIME < date_sub(now() INTERVAL minute)然后再更新状态update TSK_TASK set STATUS_ID= where ID in (…)

至此死锁问题彻底解决

[] []