LCK_M_SIX Wait Type Explained

The lock manager grants SIX locks when a transaction needs to read most of a table but modify only specific rows. The SIX lock combines shared access (allowing other readers) with intent exclusive capability (preventing other transactions from acquiring conflicting locks). SQL Server's lock manager escalates from row and page locks to SIX table locks when lock memory pressure occurs or when a single transaction acquires more than 5,000 locks on a single table.

Lock escalation triggers vary by SQL Server version. SQL Server 2016 introduced more granular escalation thresholds, while 2019 improved lock memory management under memory pressure. SQL Server 2022 enhanced the lock manager's ability to detect escalation scenarios earlier, reducing unnecessary SIX lock waits.

The wait manifests when multiple sessions attempt concurrent operations on the same table. Session A holds a SIX lock, which is most commonly acquired as the result of lock escalation from fine-grained row/page locks up to an object-level lock, or through specific internal operations. It is not acquired directly by ordinary UPDATE statements, which use U locks (converted to X) combined with IX intent locks at higher granularity. Session B then waits for its own SIX lock or for a conflicting lock type like exclusive (X) or schema modification (Sch-M). The lock compatibility matrix shows SIX locks are only compatible with Intent Shared (IS) locks. SIX conflicts with S, U, IU, IX, SIU, SIX, UIX, BU, X, and Sch-M locks.

Buffer pool pressure exacerbates SIX waits because dirty pages cannot be flushed while locks are held, forcing longer lock duration. The scheduler quantum expiration during lock waits moves waiting tasks to the waitable list, where they remain until the lock manager signals availability.

-- Current SIX lock waits and blocking chains
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time_ms,
    r.blocking_session_id,
    t.text AS sql_text,
    db.name AS database_name,
    o.name AS object_name
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
LEFT JOIN sys.dm_tran_locks l ON r.session_id = l.request_session_id
LEFT JOIN sys.databases db ON l.resource_database_id = db.database_id  
LEFT JOIN sys.objects o ON l.resource_associated_entity_id = o.object_id
WHERE r.wait_type = 'LCK_M_SIX'
ORDER BY r.wait_time_ms DESC;

-- Lock escalation events and SIX lock patterns
SELECT 
    l.resource_type,
    l.resource_database_id,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    s.session_id,
    s.login_name,
    s.program_name,
    COUNT(*) AS lock_count
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE l.request_mode = 'SIX' 
   OR (l.request_mode IN ('IX', 'X') AND l.resource_type = 'OBJECT')
GROUP BY l.resource_type, l.resource_database_id, l.resource_associated_entity_id, 
         l.request_mode, l.request_status, s.session_id, s.login_name, s.program_name
ORDER BY lock_count DESC;

-- Historical SIX wait statistics  
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LCK_M_SIX'
   AND waiting_tasks_count > 0;

-- Tables with disabled lock escalation that might benefit from SIX optimization
SELECT 
    t.name AS table_name,
    t.lock_escalation_desc,
    i.name AS index_name,
    ps.row_count,
    ps.reserved_page_count * 8 AS reserved_kb
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id  
JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE t.lock_escalation = 0  -- DISABLE
   OR t.lock_escalation = 1  -- AUTO but large tables
   AND ps.row_count > 100000
ORDER BY ps.reserved_page_count DESC;

-- Active transactions holding SIX locks with duration
SELECT 
    s.session_id,
    t.transaction_id,
    t.transaction_begin_time,
    DATEDIFF(second, t.transaction_begin_time, GETDATE()) AS duration_seconds,
    l.resource_database_id,
    l.resource_associated_entity_id,
    l.request_mode,
    s.last_request_start_time,
    st.text AS current_sql
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions ts ON t.transaction_id = ts.transaction_id
JOIN sys.dm_exec_sessions s ON ts.session_id = s.session_id
JOIN sys.dm_tran_locks l ON s.session_id = l.request_session_id
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st
WHERE l.request_mode = 'SIX'
ORDER BY duration_seconds DESC;

Disable lock escalation on problematic tables This prevents row/page locks from escalating to SIX table locks, keeping granularity finer but using more lock memory.

