1
2
3
4
5
6
7
8
9

数据库 → 表 → 页 / Extent → 行

层级关系：
  上层加意向锁（IS/IX），下层加实际锁（S/X）
  → 加行锁前，先在表上加意向锁

为什么不直接锁行？
  - 防止"表级操作（DROP TABLE）"看不到正在进行的行锁
  - 意向锁是一种"快速检测冲突"的机制

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

兼容性矩阵（行级 / 表级）：
        S    X    IS   IX
   S    ✅   ❌   ✅   ❌
   X    ❌   ❌   ❌   ❌
   IS   ✅   ❌   ✅   ✅
   IX   ❌   ❌   ✅   ✅

关键观察：
  - S 和 X 互斥（读和写不能同时）
  - X 和任何锁互斥（写需要独占）
  - IS 和 IX 互相兼容（意向只是"标记"，不实际锁定）
  - S 和 IX 不兼容（表级 S 锁阻止 IX，因为 IX 暗示有行级 X 锁）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SQL Server 锁粒度：
  - RID（行 ID，堆表）
  - KEY（索引键，clustered/nonclustered）
  - PAGE（页）
  - EXTENT（区，8 页）
  - HoBT（堆或 B 树）
  - TABLE（表）
  - FILE（文件）
  - DATABASE（数据库）

→ SQL Server 会自动选择锁粒度（行/页/表），称为"动态锁"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SQL Server 会在一条语句获取大量行锁或键锁时尝试锁升级：
  - 常见目标是整张表
  - 表配置为 LOCK_ESCALATION = AUTO 且已分区时，也可能升级到分区（HoBT）

约 5000 个锁是一次重要的升级检查触发点，不是保证升级的固定阈值；
是否成功还受冲突锁、内存压力和后续检查影响。

问题：
  - 升级后锁范围变大，并发变差
  - "我的 SELECT 突然阻塞了所有写入" 这种诡异问题

1
2
3
4
5
6
7
8
9

-- 查看锁
SELECT * FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

-- 禁用表锁升级
ALTER TABLE t SET (LOCK_ESCALATION = DISABLE);

-- 升级到 partition 而非 table
ALTER TABLE t SET (LOCK_ESCALATION = AUTO);

1
2
3
4
5
6

Oracle、InnoDB 和 PostgreSQL 通常不会像 SQL Server 那样把大量行锁自动升级为表锁。
但三者的行锁表示和资源消耗并不相同，不能据此推导出“持有任意数量的锁都没有成本”。

代价：
  - Oracle / PG 内存占用高（每行锁一个 entry）
  - 但并发性更好

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

Record Lock（记录锁）：
  锁定索引上的一条记录
  WHERE id = 5 → 锁 id=5 的索引节点

Gap Lock（间隙锁）：
  锁定索引上的"区间"，但不含端点
  WHERE id > 5 AND id < 10 → 锁 (5, 10) 间隙
  防止其他事务在这个区间 INSERT

Next-Key Lock（Next-Key = Record + Gap）：
  锁定"一条记录 + 该记录之前的间隙"
  WHERE id BETWEEN 5 AND 10 → 锁 (3, 5], (5, 8], (8, 10] 等多个 Next-Key

1
2
3
4
5
6
7

InnoDB RR 下的加锁规则（基于《MySQL 实战 45 讲》）：

1. 原则 1：基本单位是 Next-Key Lock
2. 原则 2：查找过程中访问到的对象才加锁
3. 优化 1：唯一索引等值查询，命中记录 → 退化为 Record Lock
4. 优化 2：等值查询，向右遍历到最后一个不满足条件的值 → Next-Key 退化为 Gap Lock
5. 唯一索引范围查询：会访问到不满足条件的第一个值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

示例表：id (1, 5, 10, 15, 20)，主键

场景 1：等值命中唯一索引
  SELECT * FROM t WHERE id = 10 FOR UPDATE;
  → 锁 id=10 的 Record Lock（不锁间隙）

场景 2：等值未命中唯一索引
  SELECT * FROM t WHERE id = 7 FOR UPDATE;
  → 锁 (5, 10) 的 Gap Lock（防止插入 7）

场景 3：范围查询
  SELECT * FROM t WHERE id >= 10 AND id < 15 FOR UPDATE;
  → 锁 id=10 的 Record Lock + (10, 15] 的 Next-Key Lock（含 15 这条记录）

场景 4：SELECT * FROM t WHERE id > 10 FOR UPDATE;
  → 锁 (10, 15], (15, 20], (20, +∞]

1
2
3
4
5
6

唯一索引等值命中 → Record Lock（窄）
非唯一索引等值命中 → Next-Key（多个 Gap）

