SQL Server 性能优化实战（第五期）：事务与隔离级别——平衡数据一致性与并发性能

绩隐金
0 阅读10分钟

SQL Server 性能优化实战（第五期）：事务与隔离级别——平衡数据一致性与并发性能

前四期我们聚焦在索引、执行计划和等待统计上，这些都是从“查询”角度优化性能。但还有一个更底层的因素深刻影响着数据库的并发能力——事务隔离级别。你遇到过查询被阻塞、死锁报错、或者为了“不读脏数据”而牺牲了大部分并发性能吗？这一期，我们深入事务与隔离级别，帮你找到“数据正确性”和“系统吞吐量”之间的最佳平衡点。

一、事务的 ACID 特性

事务是一组要么全部成功、要么全部失败的操作单元。ACID 是事务的四个核心特性：

特性含义SQL Server 实现方式
Atomicity 原子性事务中的所有操作要么全部提交，要么全部回滚事务日志（Transaction Log）
Consistency 一致性事务执行前后，数据保持完整性约束约束、触发器、外键等
Isolation 隔离性并发事务之间互不干扰锁（Locking）或行版本控制（Row Versioning）
Durability 持久性事务一旦提交，数据永久保存事务日志写入磁盘 + 检查点

性能优化中，隔离性是影响最大的因素——它直接决定了数据库的并发处理能力。

二、并发问题：脏读、不可重复读、幻读

在没有足够隔离的情况下，并发事务会导致三种典型问题：

2.1 脏读（Dirty Read）

定义：读到了另一个未提交事务修改的数据。

场景：

事务A（未提交）：UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1
事务B：SELECT Balance FROM Accounts WHERE ID = 1  -- 读到已扣除但未提交的余额
事务A：ROLLBACK  -- 事务A回滚，事务B读到的数据从未真实存在过

危害：读到“幽灵数据”，可能导致业务逻辑错误。

2.2 不可重复读（Non-repeatable Read）

定义：同一个事务内，两次读取同一行数据，结果不一致（因为其他事务修改并提交了）。

场景：

事务A：SELECT Balance FROM Accounts WHERE ID = 1  -- 结果 1000
事务B：UPDATE Accounts SET Balance = 2000 WHERE ID = 1; COMMIT
事务A：SELECT Balance FROM Accounts WHERE ID = 1  -- 结果 2000（前后不一致）

危害：同一事务内数据不一致，可能导致业务判断错误。

2.3 幻读（Phantom Read）

定义：同一个事务内，两次查询返回的行数不一致（因为其他事务插入或删除了数据）。

场景：

事务A：SELECT COUNT(*) FROM Orders WHERE Status = 'Pending'  -- 结果 10
事务B：INSERT INTO Orders VALUES ('Pending'); COMMIT
事务A：SELECT COUNT(*) FROM Orders WHERE Status = 'Pending'  -- 结果 11（多了一行）

危害：基于行数的操作（如分页、统计）出现不一致。

三、SQL Server 的五种隔离级别

SQL Server 支持五种隔离级别，通过不同的机制平衡并发与一致性。

3.1 隔离级别概览

隔离级别脏读不可重复读幻读并发性能实现机制
READ UNCOMMITTED✅✅✅最高不加共享锁
READ COMMITTED（默认）❌✅✅高读完后立即释放共享锁
REPEATABLE READ❌❌✅中保持共享锁直到事务结束
SERIALIZABLE❌❌❌最低范围锁（Range Lock）
SNAPSHOT❌❌❌高行版本控制（无读锁）
READ COMMITTED SNAPSHOT❌✅✅高行版本控制（无读锁）

💡 SQL Server 实际上有 6 种隔离级别，因为 READ_COMMITTED_SNAPSHOT 是 READ COMMITTED 的一种特殊实现（数据库级别设置）。

3.2 详细解读

📖 READ UNCOMMITTED / NOLOCK

行为：读操作不加共享锁，也不受排他锁影响。

优点：永不阻塞，读性能最高。

缺点：可能读到脏数据、重复读不一致、幻读。

使用方式：

-- 会话级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查询级别（表提示）
SELECT * FROM Orders WITH (NOLOCK) WHERE CustomerID = 100;

⚠️ 警告：NOLOCK 不仅会脏读，还可能读到重复行或丢失行（索引分裂时）。金融、账务类系统严禁使用。

✅ READ COMMITTED（SQL Server 默认）

行为：读操作会短暂加共享锁，读取完成后立即释放（不保持到事务结束）。

优点：避免脏读，性能较好。

