故障分析 | MySQL锁等待超时一例分析
发布网友
发布时间：2024-10-23 05:56
共1个回答
热心网友
时间：1分钟前
开发反馈某业务持续性报锁等待超时，具体错误信息为单条insert语句锁等待超时，数据库版本为5.6，锁等待超时参数设置时长30秒。通过查看慢日志及show engine innodb status\G，发现存在批量插入动作，由于自增锁竞争导致死锁。了解到批量插入SQL为定时作业，且innodb_autoinc_lock_mode参数设置为1，这表示需要等到语句执行结束才释放自增锁。因此，解决锁等待超时的问题有两个方法：将参数值设置为2以允许批量语句执行期间释放自增锁，但该参数为静态参数需重启MySQL生效；或优化SQL执行时间，但当前SQL执行时间长达100+秒，扫描行数86w，结果集却为0，显示有优化空间。
分析SQL执行计划揭示，SQL包含子查询，使用派生表，首先执行子查询并将结果集存入临时表derived2，然后执行另一个子查询并将结果集存入临时表derived3，最后对derived2和derived3进行表关联，使用Block Nested Loop算法，即使表chat_black有复合索引也无法使用。如果MySQL版本为5.7，optimizer_switch参数增加了一个选项：derived_merge=on，满足一定条件时子查询可以被合并到外层查询。
通过分析发现SQL执行效率低下，导致锁等待超时。对SQL进行执行计划得到优化，执行时间从原来的100+秒降低到不到1秒。执行时间缩短后，自然不存在自增锁等待超时的问题，从而解决了锁等待超时现象。
