Linux服务器MySQL锁等待频繁,如何区分事务未提交与索引问题
MySQL 锁等待突然增多时,受影响的不一定只有一条 SQL:写入可能排队,连接池里的连接被占满,随后读写请求也会超时。排查的关键不是看到“锁等待”就立刻加索引或重启,而是先找出等待关系,再判断阻塞事务为何没有结束,以及执行语句是否扫描、锁定了过多记录。
通常要区分两类情况:一类是事务已经持有锁,却迟迟没有提交或回滚;另一类是事务仍在执行,但索引或扫描范围导致它锁住了超出预期的记录。两者也可能同时存在:索引不合适的语句先锁住大量记录,应用随后又长时间不提交。以下判断应以实际锁等待链和执行计划为依据,不要只凭单个指标下结论。
先确认症状:等待发生在哪里
先记录故障时间、受影响的业务、MySQL 错误码和等待持续时间。常见的锁等待超时错误是 1205,死锁错误是 1213。它们都涉及并发事务,但含义不同:超时表示等待超过设定时限;死锁表示数据库检测到循环等待并选择回滚一个事务。不能把二者都归结为“索引差”。
应用侧还要确认影响范围:是单个接口变慢,还是同一张表上的多类写请求都在排队;是偶发尖峰,还是持续增长;连接池的活跃连接、等待连接和超时数量是否同步增加。连接池耗尽可能是锁等待的后果,不应仅凭连接数高就认定连接池配置有问题。
排查期间尽量保留发生故障时的时间点、SQL 摘要和监控记录。锁等待消失后,现场视图可能已经无法还原当时的阻塞关系。
按问题树分层
| 层级 | 观察什么 | 初步指向 |
|---|---|---|
| 网络与连接 | 应用到数据库是否断连、重连或出现网络超时 | 连接问题可能放大请求堆积,但本身不能证明存在行锁等待 |
| Linux 系统 | CPU、磁盘延迟、内存和 I/O 是否异常 | 资源瓶颈可能让持锁事务执行更久,也可能与锁问题并存 |
| MySQL 服务 | 等待锁、长事务、死锁和活跃事务 | 确认阻塞者、等待者及所涉及的表和索引 |
| 应用与 SQL | 事务边界、自动提交、执行计划和请求耗时 | 判断事务未结束,还是语句扫描与锁定范围过大 |
诊断顺序建议由外到内:先确认连接和系统是否异常,再查 MySQL 的等待链,最后结合应用事务边界和 SQL 执行计划定位原因。即便 CPU 或磁盘利用率偏高,也只能说明系统可能让事务变慢,不能单独证明锁等待的根因。

