[已解决] MySQL 大表频繁修改索引列会造成锁等待吗？

破案

最后发现报表中有个更新销量的脚本，update中有个包含order_items的子查询导致锁表，一直没发现是因为关注的点都是splunk中出问题的时间段，如果把时间拉长到一个月，就能看到三四个密集的慢查询，都是锁释放导致的。把这个sql优化掉就解决了。

问题描述

场景是公司有一张大约500万行数据，140列的订单商品表（order_items），涉及的列有 id 自增主键， state_id 有索引idx_state_id 状态ID。

在用户操作自己的订单和后台工厂数据回调时会进行商品状态更新。

伪代码如下，根据主键条件查询数据，并更新另一个索引列，状态列索引区分度低。

foreach ($ordersItems as $orderItem) {
    // update order_items set state_id = ? where id = ?
}

实际运行时有时会出现 wait lock timeout exceeded 的报错，等待锁释放超过50s后自动失败。

猜测原因

死锁

leader 让我去排查原因，给出的方向是死锁/锁表或其他原因。我尝试了一些可能的原因，也详细查了资料，排除了一些可能的原因，但是还没有找到真正的问题原因。

首先排除是死锁导致的

理由一：死锁异常是检测到死锁deadlock try restarting transaction，日志中没有这样的记录。

理由二：死锁是两个事务排他锁冲突导致的，这里没有开启事务（至少是没有找到事务），所以我认为不是死锁导致的。

锁表

分析MySQL slow SQL log，除了在某一时段出现多次超过50s的SQL，这段时间内和前后都有5s - 40s左右的记录，他们之间也有不超过50s的间隔的记录。如果锁表日志应该更密集，也不应该只出现在某几个时段。

其他原因

一种可能是其他事务加锁没有释放，但按道理这样的查询会被记录到慢sql，除非在事务加锁后进行了如读取aws s3数据或其他耗时的操作。这样理论是可能的，但很难排查。

另一种可能是由于频繁操作索引列，会不会由于某些性能原因导致自己锁自己？比如更新 idx_state_id 时由于瞬间操作过于频繁或数据原因更新缓慢？

提问主要是希望大家能对其他原因的第二点进行讨论，模拟环境虽然比较困难，但我也在尝试，如果理论能证实/证伪就能节省很多时间，先感谢大家了。