1
-- Record Lock 示例
2
-- 表：users(id INT PRIMARY KEY, name VARCHAR(50), age INT, INDEX idx_age(age))
3
BEGIN;
4
SELECT * FROM users WHERE id = 5 FOR UPDATE;
5
-- 锁定：id=5 的 Record Lock（在聚簇索引上）
6

7
SELECT * FROM users WHERE age = 25 FOR UPDATE;
8
-- 锁定：age=25 的 Record Lock（在 idx_age 索引上）
9
--       + 对应主键的 Record Lock（回表后在聚簇索引上）

1
-- Gap Lock 示例
2
-- 假设 users 表中 age 列有值：10, 20, 30, 40
3
BEGIN;
4
SELECT * FROM users WHERE age = 20 FOR UPDATE;
5
-- 在 RR 隔离级别下，对 age=20 命中的记录加 Next-Key Lock (10, 20]
6
-- 并向右遍历到第一条不满足条件的 age=30，退化为纯 Gap Lock (20, 30)
7
-- 即 idx_age 上：(10, 20] 的 Next-Key Lock + (20, 30) 的 Gap Lock（不含 age=30 本身）
8

9
-- 另一个事务尝试插入
10
INSERT INTO users (name, age) VALUES ('New', 25);
11
-- 阻塞！因为 25 落在 (20, 30) 间隙内

1
-- Next-Key Lock 的范围表示
2
-- 假设索引值为：5, 10, 15, 20
3
-- Next-Key Lock 的可能范围：
4
--   (-∞, 5]   → 间隙(-∞,5) + 记录5
5
--   (5, 10]   → 间隙(5,10) + 记录10
6
--   (10, 15]  → 间隙(10,15) + 记录15
7
--   (15, 20]  → 间隙(15,20) + 记录20
8
--   (20, +∞)  → 间隙(20,+∞)

1
-- 加锁规则实战分析
2
-- 表：t(id INT PRIMARY KEY, c INT, INDEX idx_c(c))
3
-- 数据：id=5,c=5 | id=10,c=10 | id=15,c=15 | id=20,c=20
4

5
-- 场景1：等值查询，唯一索引命中
6
SELECT * FROM t WHERE id = 10 FOR UPDATE;
7
-- 加锁：id=10 的 Record Lock（退化）
8

9
-- 场景2：等值查询，唯一索引未命中
10
SELECT * FROM t WHERE id = 12 FOR UPDATE;
11
-- 加锁：(10, 15) 的 Gap Lock（退化）
12

13
-- 场景3：等值查询，普通索引命中
14
SELECT * FROM t WHERE c = 10 FOR UPDATE;
15
-- 加锁：idx_c 上 (5, 10] 的 Next-Key Lock
16
--       idx_c 上 (10, 15) 的 Gap Lock（向右到不满足条件的第一条）
17
--       聚簇索引上 id=10 的 Record Lock（回表）
18

19
-- 场景4：范围查询
20
SELECT * FROM t WHERE c >= 10 AND c < 15 FOR UPDATE;
21
-- 加锁：idx_c 上 (5, 10] 的 Next-Key Lock
22
--       idx_c 上 (10, 15] 的 Next-Key Lock
23
--       聚簇索引上 id=10, id=15 的 Record Lock

1
-- 死锁案例：交叉更新
2
-- 事务 A                         -- 事务 B
3
BEGIN;                            BEGIN;
4
UPDATE t SET c=100
5
WHERE id=5;
6
-- 持有：id=5 的 Record Lock
7

8
                                  UPDATE t SET c=200
9
                                  WHERE id=10;
10
                                  -- 持有：id=10 的 Record Lock
11

12
UPDATE t SET c=100
13
WHERE id=10;
14
-- 等待：id=10 的 Record Lock（被 B 持有）
15

16
                                  UPDATE t SET c=200
17
                                  WHERE id=5;
18
                                  -- 等待：id=5 的 Record Lock（被 A 持有）
19
                                  -- 死锁！InnoDB 检测到并回滚 B

1
-- 被回滚的事务收到：
2
-- ERROR 1213 (40001): Deadlock found when trying to get lock;
3
-- try restarting transaction
4

5
-- 查看最近的死锁信息
6
SHOW ENGINE INNODB STATUS\G
7
-- 关注 "LATEST DETECTED DEADLOCK" 段，里面有死锁时刻两个事务的锁信息

1
-- 第一步：查看当前所有事务
2
SELECT trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started
3
FROM information_schema.INNODB_TRX;
4

5
-- 第二步：查看当前锁信息（MySQL 8.0 用 performance_schema）
6
SELECT * FROM performance_schema.data_locks;
7
SELECT * FROM performance_schema.data_lock_waits;
8

9
-- MySQL 5.7 用 information_schema（INNODB_LOCKS 在 8.0 已删，5.7 才可用）：
10
-- SELECT * FROM information_schema.INNODB_LOCKS;
11
-- SELECT * FROM information_schema.INNODB_LOCK_WAITS;
12

13
-- 第三步：找到阻塞源头
14
-- trx_state = LOCK WAIT 的事务是被阻塞的
15
-- MySQL 8.0 的锁信息在 performance_schema，与 INNODB_TRX 不在同一模型，
16
-- 直接查 sys.innodb_lock_waits 视图（封装了 data_lock_waits + PROCESSLIST）更直观：
17
SELECT * FROM sys.innodb_lock_waits\G
18

19
-- MySQL 5.7 的写法（INNODB_LOCK_WAITS 在 8.0 已删，5.7 才可用）：
20
-- SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query,
21
--        b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query
22
-- FROM information_schema.INNODB_LOCK_WAITS w
23
-- JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
24
-- JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;
25

26
-- 第四步：查看锁详情
27
SHOW ENGINE INNODB STATUS\G
28
-- 关注 "TRANSACTIONS" 段和 "LATEST DETECTED DEADLOCK" 段

1
*** (1) TRANSACTION:
2
TRANSACTION 12345, ACTIVE 2 sec starting index read
3
mysql tables in use 1, locked 1
4
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
5
MySQL thread id 10, OS thread handle 140123456789, query id 100 localhost root updating
6
UPDATE t SET c=100 WHERE id=10
7

8
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
9
RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table `test`.`t`
10
trx id 12345 lock_mode X locks rec but not gap waiting
11
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; ...
12

13
*** (2) TRANSACTION:
14
TRANSACTION 12346, ACTIVE 1 sec starting index read
15
UPDATE t SET c=200 WHERE id=5
16

17
*** (2) HOLDS THE LOCK(S):
18
RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table `test`.`t`
19
trx id 12346 lock_mode X locks rec but not gap
20

21
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
22
...
23
Record lock, heap no 2 ...
24

25
*** WE ROLL BACK TRANSACTION (2)