最近有一個朋友問我一個關于給查詢操作強制上X鎖卻不阻塞的問題。該查詢寫在一個存儲過程中，代碼如代碼1所示：
1: create PROC [dbo].[GetCityOrders]
2: @city NVARCHAR(10) ,
3: @num INT
4: AS
5: SET NOCOUNT ON
6:
7: BEGIN TRY
8:
9: BEGIN TRAN
10:
11: SELECT TOP ( @num )
12: id ,
13: number ,
14: price ,
15: mid ,
16: @city city
17: INTO #cityorders
18: FROM cmcc WITH ( XLOCK )
19: WHERE prov = 0
20: AND status = 0
21: AND city = @city
22:
23: UPDATE cmcc
24: SET status = 100
25: WHERE id IN ( SELECT id
26: FROM #cityorders )
27:
28: SELECT o.* ,
29: c.attach
30: FROM #cityorders o
31: LEFT JOIN cmcc_attach c ON o.id = c.id
32:
33: DROP TABLE #cityorders
34:
35: COMMIT TRAN
36:
37: END TRY
38: BEGIN CATCH
39:
40: ROLLBACK
41:
42: END CATCH
代碼1.
該存儲過程首先通過對查詢操作加X鎖，使得其他讀取操作更新時不影響該部分加X鎖的操作。乍一看沒有任何問題，但是當業務上線后就發現，即使查詢有了X鎖，但實際上還是會有多個調用該存儲過程的客戶端同時讀取到同一條數據的現象現象。
原因？
為了驗證原因，我們來做一個Demo測試，首先我們創建測試表，代碼如代碼2所示。
1: CREATE TABLE dbo.DemoX
2: (
3: [key] INT PRIMARY KEY ,
4: [value] INT,
5: );
6: GO
7: INSERT INTO dbo.DemoX
8: ( [key], value )
9: VALUES ( 1, 100 );
10: GO
代碼2.創建測試DEMO
接下來，對該DemoX表進行Select操作，并查看鎖。如代碼3所示。
1: BEGIN TRAN
2: SELECT [key],value
3: FROM dbo.DemoX D WITH (XLOCK);
4:
5: SELECT L.resource_type,
6: L.request_mode,
7: L.request_status,
8: L.resource_description,
9: L.resource_associated_entity_id
10: FROM sys.dm_tran_current_transaction T
11: JOIN sys.dm_tran_locks L
12: ON L.request_owner_id = T.transaction_id;
代碼3.使用X鎖提示查語句
在代碼3中顯式指定了X鎖，并查看上鎖情況，可以看出X鎖以及對應父對象上的意向鎖都正常存在，如圖1所示。
圖1.
我們再開另外一個窗口運行一個普通的Select，結果如圖2所示。
圖2.
為什么沒有阻塞
理論上來說，第二個查詢應該會被阻塞，因為第二個查詢所需加的S鎖和第一個查詢的X鎖不兼容。后來在網上找打StackOverFlow的一篇博文：“http://stackoverflow.com/questions/4609217/sql-server-the-misleading-xlock-optimizations”，找到了答案。
在SQL Server中，默認的已提交讀為了保證不讀臟數據（既在內存中修改，還未落盤的數據），會對需要查找的數據上S鎖，但如果發現數據并不是臟數據，則會優化跳過加S鎖的步驟，代碼3中的查詢語句強制使用了X鎖提示，但未進行任何數據修改，所以不存在臟數據，因此后續查詢就通過優化放棄使用S鎖，從而不阻塞，導致了意料之外的結果。
解決辦法
SQL Server對于該特性的優化僅僅對行鎖生效，如果在指定查詢時使用頁鎖提示，則會按照語句，對阻塞后續查詢，代碼如代碼4所示。
1: SELECT [key],value
2: FROM dbo.DemoX D WITH (PAGLOCK,XLOCK);
代碼4.
但顯而易見，該方法會降低并發，如果有可能，請不要對Select操作使用X鎖提示，否則請加上頁鎖提示。
另一個辦法是使用CTE進行表更新，將代碼1中的代碼兩部分合二為一，數據在更新時會導致臟數據，因此不會出現跳過S鎖的情況。
文章列表
留言列表
