快捷導(dǎo)航

SQL Server臟讀防御指南

更新時(shí)間：2025年07月08日 09:31:38 作者：墨瑾軒

臟讀就是指當(dāng)一個(gè)事務(wù)正在訪問數(shù)據(jù),并且對(duì)數(shù)據(jù)進(jìn)行了修改,而這種修改還沒有提交到數(shù)據(jù)庫中,這時(shí)另外一個(gè)事務(wù)也訪問這個(gè)數(shù)據(jù),然后使用了這個(gè)數(shù)據(jù),本文給大家介紹了在SQL Server中如何防御臟讀,需要的朋友可以參考下

一、第一步：環(huán)境搭建——給數(shù)據(jù)庫裝上"零食監(jiān)控器"
二、第二步：復(fù)現(xiàn)臟讀——讓數(shù)據(jù)上演"偷吃現(xiàn)場"
三、第三步：解決方案1——用Read Committed隔離級(jí)別"鎖住零食袋"
四、第四步：解決方案2——用鎖機(jī)制"貼上封條"
五、第五步：解決方案3——用樂觀鎖"防閨蜜偷吃"
六、第六步：終極防御——用快照隔離級(jí)別"開監(jiān)控錄像"
七、第七步：實(shí)戰(zhàn)演練——用代碼驗(yàn)證所有方案

一、第一步：環(huán)境搭建——給數(shù)據(jù)庫裝上"零食監(jiān)控器"

目標(biāo)：創(chuàng)建測試表，像準(zhǔn)備零食一樣準(zhǔn)備好數(shù)據(jù)。

步驟：

創(chuàng)建測試表：

-- 創(chuàng)建模擬數(shù)據(jù)表（假設(shè)是"零食庫存表"）  
CREATE TABLE Snacks  
(  
    Id INT PRIMARY KEY,  
    Name NVARCHAR(50),  
    Stock INT  
);  

-- 插入初始數(shù)據(jù)（袋裝薯片庫存50）  
INSERT INTO Snacks VALUES (1, '薯片', 50);

開啟兩個(gè)會(huì)話：
- 在SQL Server Management Studio（SSMS）中打開兩個(gè)查詢窗口，分別模擬事務(wù)A和事務(wù)B。

注釋解析：

Stock字段模擬庫存數(shù)量，初始值為50。
兩個(gè)會(huì)話分別代表兩個(gè)"偷吃零食"的事務(wù)。

二、第二步：復(fù)現(xiàn)臟讀——讓數(shù)據(jù)上演"偷吃現(xiàn)場"

目標(biāo)：用兩個(gè)事務(wù)模擬臟讀，像偷吃薯片后被發(fā)現(xiàn)一樣。

場景：

事務(wù)A：假裝"偷吃"薯片，但還沒提交。
事務(wù)B：假裝"檢查庫存"，發(fā)現(xiàn)被偷吃的數(shù)據(jù)。

代碼示例（事務(wù)A窗口）：

-- 事務(wù)A：偷吃20袋薯片（但不提交?。? 
BEGIN TRANSACTION;  

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

-- 暫停在此，等待事務(wù)B執(zhí)行  
WAITFOR DELAY '00:00:10'; -- 等待10秒讓事務(wù)B有時(shí)間執(zhí)行  

ROLLBACK; -- 最終放棄偷吃（模擬回滾）

代碼示例（事務(wù)B窗口）：

-- 事務(wù)B：查看庫存（可能會(huì)讀到臟數(shù)據(jù)）  
SELECT * FROM Snacks WHERE Id = 1;  

-- 預(yù)期結(jié)果：Stock = 30（但事務(wù)A未提交?。?

現(xiàn)象：

事務(wù)B會(huì)讀到Stock=30，但事務(wù)A最終回滾，實(shí)際庫存仍是50。
這就是臟讀！就像偷吃薯片后又假裝沒動(dòng)，但被監(jiān)控拍到！

三、第三步：解決方案1——用Read Committed隔離級(jí)別"鎖住零食袋"

目標(biāo)：設(shè)置事務(wù)隔離級(jí)別，像給零食袋上鎖一樣防止未提交數(shù)據(jù)被讀取。

步驟：

在事務(wù)B中設(shè)置隔離級(jí)別：

-- 在事務(wù)B窗口中，修改查詢?yōu)椋? 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
BEGIN TRANSACTION;  

