LCK_M_S Wait Type Explained

The SQL Server Lock Manager generates LCK_M_S waits when a session requests a shared (S) lock on a resource but encounters a blocking exclusive (X) lock. The lock hierarchy determines which resource triggers the wait: row (RID), key, page, extent, table (TAB), or database (DB).

When a session executes a SELECT statement under READ COMMITTED isolation, it requests shared locks on data being read. If another session holds an exclusive lock from an UPDATE, INSERT, or DELETE operation, the reading session enters LCK_M_S wait state. The Lock Manager places the waiting session in a FIFO queue for that specific resource.

SQL Server 2016 introduced Query Store deadlock detection improvements that better surface these blocking chains. SQL Server 2019 added intelligent query processing features that can sometimes avoid certain locking scenarios through batch mode processing. SQL Server 2022's Parameter Sensitive Plan optimization can reduce lock duration by choosing more efficient execution plans.

The wait specifically occurs in the sqlos scheduler when the session yield occurs during lock acquisition. The Buffer Pool Manager may also be involved if the locked pages need to be read from disk, creating compound waits.

Lock escalation thresholds (5,000 locks per object or 40% of memory) can transform granular RID/KEY locks into table-level locks, potentially increasing LCK_M_S duration as more sessions compete for fewer, broader locks.

-- Identify current blocking chains causing LCK_M_S waits
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time_ms,
    r.blocking_session_id,
    t.text AS blocking_sql,
    r.wait_resource
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_S'
ORDER BY r.wait_time_ms DESC;

-- Analyze lock resource contention patterns
SELECT 
    resource_type,
    resource_database_id,
    resource_associated_entity_id,
    request_mode,
    request_status,
    COUNT(*) as lock_count
FROM sys.dm_tran_locks
WHERE request_mode = 'S' OR request_status = 'WAIT'
GROUP BY resource_type, resource_database_id, resource_associated_entity_id, request_mode, request_status
ORDER BY lock_count DESC;

-- Historical LCK_M_S 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 = 'LCK_M_S'
    AND waiting_tasks_count > 0;

-- Identify long-running transactions holding exclusive locks
SELECT 
    s.session_id,
    t.transaction_id,
    t.transaction_begin_time,
    DATEDIFF(ms, t.transaction_begin_time, GETDATE()) AS duration_ms,
    s.reads,
    s.writes,
    st.text AS sql_text
FROM sys.dm_tran_active_transactions t
INNER JOIN sys.dm_tran_session_transactions ts ON t.transaction_id = ts.transaction_id
INNER JOIN sys.dm_exec_sessions s ON ts.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) st
WHERE t.transaction_type = 1 -- User transaction
ORDER BY duration_ms DESC;

-- Lock escalation events that may increase LCK_M_S waits
SELECT 
    database_id,
    object_id,
    lock_escalation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL)
WHERE leaf_allocation_count > 0 
    AND lock_escalation_count > 0
ORDER BY lock_escalation_count DESC;

Kill blocking session (emergency only) This terminates the session holding exclusive locks that block shared lock requests. Use only when the blocking session is stuck or runaway.

-- Replace @blocking_spid with actual session ID from diagnostic queries
DECLARE @blocking_spid INT = 0; -- Set this value
DECLARE @sql NVARCHAR(50);

-- Verify the session exists and is blocking others
IF EXISTS (SELECT 1 FROM sys.dm_exec_sessions WHERE session_id = @blocking_spid)
BEGIN
    SET @sql = 'KILL ' + CAST(@blocking_spid AS VARCHAR(10));
    PRINT 'Executing: ' + @sql;
    EXEC sp_executesql @sql;
END
ELSE
    PRINT 'Session not found or already terminated';

Enable Read Committed Snapshot Isolation Reduces shared lock contention by using row versioning instead of shared locks for read operations.

-- Enable RCSI to reduce shared lock blocking
-- WARNING: Test thoroughly - increases tempdb usage and affects application behavior
DECLARE @db_name NVARCHAR(128) = DB_NAME();
DECLARE @sql NVARCHAR(MAX);

-- Check current RCSI status
SELECT 
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
FROM sys.databases 
WHERE name = @db_name;

-- Enable RCSI (requires exclusive database access)
SET @sql = 'ALTER DATABASE [' + @db_name + '] SET READ_COMMITTED_SNAPSHOT ON';
PRINT 'Execute when database has no active connections:';
PRINT @sql;

Adjust lock escalation thresholds Prevents lock escalation that can increase blocking scope for shared locks.

-- Disable lock escalation on frequently contended tables
-- Increases memory usage but reduces blocking scope
SELECT 
    SCHEMA_NAME(schema_id) AS schema_name,
    name AS table_name,
    lock_escalation_desc
FROM sys.tables 
WHERE lock_escalation = 0; -- AUTO

-- Example: Disable escalation on specific table
-- ALTER TABLE Sales.OrderDetails SET (LOCK_ESCALATION = DISABLE);
PRINT 'Identify target tables from blocking analysis, then disable escalation selectively';

Optimize blocking queries Reduces exclusive lock duration by adding missing indexes or rewriting inefficient queries.

-- Generate index recommendations for frequently blocking queries
SELECT DISTINCT
    mid.database_id,
    mid.object_id,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
    AND migs.avg_user_impact > 50
ORDER BY migs.avg_user_impact DESC;

Configure appropriate transaction isolation levels for application workloads. Use READ COMMITTED SNAPSHOT ISOLATION for OLTP systems with heavy concurrent read/write patterns. This eliminates most shared lock waits by using row versioning instead of blocking.

Implement proper indexing strategies to minimize lock duration. Covering indexes reduce key lookups that extend lock hold times. Partition large tables to isolate lock contention to specific partitions.

Monitor transaction duration and implement connection pooling with shorter connection lifetimes. Long-running transactions holding exclusive locks are the primary cause of LCK_M_S waits. Set appropriate lock timeout values (SET LOCK_TIMEOUT) for applications that can handle lock timeout exceptions.

Configure lock escalation appropriately using ALTER TABLE SET (LOCK_ESCALATION = DISABLE) for tables with high concurrent access patterns. Balance this against memory usage for lock structures.

Use Query Store to identify and optimize queries with high average duration that correlate with LCK_M_S wait spikes. Implement query hints like NOLOCK for read-only reporting queries where dirty reads are acceptable, or use Always On readable secondaries to offload reporting workloads.

Set up Extended Events sessions to capture lock escalation and blocking events for proactive monitoring:

CREATE EVENT SESSION [LCK_M_S_Monitoring] ON SERVER 
ADD EVENT sqlserver.lock_escalation,
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file(SET filename=N'C:\Logs\LCK_M_S_Events.xel')
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);