第五期：并发控制（上）—— 隔离级别、锁与锁升级### 第五期：并发控制（上）—— 隔离级别、锁与锁升级 #### 1.

### 第五期：并发控制（上）—— 隔离级别、锁与锁升级

#### 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 锁冲突 |

**现象解释**：

- 为什么 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 锁

&#x20;   ↓

事务 B 请求表 T 的 S 锁 → 被阻塞，等待资源

&#x20;   ↓

事务 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，是排查阻塞问题的基本功。

**下期预告**：并发控制（下）—— 死锁检测、分析与消除，什么是“死锁优先级”？如何通过索引设计规避死锁？