[SQL] Phantom Read

隔離層級 Repeatable Read，因為 S-Lock 會在交易結束後才釋放，所以不會有 Nonrepeatable Read 問題，但會發生 Phantom Read，也就是交易內多次讀取資料，資料筆數會有所差異

官方文件說明

指定陳述式不能讀取其他交易已修改而尚未認可的資料，且在目前交易完成之前，任何其他交易都不能修改目前交易已讀取的資料。

交易中每個陳述式所讀取的所有資料都會設定共用鎖定，直到交易完成為止。 這可以防止其他交易修改目前交易已讀取的任何資料列。 其他交易仍可以插入新資料列，但必須符合目前交易所發出的陳述式搜尋條件。 如果目前交易稍後重試陳述式，便會擷取新的資料列，因而產生虛設項目讀取 (Phantom Read)。 由於共用鎖定會保留至交易完成，而不是在每個陳述式結束時釋出，因此並行發生的可能性會低於預設的 READ COMMITTED 隔離等級。 請只在必要時，才使用這個選項。

Sample Code

USE AdventureWorks
GO 

DROP TABLE IF EXISTS tbPhantomReadDemo

CREATE TABLE tbPhantomReadDemo
(
  ID int identity(1,1) Primary key,
  Col1 int
)

INSERT INTO tbPhantomReadDemo (Col1) 
VALUES(1) , (2) , (3) , (4) , (5)

-- 顯示建立資料
SELECT * FROM tbPhantomReadDemo

----- Phantom Read Demo
----- Session1
-- Step1：開啟交易並把隔離層級設定為 Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
-- 讀取資料
SELECT * FROM tbPhantomReadDemo
WAITFOR DELAY '00:00:05'
-- Step3：再次讀取資料 
SELECT * FROM tbPhantomReadDemo

----- Session2
-- Step2：insert 資料
INSERT INTO tbPhantomReadDemo (Col1)
	VALUES(6)

上述 Code 流程圖示化

 

Session1 查詢結果

 

S-Lock 雖然是交易結束才會釋放，但是 insert 資料不在 S-Lock 範圍內，無法因為鎖互斥而產生 Blocking