为什么？
  - 唯一索引：相同值只能有一个，不需要防止"再插一个相同值"
  - 非唯一索引：可能有多条相同值记录，需要锁定间隙防止幻读

1
2
3
4
5

RC 级别下，InnoDB 的普通索引扫描通常只加 Record Lock：
  - 一般不使用 Gap Lock / Next-Key Lock
  - 外键约束检查、重复键检查等场景仍可能使用间隙锁
  - 不防幻读
  - 并发性更好

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

LOCK TABLE t IN {MODE} MODE;

模式（强度递增）：
  ACCESS SHARE        ← SELECT 自动加
  ROW SHARE           ← SELECT FOR UPDATE/SHARE 自动加
  ROW EXCLUSIVE       ← INSERT/UPDATE/DELETE 自动加
  SHARE UPDATE EXCL   ← VACUUM 自动加
  SHARE               ← CREATE INDEX 自动加
  SHARE ROW EXCL      ← 手动
  EXCLUSIVE           ← 手动
  ACCESS EXCLUSIVE    ← ALTER/DROP/TRUNCATE 自动加

1
2
3
4
5
6
7
8
9

PG 行级锁类型：
  FOR UPDATE       ← 排他，允许其他事务读，阻止写
  FOR NO KEY UPDATE ← 排他（弱），允许其他 FOR KEY SHARE
  FOR SHARE        ← 共享
  FOR KEY SHARE    ← 共享（弱）

PG 行锁不存独立结构，记录在行的 xmax：
  - 优点：节省内存（不限制锁数量）
  - 缺点：检查冲突需要读行（多版本查询）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 会话级 Advisory Lock
SELECT pg_advisory_lock(12345);     -- 申请锁
SELECT pg_advisory_unlock(12345);   -- 释放

-- 事务级 Advisory Lock（事务结束自动释放）
SELECT pg_advisory_xact_lock(12345);

-- 非阻塞尝试
SELECT pg_try_advisory_lock(12345);  -- 返回 true/false

-- 双 int 参数版本（支持 8 字节 key）
SELECT pg_advisory_lock(id1, id2);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

应用场景：
  - 应用层分布式锁（避免引入 Redis / ZooKeeper）
  - 跨服务协调（多个服务连同一 PG）
  - 任务调度（多个 worker 抢任务）

优势：
  - 与业务事务原子性一致（事务级）
  - 不需要额外基础设施
  - 自动释放（连接断开 / 事务结束）

劣势：
  - 只能在 PG 内有效
  - 不适合跨数据库场景

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Oracle 行锁：
  - 通过 ITL（块的事务槽）实现
  - 锁信息存在数据块头部，不存独立结构
  - 不会"锁升级"

锁模式：
  - RX（Row Exclusive）：INSERT/UPDATE/DELETE
  - RS（Row Shared）：SELECT FOR SHARE
  - SRX（Shared Row Exclusive）：手工声明
  - S（Shared）：手工声明
  - X（Exclusive）：手工声明

1
2
3
4

DDL 操作时锁定 schema 对象：
  - Exclusive DDL Lock：CREATE/ALTER/DROP 时，完全独占
  - Share DDL Lock：CREATE PROCEDURE 等，允许其他 DDL 但不允许 DROP
  - Breakable Parse Lock：缓存依赖关系

1
2
3
4

Oracle 设计哲学：
  - 数据块中的 ITL 记录访问该块的事务信息，行头再指向相应事务槽
  - 一个事务在同一数据块修改多行时可以共享一个 ITL 槽，并不是“一行一个 ITL entry”
  - 通常不会把大量行锁自动升级为表锁，但仍有 ITL、Undo 和事务管理成本

1
2
3
4
5
6

-- 标准
SELECT * FROM t WHERE id = 1 FOR UPDATE;
-- 加 X 锁，其他事务读可（MVCC），写不行

SELECT * FROM t WHERE id = 1 FOR SHARE;
-- 加 S 锁，其他事务可以读，但不能写

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- NOWAIT：拿不到锁立即报错
SELECT * FROM t WHERE id = 1 FOR UPDATE NOWAIT;
-- 错误：ORA-00054 / Lock wait timeout exceeded

-- SKIP LOCKED：跳过被锁的行（不阻塞）
SELECT * FROM t WHERE id IN (1, 2, 3) FOR UPDATE SKIP LOCKED;
-- 假设 id=2 被锁，返回 id=1 和 id=3

-- 用途：任务队列
SELECT * FROM task_queue WHERE status = 'pending'
FOR UPDATE SKIP LOCKED LIMIT 10;
-- 多个 worker 同时取任务，互不阻塞

1
2
3
4
5

