SQL Server中如何定位Row Lock鎖定哪一行數據

在SQL Server中有時候會使用提示（Hint）強制SQL使用行鎖（Row Lock），前兩天有個同事咨詢了一個問題，如何定位Row Lock具體鎖定了哪一行。其實這個問題只適合研究一下，實際意義並不大，因為找到、定位被鎖定的行的代價開銷較大，而意義卻不怎麼大，而且使用場景也很少。那麼下麵我們來探討、研究一下這個問題吧：

在會話視窗（會話ID=65）下執行下麵SQL語句，模擬SQL Server使用行鎖鎖定某一行記錄： 

USE AdventureWorks2012;

GO

SELECT  @@SPID;

BEGIN TRAN;

UPDATE  [dbo].[DatabaseLog] WITH ( ROWLOCK )

SET     TSQL = N'dddd'

WHERE   DatabaseLogID = 1;

--ROLLBACK;

在另外一個會話視窗使用下麵SQL查詢，我們能看到相關鎖的一些信息，如下所示，但是這些信息還不夠詳細，我們還需要更詳細的信息：

SELECT Db_name(RSC_DBID)                AS 'DATABASE_NAME', 

       CASE RSC_TYPE 

         WHEN 1 THEN 'null' 

         WHEN 2 THEN 'DATABASE' 

         WHEN 3 THEN 'FILE' 

         WHEN 4 THEN 'INDEX' 

         WHEN 5 THEN 'TABLE' 

         WHEN 6 THEN 'PAGE' 

         WHEN 7 THEN 'KEY' 

         WHEN 8 THEN 'EXTEND' 

         WHEN 9 THEN 'RID ( ROW ID)' 

         WHEN 10 THEN 'APPLICATION' 

       END                              AS 'REQUEST_TYPE', 

       CASE REQ_OWNERTYPE 

         WHEN 1 THEN 'TRANSACTION' 

         WHEN 2 THEN 'CURSOR' 

         WHEN 3 THEN 'SESSION' 

         WHEN 4 THEN 'ExSESSION' 

       END                              AS 'REQUEST_OWNERTYPE', 

       Object_name(RSC_OBJID, RSC_DBID) AS 'OBJECT_NAME', 

       PROCESS.HOSTNAME, 

       PROCESS.NT_DOMAIN, 

       PROCESS.NT_USERNAME, 

       PROCESS.PROGRAM_NAME, 

       SQLTEXT.TEXT 

FROM   sys.syslockinfo LOCK 

       JOIN sys.sysprocesses PROCESS 

         ON LOCK.REQ_SPID = PROCESS.SPID 

       CROSS apply sys.DM_EXEC_SQL_TEXT(PROCESS.SQL_HANDLE) SQLTEXT 

WHERE  PROCESS.SPID = 65 

查詢sys.dm_tran_locks我們可以得到更詳細的信息，例如，從resource_description中我們可以得到file_id=1, 頁面編號為273，這個頁面的第一條記錄（0）

SELECT  resource_type ,

        resource_database_id , --資料庫id

        resource_description , --資源描述

        resource_associated_entity_id , --資源關聯實體id

        request_mode , --請求模式

        request_type , --請求類型

        request_status ,

        request_session_id , --請求會話id

        request_owner_type

FROM    sys.dm_tran_locks

WHERE   request_session_id = 65;

準備下麵腳本，為了後續我們定位到行鎖鎖定哪一行記錄。準備好後面腳本後，我們就可以開始測試了。註意，需要開啟跟蹤DBCC TRACEON(3604)。否則DBCC PAGE沒有任何輸出信息

IF EXISTS (SELECT * FROM sys.objects WHERE type='U' AND name='DBCC_PAGE_RESULT')

    DROP TABLE DBCC_PAGE_RESULT;

GO

CREATE TABLE DBCC_PAGE_RESULT

(

    [ParentObject]      NVARCHAR(200),

    [Object]          NVARCHAR(2000),

    [Field]          NVARCHAR(4000),

    [Value]          NVARCHAR(MAX)

)

GO

CREATE PROCEDURE PRC_DBCC_PAGE

(

 @dbid        INT,

 @filenum    INT,

 @pagenum     INT

)

AS

 DBCC PAGE(@dbid, @filenum,  @pagenum, 3) WITH TABLERESULTS;