sys.dm_tran_locks - Usage & Examples

SQL Server's lock manager maintains an in-memory hash table of all active locks, which sys.dm_tran_locks directly exposes. Each lock entry contains the resource type (RID, KEY, PAGE, OBJECT, DATABASE), lock mode (S, X, U, IS, IX, SIX), and owning transaction details. The lock manager allocates approximately 96 bytes per lock from the buffer pool's general memory space.

When transactions begin, SQL Server acquires locks based on isolation level requirements and query patterns. Under READ COMMITTED isolation, shared locks are acquired on read operations and released as soon as the row has been read — they are not held for the duration of the transaction. UPDATE operations acquire update locks that convert to exclusive locks during modification. To explicitly request update locks in T-SQL, use the table hint SELECT ... WITH (UPDLOCK); SQL Server does not support the Oracle/PostgreSQL SELECT ... FOR UPDATE syntax.

SQL Server's default lock escalation threshold remains at approximately 5,000 locks per object and has not changed in SQL Server 2016 or later releases. SQL Server 2019 enhanced lock memory tracking within Resource Governor workload groups, while SQL Server 2022 improved lock partitioning for highly concurrent OLTP workloads.

The lock manager integrates directly with the transaction log manager, ensuring locks persist until transaction completion or rollback. Lock compatibility matrices determine which lock types can coexist on the same resource, with incompatible locks forming blocking chains visible through request_status = 'WAIT' entries.

Intent locks (IS, IX, SIX) provide hierarchical locking efficiency, allowing the lock manager to determine resource availability without scanning lower-level locks. Schema modification (Sch-M) and schema stability (Sch-S) locks protect metadata operations, often causing blocking when DDL operations conflict with DML workloads.

-- Current blocking chains with lock details
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    l.resource_type,
    l.resource_database_id,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    r.wait_time,
    r.wait_type
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
INNER JOIN sys.dm_exec_requests blocked ON r.session_id = blocked.session_id
INNER JOIN sys.dm_exec_requests blocking ON r.blocking_session_id = blocking.session_id
WHERE l.request_status = 'WAIT';

-- Lock counts by session and resource type
SELECT 
    request_session_id,
    resource_type,
    request_mode,
    COUNT(*) as lock_count,
    SUM(CASE WHEN request_status = 'WAIT' THEN 1 ELSE 0 END) as waiting_locks
FROM sys.dm_tran_locks
WHERE request_session_id > 50
GROUP BY request_session_id, resource_type, request_mode
HAVING COUNT(*) > 100
ORDER BY lock_count DESC;

-- Expensive transactions holding many locks
SELECT 
    l.request_session_id,
    COUNT(DISTINCT l.resource_associated_entity_id) as objects_locked,
    COUNT(*) as total_locks,
    t.transaction_begin_time,
    DATEDIFF(second, t.transaction_begin_time, GETDATE()) as transaction_duration_sec,
    s.program_name,
    s.login_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_tran_session_transactions st ON l.request_session_id = st.session_id
INNER JOIN sys.dm_tran_active_transactions t ON st.transaction_id = t.transaction_id
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE l.request_session_id > 50
GROUP BY l.request_session_id, t.transaction_begin_time, s.program_name, s.login_name
HAVING COUNT(*) > 1000
ORDER BY total_locks DESC;

-- Lock escalation candidates and memory consumption
SELECT 
    resource_database_id,
    DB_NAME(resource_database_id) as database_name,
    resource_associated_entity_id,
    OBJECT_NAME(resource_associated_entity_id, resource_database_id) as object_name,
    resource_type,
    COUNT(*) as lock_count,
    COUNT(*) * 96 as estimated_memory_bytes
FROM sys.dm_tran_locks
WHERE resource_type IN ('RID', 'KEY', 'PAGE')
    AND resource_database_id > 4
GROUP BY resource_database_id, resource_associated_entity_id, resource_type
HAVING COUNT(*) > 1000
ORDER BY lock_count DESC;

-- Schema locks blocking DML operations
SELECT 
    l.request_session_id,
    l.resource_type,
    l.resource_subtype,
    l.request_mode,
    l.request_status,
    DB_NAME(l.resource_database_id) as database_name,
    OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) as object_name,
    s.program_name,
    r.command,
    r.wait_type
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
WHERE l.request_mode IN ('Sch-S', 'Sch-M')
    AND l.resource_type = 'OBJECT'
ORDER BY l.request_session_id;

Kill blocking sessions holding excessive locks

