SQL Server脏读防御指南_MsSqlserver

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

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

步骤：

创建测试表：

-- 创建模拟数据表（假设是"零食库存表"）  
create table snacks  
(  
    id int primary key,  
    name nvarchar(50),  
    stock int  
);  

-- 插入初始数据（袋装薯片库存50）  
insert into snacks values (1, '薯片', 50);

开启两个会话：
- 在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窗口）：

-- 事务b：查看库存（可能会读到脏数据）  
select * from snacks where id = 1;  

-- 预期结果：stock = 30（但事务a未提交！）

现象：

事务b会读到stock=30，但事务a最终回滚，实际库存仍是50。
这就是脏读！就像偷吃薯片后又假装没动，但被监控拍到！

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

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

步骤：

在事务b中设置隔离级别：

-- 在事务b窗口中，修改查询为：  
set transaction isolation level read committed;  
begin transaction;  

select * from snacks where id = 1;  

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

注释解析：

read committed：确保只能读取已提交的数据，像给零食袋加了"已开封需付款"的标签。
事务b现在会等待事务a提交或回滚，不会读取中间状态。

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

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

步骤：

在事务a中使用排他锁：

-- 事务a：偷吃时立即加锁  
begin transaction;  

update snacks  
set stock = stock - 20  
where id = 1  
with (rowlock, xlock); -- 行级排他锁  

-- 等待期间，事务b无法读取此行！  
waitfor delay '00:00:10';  

rollback;

事务b尝试读取：

-- 事务b：现在会阻塞，直到事务a释放锁  
select * from snacks where id = 1;

注释解析：

xlock：强制对行加排他锁，其他事务无法读取或修改。
这就像给零食袋贴上"正在偷吃，请勿打扰"的封条！

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

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

步骤：

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

alter table snacks  
add version int default 0; -- 版本号初始为0

事务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;

事务b检查版本号：

select * from snacks where id = 1;  

-- 结果：版本号未变化，stock仍为50

注释解析：

乐观锁通过版本号比对，确保只有未被修改的数据能被更新。
这就像零食包装上的防伪码，一撕就暴露"偷吃痕迹"！

六、第六步：终极防御——用快照隔离级别"开监控录像"

目标：用快照隔离级别记录数据历史，像监控录像回放一样防偷吃。

步骤：

在数据库级别启用快照隔离：

-- 在ssms中右键数据库 → 属性 → 选项 → 启用"允许快照隔离"  
alter database yourdatabase set allow_snapshot_isolation on;

事务b使用快照隔离：

set transaction isolation level snapshot;  
begin transaction;  

select * from snacks where id = 1;  

-- 即使事务a未提交，结果仍为50！  
commit;

注释解析：

快照隔离通过记录历史版本，让事务b看到事务a修改前的数据。
这就像监控录像回放，永远显示"偷吃前"的库存！

七、第七步：实战演练——用代码验证所有方案

场景：模拟多个解决方案的实际效果。

代码示例（事务a）：

-- 方案1：脏读发生  
begin transaction;  
update snacks set stock = 30 where id = 1;  
-- 不提交，等待事务b读取

代码示例（事务b）：

-- 方案1：脏读发生  
select * from snacks; -- 读到30  

-- 方案2：使用read committed  
set transaction isolation level read committed;  
select * from snacks; -- 仍读到50！  

-- 方案3：使用乐观锁  
select * from snacks where version = 0; -- 确保未被修改

通过本文，你已经掌握了：