SQL Server中如何定位Row Lock鎖定哪一行資料

原文: 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;      

GO      

DBCC TRACEON(3604)      

;WITH    t AS ( SELECT   Object ,      

                        Field ,      

                        Value ,      

                        CASE WHEN CHARINDEX('Column', Object) > 0      

                             THEN CHARINDEX('Column', Object)      

                             ELSE CHARINDEX('Offset', Object)      

                        END AS substring_len      

               FROM     dbo.DBCC_PAGE_RESULT dp      

               WHERE    Object LIKE 'Slot%Column%'      

                        OR Field = 'KeyHashValue'      

             ),      

        tt      

          AS ( SELECT   Object ,      

                        Field ,      

                        Value ,      

                        CAST(SUBSTRING(Object, LEN('Slot') + 1,      

                                       substring_len - LEN('Slot') - 1) AS INT) AS row      

               FROM     t      

             ),      

        ttt      

          AS ( SELECT   Object ,      

                        Field ,      

                        Value ,      

                        row ,    --第幾行        

                        MAX(CASE WHEN Field = 'KeyHashValue' THEN Value      

                                 ELSE ''      

                            END) OVER ( PARTITION BY row ) AS KeyHashValue      

               FROM     tt      

             )      

    SELECT  *      

    FROM    ttt      

    WHERE   ttt.row = 0      

如下截圖所示，就可以找到行鎖（Row Lock）鎖定了row=0這行記錄（注意，這裡的行記錄是從0開始的，而不是1），也就是DatabaseLogID=1的記錄。如果1:273:2， 那麼查詢條件中row=2  這個表示這個頁面的第幾行記錄。

但是，有時候你鎖定了一行，查詢sys.dm_tran_locks時，你會發現resource_type為RID類型的記錄有好幾條，如下所示：

USE AdventureWorks2012;      

GO      

SELECT  @@SPID;      

BEGIN TRAN;      

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

SET     TSQL = N'dddd'      

WHERE   DatabaseLogID = 21;      

--ROLLBACK;      

其實真正是資料頁的隻有resource_description=1:273:4 這行記錄， 也就是說這行記錄位于Page Number=273下的第5條記錄

其它一些頁面，例如 1，295；  1,279等都不是資料頁，如下截圖所示：m_type的值表示這個是資料頁、索引頁、IAM頁等等。具體參考

m_type

·         This is the page type. The values you’re likely to see are:

o   1 – data page. This holds data records in a heap or clustered index leaf-level.

o   2 – index page. This holds index records in the upper levels of a clustered index and all levels of non-clustered indexes.

o   3 – text mix page. A text page that holds small chunks of LOB values plus internal parts of text tree. These can be shared between LOB values in the same partition of an index or heap.

o   4 – text tree page. A text page that holds large chunks of LOB values from a single column value.

o   7 – sort page. A page that stores intermediate results during a sort operation.

o   8 – GAM page. Holds global allocation information about extents in a GAM interval (every data file is split into 4GB chunks – the number of extents that can be represented in a bitmap on a single database page). Basically whether an extent is allocated or not. GAM =

G

lobal

A

llocation

M

ap. The first one is page 2 in each file. More on these in this post.

o   9 – SGAM page. Holds global allocation information about extents in a GAM interval. Basically whether an extent is available for allocating mixed-pages. SGAM =

S

hared GAM. the first one is page 3 in each file. More on these in this post.

o   10 – IAM page. Holds allocation information about which extents within a GAM interval are allocated to an allocation unit (portion of a table or index). IAM =

I

ndex

ap. More on these in this post.

o   11 – PFS page. Holds allocation and free space information about pages within a PFS interval (every data file is also split into approx 64MB chunks – the number of pages that can be represented in a byte-map on a single database page. PFS =

P

age

F

ree

pace. The first one is page 1 in each file. More on these in this post.

o   13 – boot page. Holds information about the database. There’s only one of these in the database. It’s page 9 in file 1.

o   15 – file header page. Holds information about the file. There’s one per file and it’s page 0 in the file.

o   16 – diff map page. Holds information about which extents in a GAM interval have changed since the last full or differential backup. The first one is page 6 in each file.

o   17 – ML map page. Holds information about which extents in a GAM interval have changed while in bulk-logged mode since the last backup. This is what allows you to switch to bulk-logged mode for bulk-loads and index rebuilds without worrying about breaking a backup chain. The first one is page 7 in each file.

o   18 – a page that’s be deallocated by DBCC CHECKDB during a repair operation.

o   19 – the temporary page that ALTER INDEX … REORGANIZE (or DBCC INDEXDEFRAG) uses when working on an index.

o   20 – a page pre-allocated as part of a bulk load operation, which will eventually be formatted as a ‘real’ page.

參考資料：

http://blog.csdn.net/sqlserverdiscovery/article/details/13291629

https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/