在SQL Server中有時候會使用提示（Hint）強制SQL使用行鎖（Row Lock），前兩天有個同僚咨詢了一個問題，如何定位Row Lock具體鎖定了哪一行。其實這個問題隻适合研究一下，實際意義并不大，因為找到、定位被鎖定的行的代價開銷較大，而意義卻不怎麼大，而且使用場景也很少。那麼下面我們來探讨、研究一下這個問題吧：
在會話視窗（會話ID=65）下執行下面SQL語句，模拟SQL Server使用行鎖鎖定某一行記錄：
在另外一個會話視窗使用下面SQL查詢，我們能看到相關鎖的一些資訊，如下所示，但是這些資訊還不夠詳細，我們還需要更詳細的資訊：
查詢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沒有任何輸出資訊
如下截圖所示，就可以找到行鎖（Row Lock）鎖定了row=0這行記錄（注意，這裡的行記錄是從0開始的，而不是1），也就是DatabaseLogID=1的記錄。如果1:273:2， 那麼查詢條件中row=2 這個表示這個頁面的第幾行記錄。
但是，有時候你鎖定了一行，查詢sys.dm_tran_locks時，你會發現resource_type為RID類型的記錄有好幾條，如下所示：
其實真正是資料頁的隻有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 = <b>G</b>lobal <b>A</b>llocation <b>M</b>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 = <b>S</b>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 = <b>I</b>ndex <b>A</b>llocation <b>M</b>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 = <b>P</b>age <b>F</b>ree <b>S</b>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.
<b>參考資料：</b>
http://blog.csdn.net/sqlserverdiscovery/article/details/13291629
https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/