锁与死锁实战

行锁、间隙锁、Next-Key、Insert Intention——深入到能看懂 SHOW ENGINE INNODB STATUS

锁的层级

+---------------------------+
|  Table Lock (MDL)         |   元数据锁，DDL 用
+---------------------------+
|  AUTO-INC Lock            |   自增列分配
+---------------------------+
|  Record Lock              |   行锁
|  Gap Lock                 |   间隙锁
|  Next-Key Lock            |   行 + 前面的间隙
|  Insert Intention Lock    |   插入意向
+---------------------------+

Record Lock（行锁）

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 排他锁这一行
-- 其他事务对 id=1 的 UPDATE/DELETE/FOR UPDATE 都等

FOR UPDATE 是排他锁；LOCK IN SHARE MODE 是共享锁。

Gap Lock（间隙锁）

InnoDB 在 REPEATABLE READ 下默认锁"行 + 中间间隙"，避免幻读。

-- 表里 id = 10, 20, 30
START TRANSACTION;
SELECT * FROM tbl WHERE id BETWEEN 11 AND 19 FOR UPDATE;
-- 锁住 (10, 20) 这个开区间
-- 其他事务不能 INSERT id=15

间隙锁的几个事实：

只在 REPEATABLE READ 和更高隔离级别才有
READ COMMITTED 下没有间隙锁——但有"幻读"
间隙锁互不冲突（两个事务可以同时持有同一间隙的间隙锁）
间隙锁与"Insert Intention"冲突

Next-Key Lock

= Record Lock + 前面的 Gap Lock。InnoDB 范围扫描时默认加的锁。

-- 表里 id = 10, 20, 30
SELECT * FROM tbl WHERE id > 15 FOR UPDATE;
-- 锁住 (15, 20], (20, 30], (30, +∞)

Insert Intention Lock

INSERT 时表示"我要在这个间隙插入"。与间隙锁冲突，但与其他 Insert Intention 不冲突。

这是死锁的常见来源：

-- T1
INSERT INTO tbl (id) VALUES (15);   -- 持有 (10,20) 的 insert intention
-- T2
INSERT INTO tbl (id) VALUES (17);   -- 也持有 (10,20) 的 insert intention，不冲突

-- 但如果一个事务先 SELECT FOR UPDATE 加间隙锁，再 INSERT，就可能死锁

实战：死锁排查

-- 1. 找到死锁信息
SHOW ENGINE INNODB STATUS\G
-- 输出里搜 "LATEST DETECTED DEADLOCK"

例子输出：

LATEST DETECTED DEADLOCK
2026-05-17 18:00:00
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136
MySQL thread id 100, query id 50000 update
INSERT INTO orders (user_id) VALUES (42)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5 page no 6 n bits 72 index PRIMARY of table `app`.`orders`
  trx id 12345 lock_mode X locks gap before rec insert intention waiting

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 0 sec
...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5 page no 6 n bits 72 index PRIMARY of table `app`.`orders`
  trx id 12346 lock_mode X locks gap before rec

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
...
*** WE ROLL BACK TRANSACTION (1)

读法：

两个事务（1）（2）
都在等对方持有的锁 → 死锁
InnoDB 自动 rollback 其中一个（这里是 transaction 1）

死锁监控

SHOW STATUS LIKE 'Innodb_deadlocks';   -- 累计死锁次数

或开 innodb_print_all_deadlocks = ON 把死锁日志写到错误日志（生产推荐开）。

锁等待排查

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 完整图
SELECT
  r.trx_id AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id;

找到阻塞事务后：

KILL 12345;   -- 阻塞事务的 mysql_thread_id

减少锁竞争的范式

1. 短事务

-- ❌ 长事务
START TRANSACTION;
SELECT ... FOR UPDATE;     -- 锁 1 万行
-- 应用层做复杂运算（500ms+）
UPDATE ...;
COMMIT;

-- ✅ 短事务
-- 先查（不锁）
data = SELECT ...;
result = compute(data);
START TRANSACTION;
UPDATE ... WHERE id = ? AND version = ?;  -- 乐观锁
COMMIT;

2. 用乐观锁替代悲观锁

UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 42 AND version = 5;
-- 影响行数 = 0 → 别人改过了，重试

3. 固定加锁顺序

死锁的根源是循环等待。所有事务按相同顺序访问表 / 行。

-- ❌ 不同事务用不同顺序
T1: UPDATE A; UPDATE B;
T2: UPDATE B; UPDATE A;   -- 可能死锁

-- ✅ 始终按 id 升序
T1, T2 都按 (A, B) 顺序 → 不会死锁

4. 拆热点行

库存扣减、计数器是热点：

-- 拆成 N 行
UPDATE counters SET count = count + 1
WHERE name = 'pageviews' AND shard = FLOOR(RAND() * 32);

-- 读：SUM
SELECT SUM(count) FROM counters WHERE name = 'pageviews';

5. 用 `READ COMMITTED` 减少间隙锁

SET SESSION transaction_isolation = 'READ-COMMITTED';

代价：会出现幻读，要应用层接受。

DDL 锁

ALTER TABLE huge_table ADD COLUMN x INT;
-- 在老版本 InnoDB 是阻塞的；现在 ONLINE DDL 可以不阻塞读写

但元数据锁（MDL）始终存在：

-- 长事务持有表的 MDL，ALTER TABLE 卡住，所有新 query 也卡

排查：

SELECT * FROM information_schema.METADATA_LOCK_INFO;

找到长事务 KILL 掉。

pt-online-schema-change

第三方工具，可在线改大表 schema 不阻塞：

pt-online-schema-change \
  --alter "ADD COLUMN x INT" \
  D=app,t=orders \
  --execute

原理：建影子表 → 触发器复制变更 → swap。

锁与不同存储引擎

InnoDB：行锁，支持事务（默认）
MyRocks：行锁，LSM tree
Aria：表锁
MyISAM：表锁（禁用）
ColumnStore：bulk insert 友好，不适合 OLTP