This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view syslockinfo returns information on lock requests.  Whether the lock is granted, waiting to be granted, or converting to another type of lock, the information on the locking is available in the compatibility view..

The dynamic management view sys.dm_tran_locks is designed as a replacement to the compatibility view.  In the same fashion as syslockinfo, sys.dm_tran_locks returns information on locks; whether they are granted or waiting to be granted.

Complex Columns

One of the things that is unique about the compatiblity view syslockinfo is the inclusion in a number of columns that contain complex values.  The reason these values are complex is because the values they return and what they translate to changes for every type of lock.  The complex columns in syslockinfo are rsc_text, rsc_bin, and rsc_blk.

Rsc_text Column

The first complex column is rsc_text.  This column contains a text description of the lock.  Which means that the values included could be the hash value for the object that is locked, the file id and page number where the lock is occurring, the file id, page number, and row number for the lock, or many other variations.  There is as many possible description formats as there are lock types.

Rsc_bin Column

The second complex column is rsc_bin contains information about the lock.  It is intended as a value that contains everything about a lock that is needed if an external resource needed to decipher the lock in a single value.  The values stored in the binary value for this column include information on the file id, page number, row number, object id hash value, database id, and all of the other possible values that can comprise a lock.

Rsc_valblk Column

The last column is rsc_valblk; which returns information similar to rsc_bin.  The column is intended as a sort of overflow for the rsc_bin column, if there isn’t room for the data in the lock in that value.  Generally, all values for this column return the value 0×00000000000000000000000000000000.

Query Via syslockinfo

Before the data in syslockinfo can be used in a query, there are a few transformations that are needed for the data.  The columns that require transformations are rsc_type, req_mode, req_status, and req_ownertype.  The numeric values returned from these columns can be converted to text values using the definitions from Books Online.  Since the values in rsc_bin and rsc_valblk are designed for external application consumption, the query included won’t try to describe the values that they contain.  As for rsc_text, the value from that column will also be returned as is.  The resulting query is included in Listing 1.

--Listing 1 – Query for sys.syslockinfo
SELECT rsc_text
    , rsc_bin
    , rsc_valblk
    , rsc_dbid
    , rsc_indid
    , rsc_objid
    , CASE rsc_type WHEN 1 THEN 'NULL Resource (not used)'
        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 'EXTENT'
        WHEN 9 THEN 'RID (ROW ID)'
        WHEN 10 THEN 'APPLICATION' 
        WHEN 11 THEN 'METADATA' END rsc_type
    , rsc_flag
    , CASE req_mode WHEN 0 THEN 'NULL'
        WHEN 1 THEN 'Sch-S'
        WHEN 2 THEN 'Sch-M'
        WHEN 3 THEN 'S'
        WHEN 4 THEN 'U'
        WHEN 5 THEN 'X'
        WHEN 6 THEN 'IS'
        WHEN 7 THEN 'IU'
        WHEN 8 THEN 'IX'
        WHEN 9 THEN 'SIU'
        WHEN 10 THEN 'SIX'
        WHEN 11 THEN 'UIX'
        WHEN 12 THEN 'BU'
        WHEN 13 THEN 'RangeS_S'
        WHEN 14 THEN 'RangeS_U'
        WHEN 15 THEN 'RangeI_N'
        WHEN 16 THEN 'RangeI_S'
        WHEN 17 THEN 'RangeI_U'
        WHEN 18 THEN 'RangeI_X'
        WHEN 19 THEN 'RangeX_S'
        WHEN 20 THEN 'RangeX_U'
        WHEN 21 THEN 'RangeX_X' END AS req_mode
    , CASE req_status WHEN 1 THEN 'GRANTED'
        WHEN 2 THEN 'CONVERTING'
        WHEN 3 THEN 'WAITING' END req_status
    , req_refcnt
    , req_cryrefcnt
    , req_lifetime
    , req_spid
    , req_ecid
    , CASE req_ownertype WHEN 1 THEN 'TRANSACTION'
        WHEN 2 THEN 'CURSOR'
        WHEN 3 THEN 'SESSION'
        WHEN 4 THEN 'EXSESSION' END AS req_ownertype
    , req_transactionID
    , req_transactionUOW
FROM sys.syslockinfo;

Query via sys.dm_tran_locks

Creating a query, provided in Listing 2, with sys.dm_tran_locks that can replace syslockinfo presents a rather complicated endeavor.  To start, except for the columns rsc_text, rsc_bin, rsc_flag, req_cryrefcnt, and rsc_blk, the mapping between the views is just a matter of renaming columns.  For the columns rsc_flag and req_cryrefcnt, the only values returned now are 0’s.  This leave rsc_text, rsc_bin, and rsc_blk to define.  Starting with rsc_blk, since the only observed value returned is 0×00000000000000000000000000000000, the query will return that value.  Then there is rsc_text.  For this column depending on the lock type, the values returned for this column are most often either in the resource_subtype or resource_description. The logic works well for key, page, database, and metadata locks.  The pattern defining other variations was not evident.  The last column to define the value for is rsc_bin.  This column presents the largest set of issues.  For the most part, the query returns fairly accurate values for database, page, key, rid, and hobt locks.  But there are some discrepancies.  Since the rsc_bin value is mostly for outside application use, I opted to call the calculation “good enough’”.  If you need help digging in more or have more information, please leave a comment below.

