MySQL 锁等待排查实战从实验到分析在日常开发中锁等待、死锁是 MySQL 令人头疼的问题。当数据库出现大量Waiting for table metadata lock或Lock wait timeout exceeded时快速定位并解决锁问题就显得尤为重要。本文将通过一张精心设计的实验表带你直观感受 InnoDB 的行锁、间隙锁、临键锁及死锁的触发场景并介绍排查锁等待的 SQL 命令与方法。一、设计一张锁学习专用表为了覆盖不同索引类型下的锁行为表结构需要同时具备主键、唯一索引、普通索引和低区分度索引。CREATETABLElock_study(idINTNOTNULLAUTO_INCREMENT,user_idINTNOTNULLCOMMENT用户ID普通索引,order_noVARCHAR(32)NOTNULLCOMMENT订单号唯一索引,amountDECIMAL(10,2)NOTNULLDEFAULT0.00COMMENT金额,statusTINYINTNOTNULLDEFAULT0COMMENT状态0-待支付 1-已支付 2-已取消,create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,update_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(id),UNIQUEKEYuk_order_no(order_no),KEYidx_user_id(user_id),KEYidx_status(status))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT行锁学习测试表;设计思路字段/索引观察目标PRIMARY KEY (id)主键记录锁Record Lock精准命中一行时的表现UNIQUE KEY (order_no)唯一索引上的记录锁以及唯一键冲突导致的锁等待KEY (user_id)普通索引观察**临键锁Next-Key Lock与间隙锁Gap Lock**的核心场景KEY (status)低区分度索引演示索引失效导致行锁退化为表锁的现象ENGINEInnoDBInnoDB 才支持行锁MyISAM 只有表锁二、插入实验数据插入 10 条数据user_id故意跳过4和6在普通索引上形成间隙方便观察间隙锁。INSERTINTOlock_study(user_id,order_no,amount,status)VALUES(1,ORD001,100.00,0),(1,ORD002,200.00,1),(2,ORD003,150.00,0),(3,ORD004,300.00,1),(3,ORD005,250.00,0),(3,ORD006,180.00,2),(5,ORD007,220.00,0),(5,ORD008,130.00,1),(7,ORD009,400.00,0),(8,ORD010,350.00,1);此时user_id分布为1,1,2,3,3,3,5,5,7,8间隙(3,5)、(5,7)等自然形成为后续实验做好准备。三、经典锁实验请务必在测试库运行实验前准备打开两个终端/会话窗口均使用REPEATABLE READ隔离级别MySQL 默认级别若需观察 RC 与 RR 差异可后续切换。-- 设置隔离级别可选SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;每个实验中事务1首先执行并持有锁不提交事务2随后执行观察阻塞或等待。实验1主键记录锁事务1BEGIN; UPDATE lock_study SET amount999 WHERE id3;事务2UPDATE lock_study SET amount888 WHERE id3;现象事务2 阻塞直到事务1COMMIT或ROLLBACK。主键精准匹配会加记录锁。实验2唯一索引记录锁事务1BEGIN; UPDATE lock_study SET amount999 WHERE order_noORD005;事务2UPDATE lock_study SET amount888 WHERE order_noORD005;现象事务2 阻塞唯一索引同样会上记录锁防止重复修改。实验3普通索引间隙锁RR 级别事务1BEGIN; SELECT * FROM lock_study WHERE user_id3 FOR UPDATE;事务2INSERT INTO lock_study (user_id, order_no, amount, status) VALUES (4, ORD011, 100, 0);现象事务2 阻塞因为user_id4落在3~5的间隙中间隙锁阻止了插入。若切换到READ COMMITTED隔离级别间隙锁消失事务2可成功插入。实验4临键锁范围查询事务1BEGIN; SELECT * FROM lock_study WHERE user_id BETWEEN 3 AND 5 FOR UPDATE;事务2INSERT INTO lock_study (user_id, order_no, amount, status) VALUES (4, ORD012, 100, 0);现象事务2 阻塞临键锁包含了记录锁和间隙锁锁住(3,5]以及相邻的间隙。实验5无索引导致表锁事务1BEGIN;SELECT * from lock_study WHERE amount 200 for UPDATE;事务2UPDATE lock_study set amount 2 where amount 200;现象如果amount字段未能走索引发生全表扫描InnoDB 会对所有扫描到的记录加锁导致大面积锁等待甚至表现为“表锁”效果。务必通过 EXPLAIN 查看执行计划确保使用索引。四、锁等待排查命令当线上出现锁等待时可以使用以下 SQL 迅速定位锁持有者和等待者。4.1 mysql 8.0-- 查看当前所有锁等待关系查看整体情况首先执行这个查看整体情况。---- 字段说明-- wait_started : 锁等待开始的时间-- wait_age : 已等待时长人类可读格式如 00:00:38-- wait_age_secs : 已等待秒数数值便于排序-- locked_table_schema : 被锁表所在的数据库名-- locked_table_name : 被锁的表名-- locked_table_partition : 被锁的表分区无分区则为NULL-- locked_table_subpartition : 被锁的子分区无则为NULL-- locked_index : 被锁的索引名PRIMARY表示主键-- locked_type : 锁类型RECORD行锁/ TABLE表锁-- waiting_trx_id : 正在等待锁的事务ID-- waiting_pid : 等待事务的MySQL线程ID用于KILL QUERY-- waiting_query : 被阻塞的具体SQL语句-- waiting_lock_mode : 等待的锁模式X排他/ S共享/ X,GAP间隙锁等-- waiting_trx_rows_locked : 该事务当前持有的行锁数量等待过程中依然持有自己的锁-- waiting_trx_rows_modified : 该事务已修改的行数未提交-- waiting_pin_LATEST : 内部使用可忽略-- blocking_trx_id : 持有锁、阻塞别人的事务ID-- blocking_pid : 阻塞事务的MySQL线程ID最重要的字段这是需要KILL的目标-- blocking_query : 阻塞事务正在执行的SQLNULL表示空闲即执行完没提交-- blocking_lock_mode : 阻塞事务持有的锁模式-- blocking_trx_rows_locked : 阻塞事务当前持有的行锁数量-- blocking_trx_rows_modified : 阻塞事务已修改的行数-- blocking_trx_age : 阻塞事务已存活时长人类可读格式-- blocking_pin_LATEST : 内部使用可忽略-- sql_kill_blocking_query : 预生成的KILL QUERY命令终止阻塞者的当前SQL事务保留-- sql_kill_blocking_connection : 预生成的KILL命令直接杀掉阻塞者的连接和事务锁全部释放---- 解读指南-- - 有结果返回 → 当前存在锁等待需要关注-- - wait_age_secs 10 → 等待时间过长需要介入处理-- - blocking_query IS NULL → 阻塞事务空闲未提交大概率是应用忘记 COMMIT-- - blocking_trx_age 30 → 阻塞事务存活过久建议立即 KILL---- 处理建议-- 1. 找到 blocking_pid阻塞线程ID-- 2. 直接复制执行 sql_kill_blocking_connection 字段中的 KILL 命令-- 3. 通知应用方排查代码确保事务及时提交--SELECT*FROMsys.innodb_lock_waits;更加详细的锁和事务情况查询-- 查询当前所有的锁持有和等待信息MySQL 8.0---- 字段说明-- ENGINE_LOCK_ID : 锁的唯一标识ID-- ENGINE_TRANSACTION_ID : 持有该锁的事务ID-- THREAD_ID : 持有锁的线程ID-- OBJECT_INSTANCE_BEGIN : 锁对象的内存地址-- LOCK_TYPE : 锁类型TABLE表锁/ RECORD行锁-- LOCK_MODE : 锁模式X排他/ S共享/ IX意向排他/ IS意向共享/ X,GAP间隙锁等-- LOCK_STATUS : 锁状态GRANTED已获得/ WAITING等待中-- LOCK_DATA : 锁定的数据如主键值 3或间隙范围---- 解读指南-- - LOCK_STATUS GRANTED 且 LOCK_MODE 包含 X → 持有排他锁可能阻塞其他事务-- - LOCK_STATUS WAITING → 该事务正在等待锁被释放-- - LOCK_DATA 显示具体的主键值或范围帮助你定位被锁定的具体行-- - 同一个 ENGINE_TRANSACTION_ID 可能有多条记录表示该事务持有多个锁---- 注意-- - 该表仅存在于 MySQL 8.0 的 performance_schema 中-- - MySQL 5.7 请使用 information_schema.innodb_locks--SELECT*FROMperformance_schema.data_locks;-- 查询锁等待的依赖关系用于定位谁阻塞了谁MySQL 8.0---- 字段说明-- REQUESTING_ENGINE_LOCK_ID : 正在等待的锁ID-- REQUESTING_ENGINE_TRANSACTION_ID : 正在等待锁的事务ID等待方-- REQUESTING_THREAD_ID : 等待锁的线程ID-- REQUESTING_QUERY_ID : 等待锁的查询ID-- REQUESTING_QUERY : 被阻塞的具体SQL语句-- REQUESTING_QUERY_NORMALIZED : 标准化后的SQL参数已替换为?-- BLOCKING_ENGINE_LOCK_ID : 持有锁的锁ID-- BLOCKING_ENGINE_TRANSACTION_ID : 持有锁的事务ID阻塞方-- BLOCKING_THREAD_ID : 持有锁的线程ID-- BLOCKING_QUERY_ID : 持有锁的查询ID-- BLOCKING_QUERY : 阻塞者的SQL语句NULL表示空闲-- BLOCKING_QUERY_NORMALIZED : 标准化后的阻塞者SQL---- 解读指南-- - 有结果返回 → 当前存在锁等待-- - REQUESTING_ENGINE_TRANSACTION_ID → 被阻塞的事务受害者-- - BLOCKING_ENGINE_TRANSACTION_ID → 持有锁的事务施害者需要KILL的对象-- - BLOCKING_QUERY IS NULL → 阻塞者空闲未提交极可能是应用忘记 COMMIT---- 注意-- - 该表仅存在于 MySQL 8.0 的 performance_schema 中-- - MySQL 5.7 请使用 information_schema.innodb_lock_waits-- - 日常排查推荐使用更方便的 sys.innodb_lock_waits 视图--SELECT*FROMperformance_schema.data_lock_waits;-- 查询当前所有活跃的事务信息用于监控长事务和锁持有者---- 字段说明-- trx_id : 事务ID唯一标识-- trx_state : 事务状态RUNNING运行中/ LOCK WAIT等待锁/ COMMITTED已提交/ ROLLING BACK回滚中-- trx_started : 事务开始时间-- trx_requested_lock_id : 事务正在等待的锁ID如果 trx_state LOCK WAIT 则有值-- trx_wait_started : 锁等待开始时间-- trx_weight : 事务权重锁数量 修改行数用于死锁回滚选择-- trx_mysql_thread_id : 事务对应的MySQL线程ID用于 KILL 命令-- trx_query : 事务正在执行的SQL语句NULL表示空闲-- trx_operation_state : 事务当前操作状态如 starting index read-- trx_tables_in_use : 事务使用的表数量-- trx_tables_locked : 事务锁定的表数量-- trx_lock_memory_bytes : 事务锁结构占用的内存字节数-- trx_rows_locked : 事务当前持有的行锁数量-- trx_rows_modified : 事务已修改的行数未提交-- trx_concurrency_tickets : 并发控制tickets-- trx_isolation_level : 事务隔离级别如 READ COMMITTED / REPEATABLE READ-- trx_unique_checks : 是否开启唯一约束检查-- trx_foreign_key_checks : 是否开启外键检查-- trx_last_foreign_key_error : 最后的外键错误信息-- trx_adaptive_hash_latched : 自适应哈希索引相关-- trx_adaptive_hash_timeout : 自适应哈希索引超时-- trx_is_read_only : 是否为只读事务-- trx_autocommit_non_locking : 是否自动提交非锁定读---- 解读指南-- - trx_state LOCK WAIT → 该事务被阻塞了查看 trx_requested_lock_id 定位等待的锁-- - trx_rows_locked 0 且 trx_state RUNNING → 持有锁且未提交可能阻塞别人-- - trx_query IS NULL 且 trx_rows_modified 0 → 已执行DML但未提交最常见的长事务问题-- - 按 TIMESTAMPDIFF(SECOND, trx_started, NOW()) 排序找出最老的事务-- - 长事务会阻止 Undo Log 清理导致磁盘空间膨胀和性能下降---- 常用排查组合-- 1. 查找阻塞源按 trx_started ASC 找最早的事务-- 2. 查找锁等待trx_state LOCK WAIT-- 3. 清理长事务KILL trx_mysql_thread_id--SELECT*FROMinformation_schema.INNODB_TRX;4.2 mysql 5.7-- 查看当前所有锁等待关系MySQL 5.7---- 字段说明-- wait_started : 锁等待开始的时间[citation:2]-- wait_age : 已等待时长TIME类型如 00:00:38[citation:2]-- wait_age_secs : 已等待秒数数值便于排序和阈值判断[citation:2]-- locked_table : 被锁表的名称含库名如 db.table[citation:2]-- locked_index : 被锁的索引名称PRIMARY 表示主键[citation:2]-- locked_type : 锁类型RECORD行锁/ TABLE表锁[citation:2]-- waiting_trx_id : 正在等待锁的事务ID[citation:2]-- waiting_trx_started : 等待事务的开始时间[citation:2]-- waiting_trx_age : 等待事务已存活时长TIME类型[citation:2]-- waiting_trx_rows_locked : 等待事务当前持有的行锁数量[citation:2]-- waiting_trx_rows_modified : 等待事务已修改的行数未提交[citation:2]-- waiting_pid : 等待事务的MySQL线程ID用于 KILL QUERY[citation:2]-- waiting_query : 被阻塞的具体SQL语句[citation:2]-- waiting_lock_id : 等待的锁ID[citation:2]-- waiting_lock_mode : 等待的锁模式X排他/ S共享/ X,GAP间隙锁等[citation:2]-- blocking_trx_id : 持有锁、阻塞别人的事务ID[citation:2]-- blocking_pid : 阻塞事务的MySQL线程ID最重要的字段这是需要KILL的目标[citation:2]-- blocking_query : 阻塞事务正在执行的SQLNULL 表示空闲即执行完没提交[citation:1][citation:2]-- blocking_lock_id : 持有锁的锁ID[citation:2]-- blocking_lock_mode : 阻塞事务持有的锁模式[citation:2]-- blocking_trx_started : 阻塞事务的开始时间[citation:2]-- blocking_trx_age : 阻塞事务已存活时长TIME类型[citation:2]-- blocking_trx_rows_locked : 阻塞事务当前持有的行锁数量[citation:2]-- blocking_trx_rows_modified : 阻塞事务已修改的行数[citation:2]-- sql_kill_blocking_query : 预生成的 KILL QUERY 命令终止阻塞者的当前SQL事务保留[citation:2]-- sql_kill_blocking_connection : 预生成的 KILL 命令直接杀掉阻塞者的连接和事务锁全部释放[citation:2]---- 解读指南-- - 有结果返回 → 当前存在锁等待需要关注[citation:1]-- - wait_age_secs 30 → 等待时间过长建议优先介入处理[citation:11]-- - blocking_query IS NULL → 阻塞事务空闲未提交极可能是应用忘记 COMMIT这是最常见的问题根源[citation:1]-- - blocking_trx_age 30 → 阻塞事务存活过久建议立即 KILL[citation:2]---- 处理建议-- 1. 找到 blocking_pid阻塞线程ID[citation:1]-- 2. 直接复制执行 sql_kill_blocking_connection 字段中的 KILL 命令[citation:2]-- 3. 如果 KILL 后仍有问题需排查应用代码确保事务及时提交---- 注意-- - 该视图的数据来源为 information_schema.innodb_trx、innodb_locks、innodb_lock_waits[citation:4]-- - MySQL 5.7.14 起底层 innodb_lock_waits 表已标记为废弃但视图本身仍可使用[citation:7]-- - MySQL 8.0 中该视图底层数据源迁移至 performance_schema.data_locks / data_lock_waits-- - 该视图仅排查 InnoDB 行锁MDL 锁等待需查询 sys.schema_table_lock_waits[citation:11]--SELECT*FROMsys.innodb_lock_waits;-- 查看当前所有活跃事务找出可能持有锁的元凶SELECTtrx_id,trx_state,trx_started,TIMESTAMPDIFF(SECOND,trx_started,NOW())ASseconds_running,trx_mysql_thread_id,trx_query,trx_rows_locked,trx_rows_modifiedFROMinformation_schema.INNODB_TRXWHEREtrx_stateRUNNINGORDERBYtrx_started;4.3 两者都可-- 查看里边锁和事务的相关描述SHOWENGINEINNODBSTATUS;五、实验记录5.1 实验一记录执行SELECT*FROMsys.innodb_lock_waits;输出再执行这个查看具体锁信息-- 查询当前所有的锁持有和等待信息MySQL 8.0---- 字段说明-- ENGINE : 存储引擎固定为 INNODB-- ENGINE_LOCK_ID : 锁的唯一标识ID-- ENGINE_TRANSACTION_ID : 持有或等待该锁的事务ID-- THREAD_ID : 持有或等待锁的线程ID对应 performance_schema.threads 表-- EVENT_ID : 事件ID-- OBJECT_SCHEMA : 数据库名-- OBJECT_NAME : 表名-- PARTITION_NAME : 分区名无则为空-- SUBPARTITION_NAME : 子分区名无则为空-- INDEX_NAME : 索引名称PRIMARY 表示主键空表示表锁-- OBJECT_INSTANCE_BEGIN : 锁对象的内存地址-- LOCK_TYPE : 锁类型TABLE表锁/ RECORD行锁-- LOCK_MODE : 锁模式IX意向排他/ X排他/ X,REC_NOT_GAP记录锁等-- LOCK_STATUS : 锁状态GRANTED已获得/ WAITING等待中-- LOCK_DATA : 锁定的数据如主键值 3表锁为空---- 解读指南-- - LOCK_STATUS GRANTED 且 LOCK_MODE 包含 X → 持有排他锁可能阻塞其他事务-- - LOCK_STATUS WAITING → 该事务正在等待锁被释放-- - LOCK_DATA 显示具体的主键值或范围帮助你定位被锁定的具体行-- - LOCK_TYPE TABLE 表示表级锁如意向锁 IX通常不阻塞业务但需注意---- 注意-- - 该表仅存在于 MySQL 8.0 的 performance_schema 中-- - MySQL 5.7 请使用 information_schema.innodb_locks--SELECT*FROMperformance_schema.data_locks;输出5.2 实验二记录-- 语句查询 SELECT * FROM performance_schema.data_locks;截图5.3 实验三记录5.4 实验四记录5.5 实验五记录六死锁排查对于实时的锁信息可以通过上面的命令排查出来有没有未释放的事务和锁等待信息但是对于历史过程发生的死锁在innodb status里边只能展示最近的一次死锁信息所以我们需要将过去发生的死锁情况记录下来-- 临时生效重启失效SETGLOBALinnodb_print_all_deadlocksON;# 编辑 MySQL 配置文件Linux 通常在 /etc/mysql/my.cnf 或 /etc/my.cnfsudo vi/etc/mysql/my.cnf# 在 [mysqld] 段落下添加[mysqld]innodb_print_all_deadlocksON# 保存后重启 MySQLsudo systemctl restart mysql查看死锁错误日志的文件路径-- 查看错误日志文件路径SHOWVARIABLESLIKElog_error;然后排查就可以了六、避坑与建议学习路径所有实验均在事务中执行执行完毕后及时COMMIT或ROLLBACK释放锁。切换隔离级别对比READ COMMITTED和REPEATABLE READ下间隙锁的行为差异。避免在线上库跑实验以免造成业务阻塞。使用 EXPLAIN 确认索引使用情况防止隐式类型转换或低区分度字段导致索引失效进而造成锁升级。建议学习顺序先执行实验1、2理解精准命中的记录锁在 RR 隔离级别下运行实验3直观感受间隙锁切换至 RC 隔离级别重新运行实验3观察间隙锁消失最后执行实验5理解索引失效带来的严重后果。