星期五, 10月 01, 2021

[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 流程圖示化
 
[SQL] Phantom Read-1

Session1 查詢結果
 
[SQL] Phantom Read-2

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

沒有留言:

張貼留言