一次意外的X鎖不阻塞問題

  最近有一個朋友問我一個關于給查詢操作強制上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所示。

image

圖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鎖的情況。

分類: SQL SERVER

本文轉自CareySon部落格園部落格，原文連結：http://www.cnblogs.com/CareySon/p/4226577.html如需轉載請自行聯系原作者