-- Identify and kill sessions with over 10,000 locks older than 5 minutes
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'KILL ' + CAST(l.request_session_id AS VARCHAR(10)) + '; '
FROM (
    SELECT 
        l.request_session_id,
        COUNT(*) as lock_count,
        MAX(DATEDIFF(second, t.transaction_begin_time, GETDATE())) as max_duration
    FROM sys.dm_tran_locks l
    INNER JOIN sys.dm_tran_session_transactions st ON l.request_session_id = st.session_id
    INNER JOIN sys.dm_tran_active_transactions t ON st.transaction_id = t.transaction_id
    WHERE l.request_session_id > 50
    GROUP BY l.request_session_id
    HAVING COUNT(*) > 10000 AND MAX(DATEDIFF(second, t.transaction_begin_time, GETDATE())) > 300
) l;

PRINT @sql; -- Review before executing
-- EXEC sp_executesql @sql; -- Uncomment to execute

Test this carefully in non-production first. Killing active sessions causes rollbacks and potential data loss for uncommitted work.

Force lock escalation on heavily locked tables

-- Trigger lock escalation for tables with excessive row/page locks
DECLARE @database_id INT = DB_ID();
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 
    'ALTER TABLE ' + QUOTENAME(DB_NAME(@database_id)) + '.' + 
    QUOTENAME(OBJECT_SCHEMA_NAME(resource_associated_entity_id, @database_id)) + '.' +
    QUOTENAME(OBJECT_NAME(resource_associated_entity_id, @database_id)) + 
    ' SET (LOCK_ESCALATION = TABLE); '
FROM (
    SELECT 
        resource_associated_entity_id,
        COUNT(*) as lock_count
    FROM sys.dm_tran_locks
    WHERE resource_database_id = @database_id
        AND resource_type IN ('RID', 'KEY', 'PAGE')
        AND resource_associated_entity_id > 0
    GROUP BY resource_associated_entity_id
    HAVING COUNT(*) > 5000
) locks;

PRINT @sql;
-- EXEC sp_executesql @sql; -- Forces immediate lock escalation

Lock escalation reduces memory consumption but may increase blocking. Monitor application behavior after implementation.

Clear lock waits by setting deadlock priority

-- Lower deadlock priority for sessions with excessive waiting locks
DECLARE @sessions TABLE (session_id INT);

INSERT INTO @sessions
SELECT DISTINCT request_session_id
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT'
GROUP BY request_session_id
HAVING COUNT(*) > 50;

DECLARE @session_id INT;
DECLARE session_cursor CURSOR FOR SELECT session_id FROM @sessions;

OPEN session_cursor;
FETCH NEXT FROM session_cursor INTO @session_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(200) = 'KILL ' + CAST(@session_id AS VARCHAR(10));
    PRINT 'Session ' + CAST(@session_id AS VARCHAR(10)) + ' has excessive waiting locks';
    -- Uncomment next line to execute kills
    -- EXEC sp_executesql @sql;
    
    FETCH NEXT FROM session_cursor INTO @session_id;
END;

CLOSE session_cursor;
DEALLOCATE session_cursor;

Review each session's activity before terminating. Some waiting may be legitimate for long-running analytical queries.

Configure appropriate isolation levels application-wide to minimize lock duration. READ COMMITTED SNAPSHOT eliminates most shared lock blocking while maintaining consistency. Enable this at the database level using ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON during maintenance windows.

Set LOCK_TIMEOUT values in application connection strings to prevent indefinite blocking. Values between 5,000-30,000 milliseconds work well for OLTP workloads. Monitor sys.dm_os_wait_stats for LCK_* wait types to establish baselines.

Implement query optimization focused on index coverage and seek operations rather than scans. Large table scans acquire thousands of shared locks even with READ COMMITTED isolation. CREATE NONCLUSTERED indexes on frequently filtered columns to reduce lock footprints.

Configure lock escalation thresholds per table based on access patterns. High-concurrency tables benefit from LOCK_ESCALATION = DISABLE to prevent exclusive table locks, while batch processing tables should use LOCK_ESCALATION = TABLE to reduce memory consumption.

Monitor lock memory consumption through sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SOSNODE' and establish alerts when lock memory exceeds 5% of buffer pool. SQL Server 2019+ provides better lock memory tracking through Resource Governor.

Establish application-level transaction boundaries to minimize transaction duration. Long-running transactions hold locks until commit or rollback, creating blocking cascades. Implement savepoints for complex multi-statement transactions to reduce rollback overhead.

Configure query timeout settings in application frameworks to prevent abandoned connections holding locks indefinitely. Connection pooling should include aggressive timeout settings for idle connections with active transactions.