缺点：仍会出现不可重复读和幻读。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
    SELECT * FROM Orders WHERE OrderID = 1;  -- 加共享锁，读完释放
    -- 此时其他事务可以修改这行数据
    SELECT * FROM Orders WHERE OrderID = 1;  -- 可能读到不同的值
COMMIT
🔒 REPEATABLE READ

行为：读操作加共享锁，并保持到事务结束。

优点：避免不可重复读。

缺点：锁持有时间长，阻塞写操作，并发下降。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
    SELECT * FROM Orders WHERE OrderID = 1;  -- 加共享锁，直到事务结束
    -- 其他事务无法修改 OrderID=1 的行
    SELECT * FROM Orders WHERE OrderID = 1;  -- 保证读取一致
COMMIT  -- 释放所有锁
🔗 SERIALIZABLE

行为：读操作加范围锁（Range Lock），防止其他事务插入/删除/修改范围内的数据。

优点：完全隔离，无任何并发问题。

缺点：并发性能最差，锁争用严重。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
    SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-01-31';
    -- 其他事务无法在这个日期范围内插入、删除、修改任何行
COMMIT
📸 SNAPSHOT 和 READ COMMITTED SNAPSHOT

行为：使用 tempdb 中的行版本代替锁。读操作读取事务开始时的数据快照，不获取共享锁。

优点：

  • 读操作不阻塞写操作
  • 写操作也不阻塞读操作
  • 无脏读、不可重复读、幻读（SNAPSHOT）

缺点：

  • 额外消耗 tempdb 空间
  • 更新冲突检测（SNAPSHOT 下，冲突的事务会收到错误 3960）

启用方式：

-- 数据库级别启用（需要重启现有连接）
ALTER DATABASE YourDB SET ALLOW_SNAPSHOT_ISOLATION ON;      -- 启用 SNAPSHOT
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;       -- 启用 RCSI

-- 使用 SNAPSHOT（需要显式设置）
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    SELECT * FROM Orders WHERE CustomerID = 100;
    -- 读取的是事务开始时的快照，不受其他事务更新影响
COMMIT

RCSI vs SNAPSHOT：

特性READ COMMITTED SNAPSHOT (RCSI)SNAPSHOT
默认行为替代 READ COMMITTED需显式设置
快照时机每个语句开始时刻事务开始时刻
更新冲突检测无有（更新冲突报错）
适用场景高并发读写混合报表、数据一致性要求高

四、实战：隔离级别对性能的影响

场景：账户扣款 + 余额查询并发

-- 创建测试表
CREATE TABLE Accounts (ID INT PRIMARY KEY, Balance INT);
INSERT INTO Accounts VALUES (1, 10000);

-- 模拟并发：一个事务扣款（写），另一个事务查询余额（读）

测试 1：READ COMMITTED（默认）

-- 事务A：扣款（写）
BEGIN TRAN
    UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
    WAITFOR DELAY '00:00:05';  -- 模拟耗时操作
COMMIT

-- 事务B：查询（读）在事务A执行期间运行
SELECT Balance FROM Accounts WHERE ID = 1;  -- 会被阻塞吗？

结果：事务B的 SELECT 会被阻塞，直到事务A提交或回滚。因为事务A持有排他锁（X Lock），事务B需要共享锁（S Lock）。

测试 2：READ COMMITTED SNAPSHOT（启用后）

-- 启用 RCSI
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

-- 事务A：扣款（写）
BEGIN TRAN
    UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
    WAITFOR DELAY '00:00:05';
COMMIT

-- 事务B：查询（读）在事务A执行期间运行
SELECT Balance FROM Accounts WHERE ID = 1;  -- 不会被阻塞！

结果：事务B立即返回修改前的余额（10000），不被阻塞。事务A提交后，后续查询读到新值。

性能提升：读写不互斥，并发吞吐量可提升数倍。

五、死锁：隔离级别不当的严重后果

5.1 典型死锁场景

-- 事务A
BEGIN TRAN
    UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;
COMMIT

-- 事务B（几乎同时执行）
BEGIN TRAN
    UPDATE Accounts SET Balance = Balance - 50 WHERE ID = 2;
    UPDATE Accounts SET Balance = Balance + 50 WHERE ID = 1;
COMMIT

死锁过程：

  1. 事务A锁住 ID=1
  2. 事务B锁住 ID=2
  3. 事务A请求锁 ID=2 → 等待事务B
  4. 事务B请求锁 ID=1 → 等待事务A
  5. SQL Server 检测到死锁，选择开销较小的一个作为牺牲品（Kill），回滚其事务

5.2 如何避免死锁

  1. 按相同顺序访问资源：总是先更新 ID=1，再更新 ID=2
  2. 缩短事务长度：减少锁持有时间
  3. 使用 RCSI：读操作不加锁，减少锁争用
  4. 添加索引：让 UPDATE 锁定更少的行（精确查找 vs 扫描）
  5. 使用行版本控制：SNAPSHOT 隔离级别可消除大多数死锁