建立原因树:找出谁在等、谁持锁
MySQL 8.0 可从 performance_schema.data_lock_waits 查看等待关系,并关联锁对象。下面是只读查询示例;需要相应的 Performance Schema 查看权限。
SELECT
w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
r.OBJECT_SCHEMA AS waiting_schema,
r.OBJECT_NAME AS waiting_table,
r.INDEX_NAME AS waiting_index,
r.LOCK_MODE AS waiting_lock_mode,
r.LOCK_DATA AS waiting_lock_data,
w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
b.OBJECT_SCHEMA AS blocking_schema,
b.OBJECT_NAME AS blocking_table,
b.INDEX_NAME AS blocking_index,
b.LOCK_MODE AS blocking_lock_mode,
b.LOCK_DATA AS blocking_lock_data
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
ON r.ENGINE = w.ENGINE
AND r.ENGINE_TRANSACTION_ID = w.REQUESTING_ENGINE_TRANSACTION_ID
AND r.LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
ON b.ENGINE = w.ENGINE
AND b.ENGINE_TRANSACTION_ID = w.BLOCKING_ENGINE_TRANSACTION_ID
AND b.LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;
结果中的等待事务是被阻塞的一方,阻塞事务是当前持有相关锁的一方。重点记录表名、索引名、锁模式和锁数据,并继续把事务 ID 对应到连接及事务状态。锁数据为空或输出不完整时,不要因此认定没有行锁;继续检查事务列表和 InnoDB 状态。
可在 MySQL 8.0 中查看当前事务和连接信息:
SELECT
t.TRX_ID,
t.TRX_STATE,
t.TRX_STARTED,
t.TRX_WAIT_STARTED,
t.TRX_MYSQL_THREAD_ID AS connection_id,
t.TRX_ROWS_LOCKED,
t.TRX_ROWS_MODIFIED,
t.TRX_QUERY,
p.USER,
p.HOST,
p.DB,
p.COMMAND,
p.TIME,
p.STATE,
p.INFO
FROM information_schema.innodb_trx AS t
LEFT JOIN information_schema.processlist AS p
ON p.ID = t.TRX_MYSQL_THREAD_ID
ORDER BY t.TRX_STARTED;
MySQL 5.7 可使用 information_schema.innodb_lock_waits 关联 innodb_trx 查看等待与阻塞事务。不同小版本和权限下,可见字段或视图权限可能不同;先核对服务器版本和实际字段:
SELECT VERSION();
SHOW COLUMNS FROM information_schema.innodb_lock_waits;
SHOW COLUMNS FROM information_schema.innodb_trx;
也可查看 InnoDB 最近状态,辅助确认锁等待、死锁和事务信息:
SHOW ENGINE INNODB STATUS\G
这份输出是当前或最近一次状态的线索,不等于完整历史记录;若问题已消失,内容可能不足以还原故障现场。
逐层排除:未提交事务还是索引问题
分支一:事务持锁后没有及时结束
如果阻塞事务的开始时间明显早于等待开始时间,TRX_QUERY 为空或进程列表显示 Sleep,同时仍有事务记录和已锁定行数,这些现象支持“连接处于空闲状态,但事务尚未结束”的判断。它们不是单独的定论:事务可能正在执行但当前没有显示语句,也可能因客户端状态或观测时点而看起来空闲。
重点核对应用代码是否出现以下情形:
- 开启事务后,执行了数据库操作,却在调用远程接口、等待用户输入或处理大量业务逻辑后才提交。
- 某个异常分支漏掉提交或回滚,连接归还连接池时仍处于事务中。
- 手动事务与连接池的自动提交设置不一致,导致请求结束后事务仍然存在。
- 批量处理事务过大,单次持锁时间随处理数量增加。
判断时将事务开始时间、连接 ID、应用日志中的请求 ID 和事务结束日志对齐。若持锁事务长时间没有当前 SQL,优先检查连接归还和事务提交路径;若它仍在执行,则还要看 SQL 本身是否扫描范围过大,不能只按“未提交”处理。
事务长不一定是业务代码忘记提交。它也可能在等待磁盘 I/O、CPU 调度或外部依赖,持锁时间因此被拉长。应结合系统指标判断原因,但不要把系统资源问题与事务未结束混为一谈。
分支二:SQL 扫描或锁定范围过大
若阻塞事务持续运行,等待锁集中在同一张表或索引,且语句按非唯一字段筛选、使用范围条件或扫描行数明显偏大,应检查执行计划和索引。InnoDB 通常锁定索引记录;在可重复读隔离级别下,范围扫描还可能涉及间隙锁或 next-key 锁。锁的具体范围受隔离级别、语句类型、索引和执行计划影响,不能只根据一条 SQL 的 WHERE 条件推断。

