数据库死锁检测与处理实战

事务 A：                         事务 B：
  1. 锁定行 1 ✅                 1. 锁定行 2 ✅
  2. 等待行 2 ... ⏳             2. 等待行 1 ... ⏳

A 等 B 持有的行 2，B 等 A 持有的行 1 → 死锁！

-- 在 REPEATABLE READ 下，以下查询会给 (10,20) 区间加间隙锁
SELECT * FROM orders WHERE amount BETWEEN 10 AND 20 FOR UPDATE;

-- 另一事务想插入 amount=15 的新行 → 阻塞在间隙锁上
INSERT INTO orders (amount) VALUES (15);

时刻 | 事务 T1                      | 事务 T2
-----+-----------------------------+----------------------------
 1   | UPDATE stock SET qty=qty-1  |  UPDATE order SET status=1
     |   WHERE id=100;   (锁行100)  |    WHERE id=200;  (锁行200)
 2   | UPDATE order SET status=1   |  UPDATE stock SET qty=qty-1
     |   WHERE id=200;   (等锁)     |    WHERE id=100;  (等锁)
 3   |        💥 DEADLOCK          |        💥 DEADLOCK

时刻 | 事务 T1                                | 事务 T2
-----+---------------------------------------+---------------------------
 1   | 锁住 emp 100~200 的间隙（FOR UPDATE） | 想插入 id=150，等间隙锁
 2   | 想插入 id=150，等 T2 的插入意图锁     | 锁住 emp 100~200 的间隙
 3   |          双方僵持，InnoDB 判死锁      |   （回滚代价小的一方）

检测频率：每次加锁等待时触发
图结构：  节点 = 事务，边 = 等待关系
判环：    DFS/BFS 找环路，找到即判死锁
回滚：    回滚 undo 量最小（代价最小）的事务

# my.cnf
# 是否启用死锁检测。禁用后靠 innodb_lock_wait_timeout 兜底
innodb_deadlock_detect = ON

# 锁等待超时（秒），超时后自动回滚当前 SQL
innodb_lock_wait_timeout = 50

-- 当前正在运行的事务
SELECT * FROM information_schema.innodb_trx\G

-- 当前被加锁的行/锁等待关系
SELECT * FROM information_schema.innodb_lock_waits\G

-- 事务持有的锁
SELECT * FROM information_schema.innodb_locks\G

-- 找出"等待别人锁超过 N 秒"的事务，往往是死锁前兆
SELECT
  trx_id,
  trx_state,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age,
  trx_rows_locked,
  trx_rows_modified
FROM information_schema.innodb_trx
WHERE trx_state = 'LOCK WAIT'
ORDER BY trx_age DESC;

# 在 Linux 上用脚本每 30s 采样一次锁等待，超过阈值触发告警
while true; do
  mysql -e "SELECT COUNT(*) FROM information_schema.innodb_lock_waits" \
    | awk '$1>10 {print "LOCK_WAIT_ALERT", $1, strftime("%F %T")}'
  sleep 30
done

-- 查看当前正在等待锁的会话及其等待的锁对象
SELECT OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, SOURCE, INDEX_NAME
FROM performance_schema.data_locks;

-- 查看锁等待关系（哪个线程等哪个线程）
SELECT * FROM performance_schema.data_lock_waits\G

-- 直接列出阻塞者与被阻塞者（含 SQL 文本）
SELECT * FROM sys.innodb_lock_waits\G

# 把死锁信息输出到日志文件（生产可临时开启观察）
SET GLOBAL innodb_print_all_deadlocks = ON;

# 查看最近一次死锁详情
SHOW ENGINE INNODB STATUS\G

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-09-30 10:00:01 0x7f3a0b0c1700
*** (1) TRANSACTION:            ← 事务 1
TRANSACTION 348179, ACTIVE 3 sec, 2 lock struct(s)
MySQL thread id 123, OS thread handle ...
UPDATE stock SET qty=qty-1 WHERE id=100
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5 page no 12 n bits 80 index PRIMARY of table `db`.`stock`
*** (2) TRANSACTION:            ← 事务 2
TRANSACTION 348180, ACTIVE 2 sec, 2 lock struct(s)
UPDATE order SET status=1 WHERE id=200
*** (2) HOLDS THE LOCK(S):      ← 事务 2 持有哪些锁
RECORD LOCKS ... of table `db`.`order`
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... of table `db`.`stock`
*** WE ROLL BACK TRANSACTION (2)   ← 回滚的是事务 2

-- 统一约定：先锁订单表，再锁库存表（所有事务都遵守）
BEGIN;
UPDATE orders SET status=1 WHERE id=200;   -- 第一步：订单
UPDATE stock  SET qty=qty-1 WHERE id=100;  -- 第二步：库存
COMMIT;

□ 所有 WHERE/JOIN 列都走索引：避免锁升级为全表/大范围扫描
□ 用覆盖索引减少回表：回表行数越少，锁行越少
□ 避免大范围 UPDATE/DELETE：分批 + LIMIT
□ 同一隔离级别下，能降级就降级（READ COMMITTED 无间隙锁）

import time

def exec_with_deadlock_retry(sql, retries=3):
    for i in range(retries):
        try:
            return execute(sql)            # 业务执行
        except DeadlockError:              # 1062/1213: 死锁或唯一键冲突
            time.sleep(0.05 * (i + 1))     # 递增退避
    raise DeadlockError("retry exhausted")