[SQL] 資料表提示-ReadPast

討論 NOLOCK 常常會一併討論 READPAST，相較於 NOLOCK 會產生 Dirty Read 問題，READPAST 沒有 Dirty Read 困惱，但 READPAST 會略過被封鎖資料

官方文件說明如下

指定 Database Engine 不讀取其他交易已鎖定的資料列。 當指定 READPAST 時，系統會略過資料列層級鎖定，但不會略過頁面層級的鎖定。 亦即，Database Engine 會略過資料列，而不會封鎖目前的交易，直到釋放鎖定為止。 例如，假設資料表 T1 包含了值為 1、2、3、4、5 的單一整數資料行。 如果交易 A 將 3 的值變更為 8，但是尚未認可，則 SELECT * FROM T1 (READPAST) 會產生 1、2、4、5 的值。 READPAST 主要是在實作使用 SQL Server 資料表的工作佇列時用來減少鎖定競爭。 使用 READPAST 的佇列讀取器會略過其他交易已鎖定的佇列項目，直接到下一個可用的佇列項目，不需要等待其他交易釋放鎖定。

依上述範例來驗證

USE AdventureWorks
GO

DROP TABLE IF EXISTS tbReadPastDemo

CREATE TABLE tbReadPastDemo
(
  ID int identity(1,1) Primary key,
  Col1 int
)
 
INSERT INTO tbReadPastDemo (Col1) 
VALUES(1) , (2) , (3) , (4) , (5)
 
-- 顯示建立資料
SELECT * FROM tbReadPastDemo

----- ReadPast Demo
----- Session A
-- Step1：開啟交易並更新 ID = 3 資料為 8，此時交易還在
begin transaction
UPDATE tbReadPastDemo SET Col1 = 8 WHERE ID = 3

-- Step3：結束交易
ROLLBACK

----- Session B
-- Step2：利用 ReadPast 查詢 ID = 2 資料，此時 Session1 還在交易內
SELECT * FROM tbReadPastDemo WITH (ReadPast)

從 Session2 可以發現，只有 4 筆資料，ID = 3 的資料被忽略了

UPDATE、DELETE 官方文件說明

UPDATE 或 DELETE 陳述式所參考的任何資料表，以及 FROM 子句所參考的任何資料表，都可以指定 READPAST。 當在 UPDATE 陳述式中指定 READPAST 時，只有在讀取資料來識別要更新的記錄時，才會套用 READPAST，不論是在陳述式的哪個位置指定，都是如此。 INSERT 陳述式 INTO 子句中的資料表不能指定 READPAST。 當讀取外部索引鍵或索引檢視表時，或當修改次要索引時，使用 READPAST 的更新或刪除作業可能會進行封鎖。

參考資料
• 資料表提示
• SQL with(nolock)與with(readpast)