例如,表有 account_id、status 和 updated_at 等字段,更新语句按 account_id 精确定位通常比按低选择性状态字段扫描更容易控制范围。但是否需要索引、联合索引如何排序,必须根据实际过滤条件、排序、数据分布和写入模式判断。为锁等待盲目增加索引也有代价:写入需维护更多索引,磁盘占用增加,执行计划也可能改变。
对有问题的语句,先用等价查询检查访问路径和估算扫描量:
EXPLAIN
SELECT id
FROM orders
WHERE account_id = 12345
AND status = 'pending';
如果服务器支持 EXPLAIN ANALYZE,可在确认语句适合执行且了解其执行成本后使用;它会实际运行被分析的查询,不应在高负载生产环境中不加评估地对重查询执行。
检查执行计划中的 key、type、rows 和 filtered 等信息,并与表规模及实际业务数据分布对照。rows 是估算值,不能直接当作精确扫描行数。必要时在可控环境结合实际执行情况分析。还要确认 SQL 谓词是否对索引列做了函数或类型转换、是否存在隐式类型转换,以及联合索引的列顺序是否符合查询条件;这些因素可能让预期索引无法有效使用。
若锁等待主要出现在插入或更新,还需检查唯一索引冲突、外键关联和被更新索引列。此类场景不一定表现为明显的全表扫描,但仍可能因并发检查或关联操作产生等待。
分支三:系统变慢,让锁持有时间变长
在等待发生时查看 Linux 资源,而不是只看故障后的平均值。可用以下只读命令观察 CPU、内存、磁盘 I/O 和进程状态:
uptime
free -h
vmstat 1 5
iostat -xz 1 5
iostat 通常由 sysstat 软件包提供,若系统未安装,不要为一次排查直接在生产机上进行未经评估的软件变更。关注指标的持续趋势:例如磁盘等待和设备利用率持续升高、内存压力或交换活动明显增加,可能说明事务执行被系统资源拖慢。单个采样点或一个高百分比不能独立确认根因,需与 MySQL 事务开始时间、SQL 耗时和业务请求时间对齐。
网络层同理:如果应用连接重置、重连增多,可能造成请求重试和连接池堆积;但只有 MySQL 错误、连接日志和锁等待视图相互印证,才能说明锁等待与连接故障之间的关系。
针对承载 MySQL 业务的服务器资源,A5数据提供香港、美国、日本、韩国、中国台湾、新加坡和马来西亚等地区的物理服务器租用,可按数据库规模与并发负载组合 Xeon 或 AMD EPYC 处理器、较大内存与 SSD/NVMe 存储,并提供不同带宽线路,从计算、磁盘到网络资源,为业务后台、数据库及接口服务提供部署基础。
定位根因:用等待链与执行证据交叉验证
对每一条代表性等待记录,至少回答四个问题:哪条事务在等待;哪条事务在阻塞;锁属于哪张表及哪个索引;阻塞事务为何还没有结束。随后将它与应用日志、SQL 执行计划和系统指标交叉验证。
| 观察结果 | 更支持的方向 | 下一步 |
|---|---|---|
| 阻塞事务开始很早、当前无 SQL、仍持有锁 | 事务未提交或连接归还异常 | 按连接 ID 查应用请求、事务提交/回滚日志及连接池行为 |
| 阻塞事务正在执行,相关语句扫描范围大 | 索引或 SQL 访问路径问题 | 检查执行计划、实际条件、索引选择性和锁定范围 |
| 同一事务既扫描范围大,又长时间未结束 | SQL 与事务边界共同作用 | 同时缩小扫描范围、缩短事务内非数据库操作时间 |
| 磁盘或 CPU 同时持续异常 | 系统资源可能延长持锁时间 | 对齐资源曲线与事务耗时,进一步定位资源瓶颈 |
| 只有连接数升高,没有可对应的锁等待链 | 不能据此判定为数据库锁问题 | 检查连接池、慢查询、网络重试及应用排队 |
对一个参考场景:若等待链显示多条更新都被同一连接阻塞,该连接对应事务已持续数分钟、进程状态为空闲,且事务锁定行数非零,优先核对该连接的事务是否漏提交。若阻塞事务在持续运行,相关 SQL 执行计划显示访问路径不符合预期,则索引或条件范围更值得优先排查。以上是判断方式示例,不代表任何特定服务器的检查结果。
处理与验证:先控制影响,再确认恢复
确认根因后,优先由应用侧结束异常事务,或在维护窗口修正事务边界和 SQL。对生产连接执行 KILL 可能触发事务回滚;回滚耗时与已修改数据量、磁盘状态等有关,等待期间锁可能仍然存在,还可能影响该连接承载的其他工作。执行前应确认连接归属、业务影响和事务范围,并与应用负责人协同;不要把重启 MySQL 或批量终止连接当作常规处理方式。
索引调整或 SQL 改写前,应先核对表结构、索引与执行计划,并评估变更对写入负载和存储的影响。生产变更需要遵循现有的备份、发布与回退流程:保留原 SQL 和索引定义,先在适当环境验证,再按变更方案实施;若执行计划或业务指标恶化,按预先验证过的回退方案恢复。删除或重建索引可能影响查询和写入,不能在未评估影响范围时直接操作。
恢复验证不能只看错误码短暂消失。持续观察一段覆盖典型业务负载的时间,确认:
- 等待关系和长事务数量回到可接受范围,没有新的同类阻塞链。
- 受影响 SQL 的执行耗时、扫描规模和错误率改善,而不是仅靠增加超时掩盖排队。
- 连接池等待、活跃连接和请求超时恢复到日常波动范围。
- CPU、内存和磁盘指标没有因补索引、重试或回滚产生新的持续压力。
复发监控应至少覆盖锁等待数量与持续时间、最长事务年龄、长时间空闲但仍在事务中的连接、死锁与锁等待超时计数,以及关键 SQL 的耗时和扫描特征。阈值应结合业务的正常事务时长与流量基线设定;发现等待增多时,保留时间、连接 ID、SQL 摘要和锁对象信息,才能在问题消失前把“事务未结束”和“访问范围过大”区分开来。
本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/4582.html



