搜尋本站文章

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



-- 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