德瑞克：SQL Server 學習筆記: [SQL Server]：UPDATE 或 DELETE 陳述式，請勿使用 NOLOCK、READUNCOMMITTED

2017-02-17

[SQL Server]：UPDATE 或 DELETE 陳述式，請勿使用 NOLOCK、READUNCOMMITTED

WITH (NOLOCK)、READUNCOMMITTED 是允許「中途讀取(Dirty Read)」。
這或許減少了「封鎖(Blocked Lock)」，但卻有許多副作用必須面對，將在後續文章討論此議題。

今 SQL Server，已經在 INSERT、UPDATE、DELETE 或 MERGE 陳述式上，禁止使用 NOLOCK、READUNCOMMITTED。

在 SQL Server 線上說明，先前已說明：

SQL Server 查詢最佳化工具會忽略套用在 UPDATE 或 DELETE 陳述式目標資料表的 FROM 子句中的 READUNCOMMITTED 和 NOLOCK 提示。
請避免在新的開發工作中使用此內容中的這些提示，並規劃修改目前在使用這些提示的應用程式。

示範環境
SQL Server 2016 SP1 Enterprise Edition

範例程式碼

USE Northwind
GO
SELECT * FROM Products
GO

UPDATE Products WITH (NOLOCK)
SET UnitsInStock=99
WHERE ProductID=1

錯誤訊息：

訊息 1065，層級 15，狀態 1，行 22
NOLOCK 與 READUNCOMMITTED 鎖定提示不允許用在 INSERT、UPDATE、DELETE 或 MERGE 陳述式的目標資料表。

-- 01_不允許使用NOLOCK 與 READUNCOMMITTED 鎖定提示

NOLOCK、READUNCOMMITTED

是指定允許中途讀取。不會發出任何共用鎖定來防止其他交易修改目前交易所讀取的資料，其他交易所設定的獨佔鎖定也不會封鎖目前交易，使它無法讀取鎖定的資料。
允許中途讀取可以提高並行程度，但代價是所讀取的資料修改後來會被其他交易回復。

這可能會使您的交易發生錯誤、為使用者提供永遠不被認可的資料，或是讓使用者看到記錄兩次 (或是根本看不到)。

READUNCOMMITTED 和 NOLOCK 提示只適用於資料鎖定。
所有的查詢 (包括具有 READUNCOMMITTED 和 NOLOCK 提示的查詢)，都會在編譯和執行期間取得 Sch-S (結構描述穩定性) 鎖定。

因此，當並行交易在資料表上保有 Sch-M (結構描述修改) 鎖定時，查詢將會遭到封鎖。
例如，資料定義語言 (DDL) 作業會在修改資料表的結構描述資訊之前先取得 Sch-M 鎖定。

任何並行查詢，包括以 READUNCOMMITTED 或 NOLOCK 提示執行的查詢，會在嘗試取得 Sch-S 鎖定時遭到封鎖。
相反地，保有 Sch-S 鎖定的查詢將會封鎖嘗試取得 Sch-M 鎖定的並行交易。

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

注意
SQL Server 的未來版本將移除套用到 UPDATE 或 DELETE 陳述式目標資料表的 FROM 子句中 READUNCOMMITTED 和 NOLOCK 提示的使用支援。
請避免在新的開發工作中使用此內容中的這些提示，並規劃修改目前在使用這些提示的應用程式。

-- 02_BOL_資料表提示_移除 NOLOCK 或 READUNCOMMITTED