搜尋本站文章

2011-08-26

答客問:WITH (NOLOCK) 與 unlock 的差異?

使用版本:
SQL Server 2005
SQL Server 2008
SQL Server 2008 R2

有朋友來信詢問:

--EX1. WITH (NOLOCK)
select * from mytest with (nolock)
GO
--EX2. unlock
select * from mytest unlock
GO


以上,這兩句語法有什麼差異?


(一)關於 EX1. WITH (NOLOCK)

NOLOCK:這相當於 READUNCOMMITTED。

READUNCOMMITTED
指定允許中途讀取。

不會發出任何共用鎖定來防止其他交易修改目前交易所讀取的資料,其他交易所設定的獨佔鎖定也不會封鎖目前交易,使它無法讀取鎖定的資料。

允許中途讀取可以提高並行程度,但代價是所讀取的資料修改後來會被其他交易回復。

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

如需有關中途讀取、不可重複讀取和幽靈讀取的詳細資訊,請參閱<並行效果>。

READUNCOMMITTED 和 NOLOCK 提示只適用於資料鎖定。

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

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

任何並行查詢,包括以 READUNCOMMITTED 或 NOLOCK 提示執行的查詢,會在嘗試取得 Sch-S 鎖定時遭到封鎖。

相反地,保有 Sch-S 鎖定的查詢將會封鎖嘗試取得 Sch-M 鎖定的並行交易。

如需有關鎖定行為的詳細資訊,請參閱<鎖定相容性 (Database Engine)>。

無法針對插入、更新或刪除作業修改的資料表指定 READUNCOMMITTED 和 NOLOCK。

SQL Server 查詢最佳化工具會忽略套用在 UPDATE 或 DELETE 陳述式目標資料表的 FROM 子句中的 READUNCOMMITTED 和 NOLOCK 提示。

SQL Server 的未來版本將移除套用到 UPDATE 或 DELETE 陳述式目標資料表的 FROM 子句中 READUNCOMMITTED 和 NOLOCK 提示的使用支援。

請避免在新的開發工作中使用此內容中的這些提示,並規劃修改目前在使用這些提示的應用程式。

您可以使用下列其中一個選項,防止交易讀到尚未認可的資料修改 (中途讀取),同時也將鎖定爭用的情況減到最低:

(1) READ_COMMITTED_SNAPSHOT 資料庫選項設為 ON 的 READ COMMITTED 隔離等級。
(2) SNAPSHOT 隔離等級。


(二)關於 EX2. unlock

印象中,SQL Server 沒有 unlock 這類的用法,這應該是「資料表別名(Table Aliases)」的寫法,請參考以下的範例程式碼:

--01 查詢資料表:Orders
-- 回傳 830 筆資料列。
USE Northwind
GO
SELECT * FROM Orders
GO

--02 設定「資料表別名(Table Aliases)」為:unlock
-- 回傳 830 筆資料列。
SELECT * FROM Orders unlock
GO

--03 使用此「資料表別名」,搭配 WHERE 子句來篩選資料列
-- 僅是回傳 1 筆資料列。
SELECT * FROM Orders unlock
WHERE unlock.OrderID =10248
GO



也就是說,若是使用 EX1. WITH (NOLOCK),是允許「中途讀取」。

若是使用 EX2. unlock,應該是當「資料表別名(Table Aliases)」來使用,無法支援「中途讀取」。

在此,感謝熊爺的解惑與說明。



參考資料:

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

使用資料表別名
http://msdn.microsoft.com/zh-tw/library/ms187455%28v=SQL.105%29.aspx

聯結基礎觀念
http://msdn.microsoft.com/zh-tw/library/ms191517%28v=SQL.105%29.aspx