MySQL / 系统课时

建立事务与锁观测基线

建立事务与锁观测基线

锁竞争发生时,单看一条慢 SQL 很难判断谁在等谁。本节把事务状态、锁对象和 InnoDB 全局快照分层观测,并用一次可控的行锁等待验证查询结果,形成问题发生前后可对照的诊断基线。

学习目标

  • 区分 INNODB_TRX、data_locks、data_lock_waits 与 InnoDB Status 的职责
  • 在锁等待现场识别等待事务、阻塞事务和具体锁对象
  • 在解除阻塞后确认观测结果恢复到无等待状态

固定三层观测口径

事务与锁信息不是一张表就能回答完整。INFORMATION_SCHEMA.INNODB_TRX 更适合看事务状态与开始时间;Performance Schema 的锁表用于定位持有锁、等待锁和阻塞关系;SHOW ENGINE INNODB STATUS 则提供 InnoDB 当下的综合快照。

MySQL 事务与锁的三层观测来源

观测来源 主要回答的问题 常用对象
INFORMATION_SCHEMA 哪些 InnoDB 事务正在运行或等待 INNODB_TRX
performance_schema 哪个锁在等待、哪个事务在阻塞 data_locks、data_lock_waits
InnoDB Status 引擎当前事务、锁等待及其他内部状态 SHOW ENGINE INNODB STATUS

这些信息变化很快,因此不要把不同时间点采集到的事务 ID、锁 ID 和 SQL 强行拼成一个永久一致的快照。诊断时应尽量连续采集,并记录采集时间。

记录无竞争基线

先用独立的观察连接记录版本、隔离级别、当前事务数和锁等待数。实验环境最好没有其他业务连接,这样前后变化更容易解释。

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;

预期结果:

text
mysql_version: <VERSION>
isolation_level: <VALUE>
autocommit: <VALUE>
active_trx: <VALUE>
lock_waits: 0

<VERSION> 和 <VALUE> 取决于当前实例与会话配置;在独立实验环境里,关键基线是没有人为制造的锁等待。

制造一个可观测的锁等待

本课独立使用一个最小表,不依赖其他课时。先初始化数据:

sql
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;

预期结果:

text
id | value_text
1  | baseline

在会话 A 持有目标行的写锁:

sql
USE tx_obs;
SET autocommit = 0;
START TRANSACTION;
UPDATE lock_demo
SET value_text = 'held-by-a'
WHERE id = 1;

预期结果:

text
UPDATE 成功,会话 A 保持事务未提交

在会话 B 请求同一行,并保持窗口等待:

sql
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;

预期结果:

text
UPDATE: 等待中,暂时没有完成结果

从事务、锁和引擎三层定位

此时不要在 A 或 B 中继续执行诊断 SQL,而是使用第三个观察连接。先看事务状态,再看锁对象与阻塞关系:

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;

预期结果:

text
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 命令行客户端:

sql
SHOW ENGINE INNODB STATUS\G

预期结果:

text
<...> INNODB MONITOR OUTPUT
<...>
TRANSACTIONS
<...>
LOCK WAIT
<...>

InnoDB Status 内容很多,锁竞争现场先定位 TRANSACTIONS 及等待相关文本即可,不必从头逐行阅读。

解除阻塞并验证恢复

在会话 A 提交,释放它持有的锁:

sql
COMMIT;

预期结果:

text
会话 A: COMMIT 成功
会话 B: 之前等待的 UPDATE 随后完成

随后在会话 B 回滚自己的更新:

sql
ROLLBACK;

预期结果:

text
会话 B: ROLLBACK 成功

最后从观察连接重新检查等待关系和业务数据:

sql
SELECT COUNT(*) AS lock_waits
FROM performance_schema.data_lock_waits;

SELECT id, value_text
FROM tx_obs.lock_demo
WHERE id = 1;

预期结果:

text
lock_waits
0

id | value_text
1  | held-by-a

如果等待关系已恢复为空,且业务表只保留 A 已提交的数据,说明这次现场从“无等待基线 → 明确阻塞 → 解除阻塞 → 恢复基线”的观测链路完整可复现。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

建立事务与锁观测基线

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通