XACTLOCKINFO Wait Type Explained

The lock manager maintains a per-transaction lock list that tracks all locks held by each active transaction. When the deadlock monitor scans for cycles, lock escalation evaluates threshold violations, or page splits require lock migration, SQL Server must synchronize access to these lock lists using internal latches.

The XACTLOCKINFO wait occurs specifically when threads contend for the transaction lock list latch. This internal structure uses a lightweight synchronization primitive to prevent corruption when multiple operations simultaneously access the same transaction's lock inventory. The deadlock monitor (running every 5 seconds by default) is the most common accessor, but lock escalation threshold checks and B-tree page split operations also trigger this contention.

SQL Server 2016 introduced optimizations to the lock manager's hash table structure that reduced some XACTLOCKINFO contention. SQL Server 2019 further optimized deadlock detection algorithms to reduce the frequency of lock list traversals. In SQL Server 2022, the lock manager gained additional improvements for highly concurrent workloads, though the fundamental XACTLOCKINFO mechanism remains unchanged.

High XACTLOCKINFO waits typically correlate with one of three scenarios: applications running many small transactions with overlapping lock footprints, frequent deadlocks triggering aggressive detection cycles, or workloads that consistently hit lock escalation thresholds during large data modifications.

-- Check current XACTLOCKINFO wait statistics
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'XACTLOCKINFO'
    AND waiting_tasks_count > 0;

-- Identify sessions with high lock counts that may contribute to contention
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    tl.resource_type,
    COUNT(*) as lock_count
FROM sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
WHERE s.is_user_process = 1
GROUP BY s.session_id, s.login_name, s.program_name, tl.resource_type
HAVING COUNT(*) > 1000
ORDER BY lock_count DESC;

-- Check deadlock frequency which drives lock list scanning
SELECT 
    cntr_value as deadlocks_per_sec
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Number of Deadlocks/sec' 
    AND instance_name = '_Total';

-- Monitor lock escalations that require lock list manipulation
SELECT 
    cntr_value as lock_escalations_per_sec
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Lock Escalations/sec' 
    AND instance_name = '_Total';

-- Find long-running transactions with large lock footprints
SELECT 
    s.session_id,
    t.transaction_begin_time,
    DATEDIFF(second, t.transaction_begin_time, GETDATE()) as duration_seconds,
    COUNT(tl.lock_owner_address) as lock_count,
    s.transaction_isolation_level
FROM sys.dm_tran_active_transactions t
INNER JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_locks tl ON t.transaction_id = tl.request_owner_id
WHERE t.transaction_begin_time < DATEADD(minute, -5, GETDATE())
GROUP BY s.session_id, t.transaction_begin_time, s.transaction_isolation_level
HAVING COUNT(tl.lock_owner_address) > 500
ORDER BY duration_seconds DESC;
-- Note: transaction_isolation_level is taken from sys.dm_exec_sessions,
-- since sys.dm_tran_active_transactions does not expose isolation level.

Enable lock escalation threshold tuning

-- Adjust lock escalation threshold to reduce lock list size
-- Apply to specific high-contention tables
ALTER TABLE [YourHighVolumeTable] 
SET (LOCK_ESCALATION = TABLE);  -- Force table-level escalation

-- For partitioned tables, consider partition-level escalation
ALTER TABLE [YourPartitionedTable] 
SET (LOCK_ESCALATION = AUTO);   -- Default behavior

Forces earlier escalation to reduce per-transaction lock counts, but may increase blocking.

Implement read committed snapshot isolation

-- Enable RCSI to reduce shared lock contention
-- TEST THOROUGHLY: Changes application behavior
ALTER DATABASE [YourDatabase] 
SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE [YourDatabase] 
SET READ_COMMITTED_SNAPSHOT ON;

Eliminates shared locks for read operations, reducing lock list sizes and XACTLOCKINFO contention.

Configure optimal MAXDOP for reduced lock contention

-- Set MAXDOP based on NUMA topology to reduce cross-processor lock contention
-- Calculate as: min(8, logical_processors_per_NUMA_node)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 4;  -- Adjust based on hardware
RECONFIGURE;

Reduces parallel query complexity that can amplify lock list contention patterns.

Implement shorter transaction patterns by breaking large operations into smaller batches using techniques like DELETE TOP(1000) loops rather than single large deletes. Configure applications to use READ_COMMITTED_SNAPSHOT isolation where read consistency without blocking is acceptable.

Monitor lock escalation thresholds on high-volume tables and manually trigger escalation through table hints (TABLOCK) for known large operations. Set appropriate LOCK_ESCALATION settings on tables based on access patterns, using TABLE level for append-only scenarios and AUTO for mixed workloads.

Establish deadlock monitoring using extended events to identify root causes rather than relying solely on frequent detection cycles. Create filtered indexes on foreign key columns to reduce lock range sizes during referential integrity checks.

Configure MAXDOP settings aligned with NUMA topology to prevent cross-node lock contention. Implement connection pooling with appropriate pool sizes to prevent excessive concurrent transactions that amplify lock list contention.

Use trace flag 1224 to disable lock escalation entirely only in specific scenarios where lock memory consumption is not a concern and XACTLOCKINFO waits are severe.