5.3 捕获死锁

-- 开启死锁跟踪
DBCC TRACEON(1222, -1);  -- 死锁信息写入错误日志
DBCC TRACEON(1204, -1);  -- 更详细的信息

-- 查看死锁历史（系统扩展事件）
SELECT * FROM sys.system_health 
WHERE name LIKE 'xml_deadlock_report'
ORDER BY target_data;

六、如何选择隔离级别

决策矩阵

业务场景推荐隔离级别原因
金融交易、账务系统READ COMMITTED + 乐观锁避免脏读，性能适中
高并发 OLTP（电商、社交）READ COMMITTED SNAPSHOT读写不互斥，吞吐量高
报表查询READ UNCOMMITTED 或 SNAPSHOT不阻塞业务，容忍一定不一致
数据一致性要求极高（对账）SERIALIZABLE完全隔离（但慎用，性能差）
分析统计（多个相关查询需一致）SNAPSHOT事务级别一致性快照

最佳实践建议

-- 1. 强烈推荐：启用 RCSI（绝大多数 OLTP 系统）
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE YourDB SET ALLOW_SNAPSHOT_ISOLATION OFF;  -- 不需要 SNAPSHOT 时关闭

-- 2. 避免使用 NOLOCK（除非报表且能容忍不一致）
-- ❌ 不推荐
SELECT * FROM Orders WITH (NOLOCK) WHERE Status = 'Pending';

-- 3. 长事务中的多次读取需要一致性，使用 REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
    SELECT * FROM Inventory WHERE ProductID = 100;  -- 第一次
    -- 做一些业务逻辑
    SELECT * FROM Inventory WHERE ProductID = 100;  -- 第二次，保证一致
COMMIT

-- 4. 避免 SERIALIZABLE，除非你真的需要

七、监控当前隔离级别

-- 查看数据库当前设置
SELECT 
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
FROM sys.databases
WHERE database_id = DB_ID();

-- 查看当前会话的隔离级别
SELECT 
    session_id,
    CASE transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable Read'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END AS isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

八、核心总结

知识点核心要点
脏读读到未提交数据，用 READ COMMITTED 避免
不可重复读同一事务内前后不一致，用 REPEATABLE READ 避免
幻读行数变化，用 SERIALIZABLE 避免
RCSI推荐默认开启，读写不互斥，大幅提升并发
SNAPSHOT事务级别一致性，有更新冲突检测
死锁按相同顺序访问资源、缩短事务、启用 RCSI
选择原则先考虑 RCSI，特殊场景再升级隔离级别

一句话记住本期内容：

隔离级别是并发性能的开关——默认 READ COMMITTED 读写互斥，开启 RCSI 立即释放读锁，SNAPSHOT 给你事务级一致性，SERIALIZABLE 虽安全但性能最差。

动手练习

-- 场景：订单系统，高并发读写
-- 问题1：以下查询应该使用什么隔离级别？
-- 需求：生成报表，允许少量数据偏差，但不能被写事务阻塞

-- 问题2：如何避免下面事务的死锁？
BEGIN TRAN
    UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 100;
    UPDATE Inventory SET Reserved = Reserved + 1 WHERE ProductID = 100;
COMMIT
点击查看参考答案

问题1：使用 READ UNCOMMITTED 或 RCSI

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 或依赖 RCSI（如果已启用）
SELECT * FROM Orders WHERE OrderDate = '2024-01-01';

问题2：死锁通常发生在多个事务以不同顺序访问 Products 和 Inventory。解决方案：

-- 方案1：确保所有事务都先更新 Products，再更新 Inventory
-- 方案2：合并为一个 UPDATE（如果可能）
UPDATE p 
SET p.Stock = p.Stock - 1, i.Reserved = i.Reserved + 1
FROM Products p
JOIN Inventory i ON p.ProductID = i.ProductID
WHERE p.ProductID = 100;

下一期预告

存储引擎内幕——数据页、区、空间管理

  • SQL Server 如何物理存储数据：页（8KB）、区（64KB）
  • 堆表 vs 聚集索引表的内部结构
  • 行溢出、页拆分、碎片产生的原理
  • 压缩技术：行压缩 vs 页压缩 vs 列存储
  • 实战：如何选择和配置文件组

📌 本文代码已在 SQL Server 2019+ 验证。启用 RCSI 需要评估 tempdb 负载和现有应用的兼容性。

本系列持续更新中，点击关注不错过下一期。

avatar
文章
阅读
粉丝