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

核心提示： 此死锁问题涉及TSK_TASK表，该表用于保存系统监测任务，巧用MySQL InnoDB引擎锁机制解决死锁问题(2)，以下是相关字段及索引：ID：主键；MON_TIME：监测时间；STATUS_ID：任务状态；索引：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_

此死锁问题涉及TSK_TASK表，该表用于保存系统监测任务，以下是相关字段及索引：

ID：主键；

MON_TIME：监测时间；

STATUS_ID：任务状态；

索引：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

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

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

要点如下:

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

在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking；

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

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

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

当“update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME

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

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

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

先查出符合条件的ID：select ID from TSK_TASK where STATUS_ID=1061 and MON_TIME < date_sub(now(), INTERVAL 30 minute)；然后再更新状态：update TSK_TASK set STATUS_ID=1064 where ID in (….)

至此，死锁问题彻底解决。