Just Code‎ > ‎

TSQL - Show all the rows in all tables that are being locked right now

posted Feb 13, 2013, 7:56 AM by Peter Henell   [ updated Feb 13, 2013, 7:57 AM ]
Using sys,dm_tran_locks we can get information about which resources that are being locked. 
If we want to select those rows that are locked we need to figure out which object they belong to and then find them using %%lockres%%.

Snippet: Get all rows that are being locked by KEY in the current database


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


DECLARE @sql VARCHAR(MAX) = '';

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL 
    DROP TABLE #tmp

-- Collect information of what object the locks belong to
-- http://stackoverflow.com/questions/7820907/how-to-find-out-what-table-a-page-lock-belongs-to
SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status,
       cleanlockrs
INTO #tmp
FROM sys.dm_tran_locks 
LEFT JOIN sys.partitions  ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
JOIN sys.indexes  ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
CROSS APPLY(
    SELECT LEFT(SUBSTRING(resource_description, 2, LEN(resource_description)), LEN(resource_description) - 2)
) clean(cleanlockrs)
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
 ORDER BY request_session_id, resource_associated_entity_id 



SELECT 
    @sql = @sql +
    'SELECT cols.*, #tmp.* FROM #tmp CROSS APPLY(SELECT '''+cleanlockrs+''',* FROM '+ObjectName+' WHERE %%lockres%% like ''%'+cleanlockrs+'%'') cols(lockRs,'+colList+') WHERE cleanlockrs = lockRs;' 
FROM 
    (
        SELECT DISTINCT cleanlockrs, ObjectName, resource_type FROM #tmp  t
    ) t(cleanlockrs, objectName, resource_type)
CROSS APPLY(
    -- Get Column Names comma separated
    -- http://stackoverflow.com/questions/4936634/comma-separated-list-of-all-columns-in-the-database-tablename-column-names
    SELECT  STUFF(
        (
        SELECT ', ' + C.COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS AS C
        WHERE 
            C.TABLE_NAME = t.ObjectName
        ORDER BY C.ORDINAL_POSITION
        FOR XML PATH('')
        ), 1, 2, '') AS Columns
) cols(colList)

WHERE 
    resource_type = 'KEY'


EXEC (@sql)
Comments