MySQL / 系统课时

演练死锁与事务改造

演练死锁与事务改造

本课通过两个并发事务以相反顺序更新同一组记录,稳定制造 InnoDB 死锁。你将从死锁日志还原锁获取顺序,再分别用统一访问顺序、缩小事务边界和索引优化降低死锁概率,并重新验证改造效果。

学习目标

  • 构造可重复的两事务死锁,并识别 MySQL 的自动回滚行为。
  • 使用 InnoDB 死锁信息还原“事务—资源”循环等待关系。
  • 通过访问顺序、事务边界与索引设计实施有针对性的改造。

构造一个稳定死锁

先建立一张账户表:

sql
DROP TABLE IF EXISTS account_balance;
CREATE TABLE account_balance (
  account_id BIGINT PRIMARY KEY,
  balance DECIMAL(12,2) NOT NULL
) ENGINE=InnoDB;

INSERT INTO account_balance VALUES
  (1, 1000.00),
  (2, 1000.00);

预期结果:

text
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 2 rows affected

打开两个 MySQL 会话。会话 A 先锁账户 1:

sql
START TRANSACTION;
UPDATE account_balance
SET balance = balance - 10
WHERE account_id = 1;

预期结果:

text
Query OK, 0 rows affected
Query OK, 1 row affected

会话 B 先锁账户 2:

sql
START TRANSACTION;
UPDATE account_balance
SET balance = balance - 20
WHERE account_id = 2;

预期结果:

text
Query OK, 0 rows affected
Query OK, 1 row affected

接着让会话 A 更新账户 2,此时它会等待会话 B:

sql
UPDATE account_balance
SET balance = balance + 10
WHERE account_id = 2;

预期结果:

text
语句进入锁等待。

保持会话 A 的语句处于等待状态,再让会话 B 更新账户 1:

sql
UPDATE account_balance
SET balance = balance + 20
WHERE account_id = 1;

预期结果:

text
两个事务中的一个会收到:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

实际被选为死锁牺牲者的事务可能不同。InnoDB 检测到等待环后会回滚其中一个完整事务,另一个事务的等待语句随后可以继续执行。收到 1213 的会话执行清理,仍存活的事务则提交:

sql
ROLLBACK;

预期结果:

text
Query OK, 0 rows affected

在未被回滚且阻塞语句已经返回的另一个会话执行:

sql
COMMIT;

预期结果:

text
Query OK, 0 rows affected

应用因此必须把死锁视为可重试的事务级错误,而不是只重新执行最后一条 SQL。

从日志还原等待环

死锁发生后立即查看最近一次死锁信息:

sql
SHOW ENGINE INNODB STATUS;

预期结果:

text
Status 字段中包含类似以下关键段落:
LATEST DETECTED DEADLOCK
*** (1) TRANSACTION:
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
...
*** (2) TRANSACTION:
...
*** (2) HOLDS THE LOCK(S):
...
*** WE ROLL BACK TRANSACTION (<ID>)

重点不是逐字阅读整段日志,而是提取四件事:事务 1 已持有什么锁、正在等待什么锁,事务 2 已持有什么锁、正在等待什么锁。把它们连起来,如果形成“事务 A 等 B 持有的资源,事务 B 又等 A 持有的资源”,就是死锁环。

MySQL 死锁等待环与改造方向

如果需要持续收集而不是只查看最近一次死锁,可以启用 innodb_print_all_deadlocks 将每次死锁写入 MySQL 错误日志,但生产环境应结合日志量和采集策略使用。

用统一访问顺序消除等待环

这个示例最直接的根因是两个事务访问资源的顺序相反。改造原则是:凡是一次事务要修改多个同类资源,都先确定资源集合,再按稳定顺序获取锁。

下面显式按照 account_id 从小到大锁定两个账户。所有涉及同一类账户资源的事务都必须遵循相同顺序:

sql
START TRANSACTION;

SELECT account_id, balance
FROM account_balance
WHERE account_id = 1
FOR UPDATE;

SELECT account_id, balance
FROM account_balance
WHERE account_id = 2
FOR UPDATE;

UPDATE account_balance
SET balance = balance - 10
WHERE account_id = 1;

UPDATE account_balance
SET balance = balance + 10
WHERE account_id = 2;

COMMIT;

预期结果:

text
Query OK, 0 rows affected
1 row in set
1 row in set
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 0 rows affected

当并发事务都以相同顺序获取账户 1、账户 2 的锁时,后来的事务仍可能等待,但不会由这两个资源形成“你等我、我等你”的反向闭环。锁等待不等于死锁:等待可以被前序事务提交自然解除,死锁则必须打破循环依赖。

缩短事务并修正索引边界

统一访问顺序解决的是资源顺序问题,真实系统还常有两类放大因素。第一类是事务中夹杂远程调用、复杂计算或用户交互,让已获取的锁被无谓持有;第二类是过滤条件缺少合适索引,扫描更多记录并扩大潜在锁冲突集合。

例如按账户主键更新时,可以先检查执行计划是否使用预期索引:

sql
EXPLAIN
UPDATE account_balance
SET balance = balance + 1
WHERE account_id = 2;

预期结果:

text
执行计划应显示通过 PRIMARY 索引定位目标记录,预计检查行数接近 1;具体列值会随 MySQL 版本和统计信息变化。

真实业务若通过非唯一条件更新,应检查 WHERE 条件的索引设计,并把网络请求、序列化、报表计算等与数据库写入无关的工作移到事务之外。这样即使无法完全消灭冲突,也能同时缩小锁范围和锁持有时间。

重新压测并验证改造

改造后不要只验证“手工复现不出来了”。应重新使用相同并发模型,记录死锁次数、事务重试次数、P95/P99 延迟和吞吐量,并确认最终数据仍满足业务约束。

先检查当前事务是否仍出现持续锁等待或异常长事务:

sql
SELECT
  trx_mysql_thread_id AS thread_id,
  trx_state,
  trx_started,
  trx_rows_locked,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

预期结果:

text
无活动事务时结果为空;压测期间可以出现 RUNNING 事务,但不应持续出现同一事务长时间处于 LOCK WAIT。

最后把应用重试限制在完整事务边界:收到 1213 后回滚整个事务,经过有上限的随机退避再重新执行,不能只重放最后一条 SQL。若压测仍有大量死锁,应继续根据死锁日志核对资源访问顺序、事务持续时间和索引命中情况,而不是单纯提高重试次数。

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

目录与阅读设置

阅读设置

17

记录一个想法

演练死锁与事务改造

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

登录哪都通