[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

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

---

參考資料

SQL Server 2016 中已被取代的 Database Engine 功能
https://msdn.microsoft.com/zh-tw/library/ms143729.aspx

資料表提示 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms187373.aspx

答客問：WITH (NOLOCK) 與 unlock 的差異？
http://sharedderrick.blogspot.tw/2011/08/with-nolock-unlock.html

Bad habits : Putting NOLOCK everywhere
https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms173763.aspx