-- Identify and disable lock escalation on specific tables
-- TEST IN DEV: Monitor lock memory usage after implementation
DECLARE @sql NVARCHAR(MAX);
DECLARE @table_name SYSNAME = 'YourTableName'; -- Replace with actual table

SET @sql = 'ALTER TABLE ' + QUOTENAME(@table_name) + ' SET (LOCK_ESCALATION = DISABLE)';
PRINT @sql;
EXEC sp_executesql @sql;

-- Verify change
SELECT name, lock_escalation_desc 
FROM sys.tables 
WHERE name = @table_name;

Enable READ_COMMITTED_SNAPSHOT to reduce blocking Reduces SIX lock contention by allowing readers to use row versioning instead of shared locks.

-- Enable RCSI to reduce lock contention
-- CAUTION: Requires exclusive database access and increases tempdb usage
USE master;
GO
DECLARE @db_name SYSNAME = 'YourDatabase'; -- Replace with actual database
DECLARE @sql NVARCHAR(MAX);

-- Check current setting first
SELECT name, is_read_committed_snapshot_on 
FROM sys.databases 
WHERE name = @db_name;

-- Enable RCSI (requires single-user mode temporarily)
SET @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) + ' SET READ_COMMITTED_SNAPSHOT ON';
PRINT 'Execute when no active connections: ' + @sql;

Optimize transaction patterns to reduce lock duration Break large transactions into smaller batches to minimize SIX lock holding time.

-- Template for batch processing to reduce lock escalation
-- Replace with your actual UPDATE/DELETE logic
DECLARE @batch_size INT = 5000;
DECLARE @rows_affected INT = 1;

WHILE @rows_affected > 0
BEGIN
    BEGIN TRAN;
    
    UPDATE TOP (@batch_size) YourTable 
    SET YourColumn = @new_value
    WHERE YourCondition = @condition
      AND YourColumn != @new_value; -- Avoid unnecessary updates
    
    SET @rows_affected = @@ROWCOUNT;
    COMMIT TRAN;
    
    -- Brief pause to allow other operations
    IF @rows_affected > 0 WAITFOR DELAY '00:00:01';
END;

Add appropriate indexes to reduce lock scope Create covering indexes to minimize rows examined and reduce SIX lock escalation triggers.

-- Analyze missing indexes that could reduce lock escalation
-- Focus on WHERE clause columns and UPDATE target columns
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_' + 
    REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') + 
    CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' + 
         REPLACE(REPLACE(REPLACE(mid.inequality_columns,', ','_'),'[',''),']','') 
         ELSE '' END +
    ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,'') + 
    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' 
         ELSE '' END + ISNULL(mid.inequality_columns, '') + ')' + 
    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 100
ORDER BY improvement_measure DESC;

Configure appropriate lock escalation thresholds based on table usage patterns. Tables with frequent large batch operations should have lock escalation disabled, while OLTP tables benefit from default AUTO escalation. Monitor lock memory usage through sys.dm_os_memory_clerks to ensure adequate allocation.

Implement READ_COMMITTED_SNAPSHOT isolation at the database level to reduce shared lock contention that contributes to SIX lock waits. This shifts blocking scenarios to tempdb row versioning, significantly reducing lock-based waits in mixed read/write workloads.

Design transactions to minimize lock holding time. Break large batch operations into smaller chunks using TOP clauses or cursor-based processing. Avoid interactive transactions that hold locks while waiting for user input. Process modifications in primary key order to reduce deadlock potential.

Create covering indexes for frequent UPDATE and DELETE operations to minimize lock escalation triggers. The optimizer requires fewer row locks when indexes eliminate key lookups, reducing the likelihood of hitting the 5,000 lock threshold that triggers table-level SIX locks.

Establish monitoring for lock escalation events using Extended Events or the Lock:Escalation performance counter. Set alerts when SIX wait times exceed baseline thresholds, typically 1000ms for OLTP systems or 5000ms for batch processing systems. Track lock memory clerks to identify memory pressure scenarios that force premature escalation.