SELECT * FROM Snacks WHERE Id = 1;  

-- 結(jié)果：Stock始終顯示50（臟讀被阻止?。? 
COMMIT;

注釋解析：

READ COMMITTED：確保只能讀取已提交的數(shù)據(jù)，像給零食袋加了"已開封需付款"的標(biāo)簽。
事務(wù)B現(xiàn)在會(huì)等待事務(wù)A提交或回滾，不會(huì)讀取中間狀態(tài)。

四、第四步：解決方案2——用鎖機(jī)制"貼上封條"

目標(biāo)：用顯式鎖強(qiáng)制阻止臟讀，像給零食袋貼上"勿動(dòng)"封條。

步驟：

在事務(wù)A中使用排他鎖：

-- 事務(wù)A：偷吃時(shí)立即加鎖  
BEGIN TRANSACTION;  

UPDATE Snacks  
SET Stock = Stock - 20  
WHERE Id = 1  
WITH (ROWLOCK, XLOCK); -- 行級(jí)排他鎖  

-- 等待期間，事務(wù)B無法讀取此行！  
WAITFOR DELAY '00:00:10';  

ROLLBACK;

事務(wù)B嘗試讀取：

-- 事務(wù)B：現(xiàn)在會(huì)阻塞，直到事務(wù)A釋放鎖  
SELECT * FROM Snacks WHERE Id = 1;

注釋解析：

XLOCK：強(qiáng)制對(duì)行加排他鎖，其他事務(wù)無法讀取或修改。
這就像給零食袋貼上"正在偷吃，請(qǐng)勿打擾"的封條！

五、第五步：解決方案3——用樂觀鎖"防閨蜜偷吃"

目標(biāo)：用版本控制機(jī)制，像零食包裝上的防偽碼一樣檢測數(shù)據(jù)變化。

步驟：

修改表結(jié)構(gòu)，添加版本字段：

ALTER TABLE Snacks  
ADD Version INT DEFAULT 0; -- 版本號(hào)初始為0

事務(wù)A嘗試偷吃并更新版本號(hào)：

BEGIN TRANSACTION;  

-- 讀取當(dāng)前版本  
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;

事務(wù)B檢查版本號(hào)：

SELECT * FROM Snacks WHERE Id = 1;  

-- 結(jié)果：版本號(hào)未變化，Stock仍為50

注釋解析：

樂觀鎖通過版本號(hào)比對(duì)，確保只有未被修改的數(shù)據(jù)能被更新。
這就像零食包裝上的防偽碼，一撕就暴露"偷吃痕跡"！

六、第六步：終極防御——用快照隔離級(jí)別"開監(jiān)控錄像"

目標(biāo)：用快照隔離級(jí)別記錄數(shù)據(jù)歷史，像監(jiān)控錄像回放一樣防偷吃。

步驟：

在數(shù)據(jù)庫級(jí)別啟用快照隔離：

-- 在SSMS中右鍵數(shù)據(jù)庫 → 屬性 → 選項(xiàng) → 啟用"允許快照隔離"  
ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

事務(wù)B使用快照隔離：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
BEGIN TRANSACTION;  

SELECT * FROM Snacks WHERE Id = 1;  

-- 即使事務(wù)A未提交，結(jié)果仍為50！  
COMMIT;

注釋解析：

快照隔離通過記錄歷史版本，讓事務(wù)B看到事務(wù)A修改前的數(shù)據(jù)。
這就像監(jiān)控錄像回放，永遠(yuǎn)顯示"偷吃前"的庫存！

七、第七步：實(shí)戰(zhàn)演練——用代碼驗(yàn)證所有方案

場景：模擬多個(gè)解決方案的實(shí)際效果。

代碼示例（事務(wù)A）：

-- 方案1：臟讀發(fā)生  
BEGIN TRANSACTION;  
UPDATE Snacks SET Stock = 30 WHERE Id = 1;  
-- 不提交，等待事務(wù)B讀取

代碼示例（事務(wù)B）：

-- 方案1：臟讀發(fā)生  
SELECT * FROM Snacks; -- 讀到30  

-- 方案2：使用Read Committed  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
SELECT * FROM Snacks; -- 仍讀到50！  

-- 方案3：使用樂觀鎖  
SELECT * FROM Snacks WHERE Version = 0; -- 確保未被修改

通過本文，你已經(jīng)掌握了：