支持情况：
  - Oracle：NOWAIT ✅ / SKIP LOCKED ✅
  - SQL Server：NOWAIT ✅（WITH NOWAIT）/ SKIP LOCKED ✅（READPAST）
  - MySQL：NOWAIT ✅（8.0+）/ SKIP LOCKED ✅（8.0+）
  - PostgreSQL：NOWAIT ✅ / SKIP LOCKED ✅

1
2
3

-- 联合查询时只锁特定表
SELECT * FROM orders o JOIN users u ON o.uid = u.id
FOR UPDATE OF o;  -- 只锁 orders 行，不锁 users

1
2
3
4
5
6
7
8
9

事务A                          事务B
BEGIN;                         BEGIN;
UPDATE t SET v=1 WHERE id=1;   UPDATE t SET v=1 WHERE id=2;
                               -- 持有 id=2 锁
UPDATE t SET v=1 WHERE id=2;
-- 等待 id=2 锁                  UPDATE t SET v=1 WHERE id=1;
                                -- 等待 id=1 锁

→ 死锁！双方互相等待

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Wait-For Graph（等待图）：
  - 数据库维护一个"事务等待关系图"
  - 检测图中是否有环
  - 有环 → 死锁
  - 选择"代价最小"的事务回滚（victim）

各家实现：
  - Oracle：自动检测并报告 ORA-00060，通常回滚触发死锁的语句；事务能否继续取决于应用如何处理
  - SQL Server：死锁监视器通常从约 5 秒间隔开始，频繁发生时可缩短到约 100 毫秒；受 `DEADLOCK_PRIORITY` 和回滚成本等因素影响选择 victim
  - MySQL InnoDB：自动检测，立即回滚（默认 deadlock_detect=ON）
  - PostgreSQL：自动检测，默认 1 秒间隔

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

1. 固定加锁顺序
   - 所有事务按相同顺序加锁（如按主键升序）
   - 避免环

2. 缩短事务
   - 持锁时间短，降低死锁概率
   - 不要在事务中调用外部 API

3. 用 NOWAIT + 重试
   - 拿不到锁立即失败
   - 应用层捕获错误后重试

4. 用 SKIP LOCKED
   - 任务队列场景，跳过被锁任务

5. 控制并发度
   - 同一资源并发不要太高

1
2
3
4
5
6
7
8

假设冲突必然发生，提前加锁
  SELECT * FROM t WHERE id = 1 FOR UPDATE;
  -- 加锁后才修改
  UPDATE t SET v = ... WHERE id = 1;
  COMMIT;

适合：写冲突频繁
代价：持锁期间其他事务阻塞

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

假设冲突很少，提交时检测

实现：版本号 / 时间戳字段
  CREATE TABLE t (id INT, v INT, version INT);

  -- 读
  SELECT id, v, version FROM t WHERE id = 1;
  -- 假设读到 version = 10

  -- 写（带版本检查）
  UPDATE t SET v = v + 1, version = version + 1
  WHERE id = 1 AND version = 10;
  -- 影响行数 = 0 → 说明版本变了 → 冲突
  -- 应用层重试

适合：读多写少、冲突少
代价：失败时需要重试

1
2
3
4
5
6
7
8
9

冲突频繁、重试代价高且临界区很短时，可以优先评估悲观锁；
冲突少、读多写少或不希望长时间持锁时，可以优先评估乐观锁。
不存在适用于所有系统的固定“20%”分界线，应通过压测观察吞吐、尾延迟和失败重试成本。

业务场景：
  - 库存扣减：悲观（必然冲突）
  - 用户更新资料：乐观（极少冲突）
  - 抢购：悲观 + 队列
  - 投票计数：乐观 + 重试

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 看当前事务
SELECT * FROM information_schema.innodb_trx;

-- 看锁（8.0+）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 死锁日志
SHOW ENGINE INNODB STATUS\G

-- 锁等待超时
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 看锁
SELECT l.locktype, l.relation::regclass, l.pid, l.mode, l.granted, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

-- 看等待链
SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

-- 杀会话
SELECT pg_terminate_backend(pid);

1
2
3
4
5
6
7
8
9

-- 看锁
SELECT s.sid, s.serial#, l.type, l.mode_held, o.object_name
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.type = 'TM';

-- 杀会话
ALTER SYSTEM KILL SESSION 'sid,serial#';

1
2
3
4
5

-- 看阻塞
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

-- 看锁
SELECT * FROM sys.dm_tran_locks WHERE request_status = 'WAIT';

1
2
3
4

记住三句话：
  1. MVCC 解决读写并发，锁解决写写并发
  2. SQL Server 有显式的锁升级机制；其他数据库通常不做同类升级，但锁的表示与成本各不相同
  3. InnoDB 的 Next-Key Lock 是"为了 RR 防幻读"的代价