第五期：并发控制（上）—— 隔离级别、锁与锁升级
1. 并发问题与隔离级别
三种常见并发问题：
| 问题 | 定义 | 示例 |
|——|——|——|
| 脏读 | 读到未提交事务的修改 | 事务A修改了行R未提交，事务B读到修改后的值，A回滚 |
| 不可重复读 | 同一事务内两次读取同一条记录，值不同 | 事务B第一次读行R，事务A修改并提交，事务B第二次读到新值 |
| 幻读 | 同一事务内两次查询，结果集行数不同 | 事务B第一次查满足条件的行集，事务A插入新行，事务B第二次查到多一行 |
SQL Server 支持的隔离级别（从弱到强）：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 实现机制 |
|———-|——|————|——|———–|
| READ UNCOMMITTED | ✅可能 | ✅可能 | ✅可能 | 不加共享锁，不遵守锁协议 |
| READ COMMITTED（默认）| ❌ | ✅可能 | ✅可能 | 读时加共享锁，读完立即释放 |
| REPEATABLE READ | ❌ | ❌ | ✅可能 | 读时加共享锁，保持到事务结束 |
| SERIALIZABLE | ❌ | ❌ | ❌ | 加范围锁（Range Lock），防止插入 |
| SNAPSHOT | ❌ | ❌ | ❌ | 使用行版本，不阻塞写 |
| READ COMMITTED SNAPSHOT（RCSI）| ❌ | ❌ | ✅可能 | 读行版本，写仍用锁 |
**SQL Server 默认隔离级别**：READ COMMITTED（使用共享锁防止脏读）
现象解释：
-
为什么同一个查询在会话A中连续两次，结果可能不同？因为默认 READ COMMITTED 允许不可重复读。
-
为什么 ALTER DATABASE 开启 READ_COMMITTED_SNAPSHOT 后，读操作不再被写阻塞？因为读改用 tempdb 的行版本，不再需要共享锁。
2. 锁的类型与粒度
锁粒度（从细到粗）：
| 粒度 | 资源 | 场景 |
|——|——|——|
| RID（行ID）| 堆中的单行 | 精确点查 |
| KEY | 索引行 | 通过聚集索引定位 |
| PAGE | 8KB 页 | 页内有多行被访问 |
| EXTENT | 64KB 区 | 分配/释放空间时 |
| TABLE | 整张表 | 全表扫描或锁升级 |
| DATABASE | 整个数据库 | DDL 操作或备份 |
主要锁模式：
| 锁模式 | 缩写 | 用途 | 兼容性 |
|——–|——|——|——–|
| 共享锁 | S | 读取操作，防止修改 | 与其他 S 锁兼容，与 X 锁冲突 |
| 排他锁 | X | 写入操作（INSERT/UPDATE/DELETE） | 不与任何锁兼容 |
| 更新锁 | U | 防止死锁（先读后写的场景） | 与 S 锁兼容，与 U/X 锁冲突 |
| 意向锁 | IS/IX/… | 表明低粒度有锁，用于锁层次通知 | 提高检测效率 |
| 架构锁 | Sch-M/Sch-S | DDL 操作 | Sch-M 阻塞所有访问 |
| 范围锁 | RangeS-S, RangeX-X 等 | SERIALIZABLE 级别防止幻读 | 锁定索引范围 |
现象解释：
-
为什么 UPDATE 语句会被 SELECT 阻塞？即使 SELECT 在别的事务中？因为事务 A 的 SELECT 持有共享锁（默认 READ COMMITTED 下读完会释放，但 REPEATABLE READ 或 SERIALIZABLE 会保持到事务结束）。
-
为什么大范围 UPDATE 时，其他查询完全无法访问这张表？可能触发了锁升级，从行锁/页锁升级为表锁（排他锁）。
3. 锁升级（Lock Escalation）
定义：SQL Server 自动将大量细粒度锁（行锁、页锁）转换为表锁，以节省内存资源。
-
触发阈值：
-
单个事务在单张表上锁定的行数超过 5000（确切阈值与数据页数和 SQL 版本有关）。
-
锁占用的内存超过特定阈值（约 40% 的锁内存上限）。
-
锁升级的影响：
-
大幅增加阻塞：一个表锁会阻塞所有其他事务的读写（除了未提交读）。
-
增加死锁风险：锁升级后粒度变粗，冲突面扩大。
现象解释：
-
为什么批量更新几万行时，整个表都被锁住？触发了锁升级，从行锁/页锁升级为表锁（X 锁）。
-
为什么删除 5000 行后突然变慢？可能触发锁升级，导致并发事务大量等待。
控制锁升级：
-- 在表上禁用锁升级（不推荐全局禁用）
ALTER TABLE Orders SET (LOCK_ESCALATION = DISABLE);
-- 只在分区级别升级（推荐，减少影响范围）
ALTER TABLE Orders SET (LOCK_ESCALATION = AUTO);
4. 阻塞（Blocking）与等待
阻塞链示例：
事务 A 持有表 T 的 X 锁
  ↓