--Listing 2 – Query for sys.dm_tran_locks
WITH TranLocks AS (
SELECT COALESCE(CASE 
            WHEN resource_type = 'DATABASE' THEN NULLIF(QUOTENAME(resource_subtype),'[]')
            WHEN resource_type IN ('KEY','PAGE','METADATA') THEN resource_description
        END, '') rsc_text
    ,CONVERT(VARBINARY(4),
        CASE WHEN resource_type = 'OBJECT' THEN tl.resource_associated_entity_id
        WHEN resource_type IN ('PAGE','KEY','RID','HOBT') THEN COALESCE(p.object_id,0)
        WHEN resource_type = 'ALLOCATION_UNIT' THEN 0
        ELSE 0
        END,1) AS HashValue
    ,CONVERT(VARBINARY(6),
        CASE WHEN resource_type = 'KEY'
        THEN '0x'+REPLACE(REPLACE(resource_description,'(',''),')','')
        ELSE '0x000000000000'
        END,1) AS HashObject
    ,CASE resource_type
            WHEN 'DATABASE' THEN 2
            WHEN 'FILE' THEN 3
            WHEN 'INDEX' THEN 4
            WHEN 'OBJECT' THEN 5
            WHEN 'PAGE' THEN 6
            WHEN 'KEY' THEN 7
            WHEN 'EXTENT' THEN 8
            WHEN 'RID' THEN 9
            WHEN 'APPLICATION' THEN 10
            WHEN 'METADATA' THEN 11
            WHEN 'HOBT' THEN 12
            WHEN 'ALLOCATION_UNIT' THEN 13
            ELSE 1 END AS resource_type_id
    ,CASE WHEN resource_type = 'OBJECT' THEN 0
        WHEN resource_type IN ('PAGE','KEY','RID','HOBT') THEN COALESCE(p.index_id,0)
        WHEN resource_type = 'ALLOCATION_UNIT' THEN 0
        ELSE 0
        END AS rsc_indid
    ,CASE WHEN resource_type = 'OBJECT' THEN tl.resource_associated_entity_id
        WHEN resource_type IN ('PAGE','KEY','RID','HOBT') THEN COALESCE(p.object_id,0)
        WHEN resource_type = 'ALLOCATION_UNIT' THEN 0
        ELSE 0
        END AS rsc_objid
    ,tl.resource_type
    ,tl.resource_subtype
    ,tl.resource_database_id
    ,tl.resource_description
    ,tl.resource_associated_entity_id
    ,tl.resource_lock_partition
    ,tl.request_mode
    ,tl.request_type
    ,tl.request_status
    ,tl.request_reference_count
    ,tl.request_lifetime
    ,tl.request_session_id
    ,tl.request_exec_context_id
    ,tl.request_request_id
    ,tl.request_owner_type
    ,tl.request_owner_id
    ,tl.request_owner_guid
    ,tl.request_owner_lockspace_id
    ,tl.lock_owner_address
FROM sys.dm_tran_locks tl
    LEFT OUTER JOIN sys.partitions p 
        ON resource_type IN ('PAGE','KEY','RID','HOBT')
        AND tl.resource_associated_entity_id = p.hobt_id
    LEFT OUTER JOIN sys.allocation_units au
        ON resource_type = 'ALLOCATION_UNIT'
        AND tl.resource_associated_entity_id = au.allocation_unit_id
        )
SELECT rsc_text
    ,SUBSTRING(HashValue,4,1)
        + SUBSTRING(HashValue,3,1)
        + SUBSTRING(HashValue,2,1)
        + SUBSTRING(HashValue,1,1)
        + SUBSTRING(CONVERT(varbinary(2), rsc_indid),2,1)
        + SUBSTRING(CONVERT(varbinary(2), rsc_indid),1,1)
        + HashObject
        + SUBSTRING(CONVERT(varbinary(2), resource_database_id),2,1)
        + SUBSTRING(CONVERT(varbinary(2), resource_database_id),1,1)
        + SUBSTRING(CONVERT(varbinary(2), resource_type_id),2,1)
        + SUBSTRING(CONVERT(varbinary(2), resource_type_id),1,1)
            AS rsc_bin_Flag
    ,0x00000000000000000000000000000000 AS rsc_valblk
    ,resource_database_id AS rsc_dbid
    ,rsc_indid
    ,rsc_objid
    ,resource_type AS rsc_type
    ,0 AS rsc_flag
    ,request_mode AS req_mode
    ,request_status AS req_status
    ,request_reference_count AS req_refcnt
    ,0 AS req_cryrefcnt --internal only
    ,request_lifetime AS req_lifetime
    ,request_session_id AS req_spid
    ,request_exec_context_id req_ecid
    ,request_owner_type req_ownertype
    ,request_owner_id AS req_transactionID
    ,request_owner_guid AS req_transactionUOW
    ,resource_subtype
    ,resource_description
    ,resource_associated_entity_id
    ,resource_lock_partition
    ,request_type
    ,request_lifetime
    ,request_session_id
    ,request_exec_context_id
    ,request_request_id
    ,request_owner_lockspace_id
    ,lock_owner_address
FROM TranLocks

One of the additional benefits of using sys.dm_tran_locks is the inclusion of a number of additional columns.  These columns, such as resource_subtype, resource_description, and resource_associated_entity_id, provide additional descriptive information about locks that was previously locked in the complex columns in syslockinfo; which meant it was often difficult to decipher.   Outside of those columns, there are other columns that help assist in linking the lock to execution contexts, requests, and sessions within the SQL Server instance.  These along with a few other columns make the locking information provided by sys.dm_tran_locks far superior and more accessible than from syslockinfo.

Summary

In this post, we compared the compatibility view syslockinfo with the dynamic management view sys.dm_tran_locks.  In some cases, transitioning between the two views could be a challenge, but only if using the complex columns in the compatibility view.  If not, the transition just requires a few column name changes and the inclusion of new locking information that gives great information on locks.  After reading all of this, do you see any reason to continue using syslockinfo?  Is there anything missing from this post that people continuing to use the compatibility view should know?