[SQL] 資料表提示-NOLOCK

利用資料表提示-NOLOCK 來進行資料查詢是實務上常見方式，但該方式會造成 Dirty Read，驗證一下該情況

NOLOCK 相當於 READ UNCOMMITTED 隔離層級，Dirty Read 官方文件解釋如下

指定陳述式可以讀取其他交易已修改，但尚未認可的資料列。

在 READ UNCOMMITTED 層級執行的交易不會發出共用鎖定來防止其他交易修改目前交易所讀取的資料。 防止目前交易讀取其他交易已修改而尚未認可之資料列的獨佔鎖定，也不會封鎖 READ UNCOMMITTED 交易。 當設定這個選項時，可能會讀取到尚未認可的修改項目，這稱為中途讀取 (Dirty Read)。 在交易結束之前，資料中的值可以變更，資料列也可以在資料集中出現或消失。 這個選項的效果，與在交易中將所有 SELECT 陳述式之所有資料表設為 NOLOCK 相同。 這是隔離等級中限制最少的一種。

Sample Code

USE AdventureWorks
GO 

DROP TABLE IF EXISTS tbNoLockDemo

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

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

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

----- NOLCOK Demo
----- Session1
-- Step1：開啟交易並更新 ID = 2 資料，此時交易還在
begin transaction 
UPDATE tbNoLockDemo SET Col1 = 999 WHERE ID = 2

-- Step3：把交易 Rollback 模擬交易失敗並查詢 ID = 2 資料 
ROLLBACK
SELECT * FROM tbNoLockDemo WITH (NOLOCK) WHERE ID = 2

----- Session2
-- Step2：利用 NOLOCK 查詢 ID = 2 資料，此時 Session1 還在交易內
SELECT * FROM tbNoLockDemo WITH (NOLOCK) WHERE ID = 2

依 Script 操作就可以產生下述 Dirty Read 結果，Session1 資料為 2，但 Session2 資料為 999

UPDATE、DELETE 官方文件說明

無法針對插入、更新或刪除作業修改的資料表指定 READUNCOMMITTED 和 NOLOCK。 SQL Server 查詢最佳化工具會忽略套用在 UPDATE 或 DELETE 陳述式目標資料表的 FROM 子句中的 READUNCOMMITTED 和 NOLOCK 提示。

計算欄位官方文件說明

如果資料表包含計算資料行，且計算資料行是由存取其他資料表之資料行的運算式或函數來計算，資料表提示就不會在這些資料表上使用，也不會傳播。 例如，在查詢中指定資料表的 NOLOCK 資料表提示。 這份資料表擁有多個計算資料行，這些計算資料行會利用存取另一資料表中之資料行的運算式和函數的組合來進行計算。 當存取運算式和函數所參考的資料表時，它們不會使用 NOLOCK 資料表提示。

參考資料
• 資料表提示
• SET TRANSACTION ISOLATION LEVEL
• 論壇討論
• Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.