SQL Server脏读防御指南

一、第一步：环境搭建——给数据库装上"零食监控器"

目标：创建测试表，像准备零食一样准备好数据。

步骤：

1. 创建测试表：

-- 创建模拟数据表（假设是"零食库存表"）  
CREATE TABLE Snacks  
(  
    Id INT PRIMARY KEY,  
    Name NVARCHAR(50),  
    Stock INT  
);  

-- 插入初始数据（袋装薯片库存50）  
INSERT INTO Snacks VALUES (1, '薯片', 50);

2. 开启两个会话：

• 在SQL Server Management Studio（SSMS）中打开两个查询窗口，分别模拟事务A和事务B。

注释解析：

• Stock字段模拟库存数量，初始值为50。

• 两个会话分别代表两个"偷吃零食"的事务。

二、第二步：复现脏读——让数据上演"偷吃现场"

目标：用两个事务模拟脏读，像偷吃薯片后被发现一样。

场景：

• 事务A：假装"偷吃"薯片，但还没提交。

• 事务B：假装"检查库存"，发现被偷吃的数据。

代码示例（事务A窗口）：

-- 事务A：偷吃20袋薯片（但不提交！）  
BEGIN TRANSACTION;  

UPDATE Snacks  
SET Stock = Stock - 20  
WHERE Id = 1;  

-- 暂停在此，等待事务B执行  
WAITFOR DELAY '00:00:10'; -- 等待10秒让事务B有时间执行  

ROLLBACK; -- 最终放弃偷吃（模拟回滚）

现象：

• 事务B会读到Stock=30，但事务A最终回滚，实际库存仍是50。

• 这就是脏读！就像偷吃薯片后又假装没动，但被监控拍到！

三、第三步：解决方案1——用Read Committed隔离级别"锁住零食袋"

目标：设置事务隔离级别，像给零食袋上锁一样防止未提交数据被读取。

步骤：

1. 在事务B中设置隔离级别：

-- 在事务B窗口中，修改查询为：  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
BEGIN TRANSACTION;  

SELECT * FROM Snacks WHERE Id = 1;  

-- 结果：Stock始终显示50（脏读被阻止！）  
COMMIT;

注释解析：

• READ COMMITTED：确保只能读取已提交的数据，像给零食袋加了"已开封需付款"的标签。

• 事务B现在会等待事务A提交或回滚，不会读取中间状态。

四、第四步：解决方案2——用锁机制"贴上封条"

目标：用显式锁强制阻止脏读，像给零食袋贴上"勿动"封条。

步骤：

1. 在事务A中使用排他锁：

-- 事务A：偷吃时立即加锁  
BEGIN TRANSACTION;  

UPDATE Snacks  
SET Stock = Stock - 20  
WHERE Id = 1  
WITH (ROWLOCK, XLOCK); -- 行级排他锁  

-- 等待期间，事务B无法读取此行！  
WAITFOR DELAY '00:00:10';  

ROLLBACK;

2. 事务B尝试读取：

-- 事务B：现在会阻塞，直到事务A释放锁  
SELECT * FROM Snacks WHERE Id = 1;

注释解析：

• XLOCK：强制对行加排他锁，其他事务无法读取或修改。

• 这就像给零食袋贴上"正在偷吃，请勿打扰"的封条！

五、第五步：解决方案3——用乐观锁"防闺蜜偷吃"

目标：用版本控制机制，像零食包装上的防伪码一样检测数据变化。

步骤：

1. 修改表结构，添加版本字段：

ALTER TABLE Snacks  
ADD Version INT DEFAULT 0; -- 版本号初始为0

2. 事务A尝试偷吃并更新版本号：

BEGIN TRANSACTION;  

-- 读取当前版本  
DECLARE @CurrentVersion INT;  
SELECT @CurrentVersion = Version FROM Snacks WHERE Id = 1;  

UPDATE Snacks  
SET Stock = Stock - 20, Version = Version + 1  
WHERE Id = 1 AND Version = @CurrentVersion; -- 检查版本是否一致  

-- 模拟回滚  
ROLLBACK;