十八岁的不忘初心 成长笔记

MySQL 笔记：行锁、表锁与死锁的排查思路

· Mysql笔记 MySQL 事务 锁 · 4 阅读 · 约 7 分钟

前言

锁的问题难在"复现一次很麻烦，出事时又只有几分钟排障窗口"。这篇把 InnoDB 的锁种类、加锁条件，以及出事时该查哪几张表记下来。

表锁与元数据锁

表锁最容易理解，MyISAM、MEMORY 只有表锁；InnoDB 也会被显式加表锁：

LOCK TABLES article READ;
FLUSH TABLES WITH READ LOCK;   -- 备份时常用，会挡住所有写
UNLOCK TABLES;

真正麻烦的是 MDL 元数据锁（MySQL 5.5 之后引入）。任何 DML 都会持有一个表的 MDL 读锁，DDL 要拿写锁，两者互斥。典型事故是这样的顺序：

  1. 会话 A 开了一个长事务，正在读写 article 表
  2. 会话 B 执行 ALTER TABLE article ADD COLUMN ...，被 A 的 MDL 挡住，进入等待
  3. 后面所有对 article 的查询都要排队在 ALTER 之后，整张表瞬间"卡死"

SHOW FULL PROCESSLIST 里看到大量 Waiting for table metadata lock 就是这种局面。先杀掉未完成的 DDL，再处理那个长事务，别反过来。

行锁加在哪：三种锁

InnoDB 的行锁是加在索引记录上的，具体形态取决于走哪个索引、匹配方式：

  • 记录锁：唯一索引或主键做等值且命中，只锁那一条
  • 间隙锁：锁住索引记录之间的空隙，只阻止插入，不锁已有记录
  • next-key 锁：记录锁加间隙锁，左开右闭的区间

大致规则（REPEATABLE READ 下）：

语句形态 加锁情况
主键/唯一索引等值且命中 记录锁
主键/唯一索引等值未命中 间隙锁
非唯一索引等值 命中记录的 next-key 锁，加上右侧间隙
范围条件 覆盖区间的 next-key 锁
条件列没有索引 扫描聚簇索引，扫过的行全部加锁，接近锁全表

最后一条是事故重灾区：给一个没建索引的列写 UPDATE ... WHERE status = 0，锁范围比想象中大得多。改之前先 EXPLAIN 确认 key 不为 NULL。

共享锁和排他锁的实际入口是这几句：

SELECT ... LOCK IN SHARE MODE;   -- 5.7 写法，8.0 是 FOR SHARE
SELECT ... FOR UPDATE;           -- 排他
UPDATE / DELETE / INSERT         -- 隐式排他锁

死锁与锁等待

死锁是两个以上事务互相持有对方需要的锁并形成环。InnoDB 默认开 innodb_deadlock_detect，检测到环之后回滚代价较小的那个事务，客户端拿到 ERROR 1213 (40001): Deadlock found。锁等待超时则是另一个错误 ERROR 1205，阈值由 innodb_lock_wait_timeout 控制，默认 50 秒。两者不是一回事，日志里要分清。

经典复现：两个事务按相反顺序更新同一批行。

-- 会话 A
START TRANSACTION;
UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance - 10 WHERE id = 2;   -- 等 B 释放 2
COMMIT;

-- 会话 B
START TRANSACTION;
UPDATE account SET balance = balance + 10 WHERE id = 2;
UPDATE account SET balance = balance + 10 WHERE id = 1;   -- 等 A 释放 1，成环
COMMIT;

排查步骤

第一步看最近一次死锁的完整现场：

SHOW ENGINE INNODB STATUS\G

输出里 LATEST DETECTED DEADLOCK 段落会给出两个事务各自持有和等待的锁、以及被回滚的是哪个。这段内容存在内存里，只保留最近一次，出现告警要立刻抓。

第二步看当下谁在等谁。5.7 可以查 information_schema 里三张表（实例装了 sys 库的话直接查 sys.innodb_lock_waits 更省事，我本机这台没装，就用原始表）：

SELECT r.trx_wait_started, w.requesting_trx_id, w.blocking_trx_id,
       r.trx_query AS waiting_query, b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query,
       trx_rows_locked, trx_rows_modified
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

拿到 trx_mysql_thread_id 之后，确认过确实是可以中断的业务，再 KILL <thread_id>。

第三步定位锁对象：

SELECT lock_table, lock_index, lock_type, lock_mode, lock_id, lock_trx_id
FROM information_schema.INNODB_LOCKS
WHERE lock_table = 'blog_php/article';

performance_schema.metadata_locks 表在 5.7 里虽然存在，但 mdl 采集默认不开，需要先把 wait/lock/metadata/sql/mdl 这个 instrument 打开才有数据。

降低概率的几条经验

  • 多个事务按同一顺序访问同一批行，比如都按主键升序更新
  • 事务尽量短：把远程调用、循环处理挪出事务
  • 更新一定走索引，且只更新必要的行
  • 大事务拆成按主键区间的小批量
  • 能接受弱一点隔离语义时，降到 READ COMMITTED 可以少掉大部分间隙锁；代价是 binlog_format 得用 ROW 或 MIXED，同时要考虑主从一致性
  • 对偶发死锁，应用层做重试比调参数更实际

小结

  • InnoDB 的锁是加在索引上的，索引选错等于锁范围失控
  • MDL 让一条 DDL 拖垮一张表，排查顺序是先停 DDL 再停长事务
  • 死锁看 SHOW ENGINE INNODB STATUS，实时等待看 INNODB_TRX 与 INNODB_LOCK_WAITS
  • 缩短事务、固定加锁顺序、走索引，比任何参数都管用

同分类其他笔记