MySQL / 系统课时
建立事务与锁观测基线
建立事务与锁观测基线
锁竞争发生时,单看一条慢 SQL 很难判断谁在等谁。本节把事务状态、锁对象和 InnoDB 全局快照分层观测,并用一次可控的行锁等待验证查询结果,形成问题发生前后可对照的诊断基线。
学习目标
- 区分
INNODB_TRX、data_locks、data_lock_waits与 InnoDB Status 的职责 - 在锁等待现场识别等待事务、阻塞事务和具体锁对象
- 在解除阻塞后确认观测结果恢复到无等待状态
固定三层观测口径
事务与锁信息不是一张表就能回答完整。INFORMATION_SCHEMA.INNODB_TRX 更适合看事务状态与开始时间;Performance Schema 的锁表用于定位持有锁、等待锁和阻塞关系;SHOW ENGINE INNODB STATUS 则提供 InnoDB 当下的综合快照。

| 观测来源 | 主要回答的问题 | 常用对象 |
|---|---|---|
INFORMATION_SCHEMA |
哪些 InnoDB 事务正在运行或等待 | INNODB_TRX |
performance_schema |
哪个锁在等待、哪个事务在阻塞 | data_locks、data_lock_waits |
| InnoDB Status | 引擎当前事务、锁等待及其他内部状态 | SHOW ENGINE INNODB STATUS |
这些信息变化很快,因此不要把不同时间点采集到的事务 ID、锁 ID 和 SQL 强行拼成一个永久一致的快照。诊断时应尽量连续采集,并记录采集时间。
记录无竞争基线
先用独立的观察连接记录版本、隔离级别、当前事务数和锁等待数。实验环境最好没有其他业务连接,这样前后变化更容易解释。
SELECT
VERSION() AS mysql_version,
@@transaction_isolation AS isolation_level,
@@autocommit AS autocommit;
SELECT COUNT(*) AS active_trx
FROM information_schema.innodb_trx;
SELECT COUNT(*) AS lock_waits
FROM performance_schema.data_lock_waits;预期结果:
mysql_version: <VERSION>
isolation_level: <VALUE>
autocommit: <VALUE>
active_trx: <VALUE>
lock_waits: 0<VERSION> 和 <VALUE> 取决于当前实例与会话配置;在独立实验环境里,关键基线是没有人为制造的锁等待。
制造一个可观测的锁等待
本课独立使用一个最小表,不依赖其他课时。先初始化数据:
CREATE DATABASE IF NOT EXISTS tx_obs;
USE tx_obs;
DROP TABLE IF EXISTS lock_demo;
CREATE TABLE lock_demo (
id INT PRIMARY KEY,
value_text VARCHAR(32) NOT NULL
) ENGINE = InnoDB;
INSERT INTO lock_demo(id, value_text) VALUES (1, 'baseline');
SELECT id, value_text FROM lock_demo;预期结果:
id | value_text
1 | baseline在会话 A 持有目标行的写锁:
USE tx_obs;
SET autocommit = 0;
START TRANSACTION;
UPDATE lock_demo
SET value_text = 'held-by-a'
WHERE id = 1;预期结果:
UPDATE 成功,会话 A 保持事务未提交在会话 B 请求同一行,并保持窗口等待:
USE tx_obs;
SET autocommit = 0;
SET SESSION innodb_lock_wait_timeout = 60;
START TRANSACTION;
UPDATE lock_demo
SET value_text = 'waiting-b'
WHERE id = 1;预期结果:
UPDATE: 等待中,暂时没有完成结果从事务、锁和引擎三层定位
此时不要在 A 或 B 中继续执行诊断 SQL,而是使用第三个观察连接。先看事务状态,再看锁对象与阻塞关系:
SELECT
trx_id,
trx_state,
trx_started,
trx_wait_started,
trx_mysql_thread_id,
trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;
SELECT
ENGINE_TRANSACTION_ID,
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
LOCK_TYPE,
LOCK_MODE,
LOCK_STATUS,
LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = 'tx_obs'
AND OBJECT_NAME = 'lock_demo'
ORDER BY ENGINE_TRANSACTION_ID, LOCK_STATUS;
SELECT
REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
REQUESTING_THREAD_ID AS waiting_thread,
BLOCKING_THREAD_ID AS blocking_thread
FROM performance_schema.data_lock_waits;预期结果:
INNODB_TRX:
至少能看到实验中的两个事务,其中会话 B 对应事务处于 LOCK WAIT
data_locks:
同一目标记录相关锁中可看到 GRANTED 与 WAITING 状态
data_lock_waits:
waiting_trx <ID_B_TRX> -> blocking_trx <ID_A_TRX>事务 ID、线程 ID、锁模式和 LOCK_DATA 的具体值会随版本、索引和现场变化,不要依赖这些动态 ID 的格式做长期规则。
再获取一次 InnoDB 全局快照。以下写法适用于 mysql 命令行客户端:
SHOW ENGINE INNODB STATUS\G预期结果:
<...> INNODB MONITOR OUTPUT
<...>
TRANSACTIONS
<...>
LOCK WAIT
<...>InnoDB Status 内容很多,锁竞争现场先定位 TRANSACTIONS 及等待相关文本即可,不必从头逐行阅读。
解除阻塞并验证恢复
在会话 A 提交,释放它持有的锁:
COMMIT;预期结果:
会话 A: COMMIT 成功
会话 B: 之前等待的 UPDATE 随后完成随后在会话 B 回滚自己的更新:
ROLLBACK;预期结果:
会话 B: ROLLBACK 成功最后从观察连接重新检查等待关系和业务数据:
SELECT COUNT(*) AS lock_waits
FROM performance_schema.data_lock_waits;
SELECT id, value_text
FROM tx_obs.lock_demo
WHERE id = 1;预期结果:
lock_waits
0
id | value_text
1 | held-by-a如果等待关系已恢复为空,且业务表只保留 A 已提交的数据,说明这次现场从“无等待基线 → 明确阻塞 → 解除阻塞 → 恢复基线”的观测链路完整可复现。