企业官网建设流程全解析

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))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='行锁学习测试表';

设计思路：

二、插入实验数据

插入 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：主键记录锁

• 事务1：BEGIN; UPDATE lock_study SET amount=999 WHERE id=3;
• 事务2：UPDATE lock_study SET amount=888 WHERE id=3;
• 现象：事务2 阻塞，直到事务1COMMIT或ROLLBACK。主键精准匹配会加记录锁。

实验2：唯一索引记录锁

• 事务1：BEGIN; UPDATE lock_study SET amount=999 WHERE order_no='ORD005';
• 事务2：UPDATE lock_study SET amount=888 WHERE order_no='ORD005';
• 现象：事务2 阻塞，唯一索引同样会上记录锁，防止重复修改。

实验3：普通索引间隙锁（RR 级别）

• 事务1：BEGIN; SELECT * FROM lock_study WHERE user_id=3 FOR UPDATE;
• 事务2：INSERT INTO lock_study (user_id, order_no, amount, status) VALUES (4, 'ORD011', 100, 0);
• 现象：事务2 阻塞，因为user_id=4落在3~5的间隙中，间隙锁阻止了插入。若切换到READ COMMITTED隔离级别，间隙锁消失，事务2可成功插入。

实验4：临键锁（范围查询）

• 事务1：BEGIN; SELECT * FROM lock_study WHERE user_id BETWEEN 3 AND 5 FOR UPDATE;
• 事务2：INSERT INTO lock_study (user_id, order_no, amount, status) VALUES (4, 'ORD012', 100, 0);
• 现象：事务2 阻塞，临键锁包含了记录锁和间隙锁，锁住(3,5]以及相邻的间隙。

实验5：无索引导致表锁

• 事务1：`BEGIN;SELECT * from lock_study WHERE amount > 200 for UPDATE;
• 事务2：UPDATE 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 : 阻塞事务正在执行的SQL（NULL表示空闲，即执行完没提交）-- 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 : 阻塞事务正在执行的SQL（NULL 表示空闲，即执行完没提交）[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_state='RUNNING'ORDERBYtrx_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_deadlocks=ON;

# 编辑 MySQL 配置文件（Linux 通常在 /etc/mysql/my.cnf 或 /etc/my.cnf）sudo vi/etc/mysql/my.cnf# 在 [mysqld] 段落下添加：[mysqld]innodb_print_all_deadlocks=ON# 保存后重启 MySQLsudo systemctl restart mysql

查看死锁错误日志的文件路径：

-- 查看错误日志文件路径SHOWVARIABLESLIKE'log_error';

然后排查就可以了

六、避坑与建议学习路径

1. 所有实验均在事务中执行，执行完毕后及时COMMIT或ROLLBACK释放锁。
2. 切换隔离级别，对比READ COMMITTED和REPEATABLE READ下间隙锁的行为差异。
3. 避免在线上库跑实验，以免造成业务阻塞。
4. 使用 EXPLAIN 确认索引使用情况，防止隐式类型转换或低区分度字段导致索引失效，进而造成锁升级。

建议学习顺序：

• 先执行实验1、2，理解精准命中的记录锁；
• 在 RR 隔离级别下运行实验3，直观感受间隙锁；
• 切换至 RC 隔离级别，重新运行实验3，观察间隙锁消失；
• 最后执行实验5，理解索引失效带来的严重后果。