事务 B 请求表 T 的 S 锁 → 被阻塞，等待资源
  ↓
事务 C 请求表 T 的 S 锁 → 也被阻塞，排在 B 之后
监控阻塞：
| DMV | 信息 |
|—–|——|
| sys.dm_exec_requests | 当前请求状态、等待类型（wait_type, wait_time）、阻塞会话 ID（blocking_session_id） |
| sys.dm_tran_locks | 所有活动锁（粒度、模式、持有/等待状态） |
| sys.dm_os_waiting_tasks | 等待队列详情 |
常见等待类型：
-
LCK_M_S：等待获取共享锁 -
LCK_M_X：等待获取排他锁 -
LCK_M_U：等待更新锁 -
LCK_M_IX：等待意向排他锁
定位阻塞源：
-- 找出阻塞别人的会话
SELECT blocking_session_id, COUNT(*)
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
GROUP BY blocking_session_id;
-- 查看持有锁的语句
DBCC INPUTBUFFER(blocking_session_id);
5. 减少阻塞的架构级方法
| 方法 | 原理 | 适用场景 |
|——|——|———-|
| 开启 RCSI（READ_COMMITTED_SNAPSHOT）| 读用行版本，无需 S 锁 | 读写混合高并发，允许稍旧的读 |
| 降低隔离级别（READ UNCOMMITTED）| 不加共享锁 | 允许脏读的非关键报表 |
| 缩短事务长度 | 减少锁持有时间 | 所有场景 |
| 优化索引 | 减少行锁数量 | 避免大范围扫描锁住多行 |
| 分区表 | 锁升级到分区级别 | 超大表，按时间分区 |
| 使用 NOLOCK 提示（慎用）| 同 READ UNCOMMITTED | 临时报表，脏读可接受 |
开启 RCSI（需数据库级别设置，且无其他连接）：
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;
6. 常见阻塞场景与排查路径
| 现象 | 最可能原因 | 排查步骤 |
|——|————|———-|
| 特定表被长时间锁定 | 未提交事务持有 X 锁或 U 锁 | DBCC OPENTRAN 查看最早活动事务 |
| 查询偶尔被阻塞几秒 | 锁升级导致表锁 | 检查 sys.dm_tran_locks 中 lock_resource_type=’OBJECT’ |
| 高峰期大量 LCK_M_XX 等待 | 隔离级别过高或索引不佳 | 考虑开启 RCSI 或优化查询减少锁行数 |
| 备份时业务阻塞 | 备份需要 Sch-S 锁，与 DDL 冲突 | 备份安排在维护窗口，避免重叠 DDL |
第五期小结
SQL Server 通过锁机制实现事务隔离，默认 READ COMMITTED 防止脏读但要承受不可重复读。锁粒度从行到表逐级变粗，锁升级可能意外造成大规模阻塞。理解隔离级别的取舍（读一致性 vs 并发性），以及如何监控 sys.dm_tran_locks 和 sys.dm_exec_requests，是排查阻塞问题的基本功。
下期预告：并发控制（下）—— 死锁检测、分析与消除，什么是“死锁优先级”？如何通过索引设计规避死锁？
