LCK_M_U Wait Type Explained

The lock manager grants Update locks to sessions that intend to read data before modifying it, preventing the classic deadlock scenario where two readers both try to escalate to exclusive locks. When a session requests an Update lock, it must wait if another session holds a Shared, Update, or Exclusive lock on the same resource.

Update locks are unique because they're compatible with Shared locks but not with other Update or Exclusive locks. This asymmetry creates the LCK_M_U wait when multiple sessions compete for Update locks on the same page, key, or table. The lock manager queues these requests in FIFO order within the lock hash table.

SQL Server 2016 introduced live query statistics which can reveal lock waits in real-time. SQL Server 2019's intelligent query processing can sometimes reduce these waits by changing execution plans to avoid lock escalation. SQL Server 2022's parameter sensitive plan optimization may create different execution paths that alter locking patterns.

The wait specifically manifests in the scheduler when a worker thread cannot proceed because the lock request cannot be granted. The thread moves from RUNNING to SUSPENDED state and registers the LCK_M_U wait until the conflicting lock releases or times out.

-- Find current blocking chains involving Update locks
SELECT 
    blocking_session_id,
    session_id,
    wait_type,
    wait_time_ms,
    resource_description,
    database_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
  AND wait_type = 'LCK_M_U'
ORDER BY wait_time_ms DESC;

-- Identify sessions holding Update locks and what they're waiting for
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    r.command,
    r.status,
    tl.resource_type,
    tl.resource_description,
    tl.request_mode,
    tl.request_status
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE tl.request_mode = 'U' OR tl.request_status = 'WAIT';

-- Show statements causing Update lock waits with execution plans
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time_ms,
    SUBSTRING(qt.text, r.statement_start_offset/2 + 1,
        (CASE WHEN r.statement_end_offset = -1 
         THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
         ELSE r.statement_end_offset END - r.statement_start_offset)/2) AS statement_text,
    qp.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.wait_type = 'LCK_M_U';

-- Historical analysis of Update lock waits from Query Store
SELECT TOP 20
    qst.query_text_id,
    qt.query_sql_text,
    ws.total_query_wait_time_ms,
    ws.avg_query_wait_time_ms,
    ws.execution_count,
    p.plan_id
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_runtime_stats rs ON ws.plan_id = rs.plan_id
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE ws.wait_category = 6 -- Locking waits
ORDER BY ws.total_query_wait_time_ms DESC;

Force plan recompilation to potentially change locking strategy

-- Clear plan cache for specific problematic queries
-- WARNING: Test in development first, this removes cached plans
DECLARE @sql_handle VARBINARY(64);
SELECT @sql_handle = sql_handle 
FROM sys.dm_exec_requests 
WHERE session_id = 54; -- Replace with actual blocking session

DBCC FREEPROCCACHE(@sql_handle);

This forces the query optimizer to generate a new execution plan, potentially choosing different indexes or join orders that reduce Update lock contention.

Terminate long-running sessions causing Update lock chains

-- Kill sessions that have been holding Update locks beyond threshold
DECLARE @session_id INT;
DECLARE kill_cursor CURSOR FOR
    SELECT DISTINCT tl.request_session_id
    FROM sys.dm_tran_locks tl
    JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
    WHERE tl.request_mode = 'U'
      AND DATEDIFF(MINUTE, s.last_request_start_time, GETDATE()) > 30
      AND s.session_id > 50; -- Exclude system sessions

OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @session_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Killing session: ' + CAST(@session_id AS VARCHAR(10));
    -- KILL @session_id; -- Uncomment after testing
    FETCH NEXT FROM kill_cursor INTO @session_id;
END;
CLOSE kill_cursor;
DEALLOCATE kill_cursor;

Use extreme caution. This terminates active sessions and will roll back their transactions.

Lower isolation level to reduce Update lock duration

-- Change problematic queries to use READ COMMITTED SNAPSHOT
ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Or explicitly use NOLOCK hints for read operations (data consistency trade-off)
-- Example modification for SELECT statements that later UPDATE:
/*
Original: SELECT * FROM Orders WHERE Status = 'Pending';
Modified: SELECT * FROM Orders WITH (READUNCOMMITTED) WHERE Status = 'Pending';
*/

READ_COMMITTED_SNAPSHOT eliminates most Update locks by using row versioning instead of blocking. Test thoroughly as this changes concurrency behavior database-wide.

Enable READ_COMMITTED_SNAPSHOT isolation at the database level to replace Update locks with row versioning for most scenarios. This eliminates the majority of LCK_M_U waits while maintaining data consistency.

Redesign batch operations to process smaller row sets with explicit transaction boundaries. Large MERGE statements and cursor operations should be broken into chunks of 1000-5000 rows to reduce lock duration.

Monitor lock escalation thresholds and consider enabling lock escalation to table level for bulk operations, but disable it for OLTP workloads. Set appropriate lock timeout values using SET LOCK_TIMEOUT to prevent indefinite waits.

Implement proper indexing strategies to ensure Update locks occur at the row level rather than page level. Missing indexes force table scans that acquire page-level Update locks affecting unrelated rows.

Configure Query Store to capture wait statistics and establish baselines for Update lock waits. Set up Extended Events sessions to track lock escalation events and identify patterns in Update lock contention.