LCK_M_S Wait

The LCK_M_S wait type in SQL Server indicates that a session is waiting to acquire a shared lock on a resource, but it’s being blocked by another process holding an incompatible lock, usually an exclusive (X) or update (U) lock.

Understanding Locks in SQL Server

SQL Server uses different types of locks to manage concurrency and ensure data integrity during transactions. A shared lock (S) is acquired when a session needs to read data, allowing other sessions to also read but preventing writes. Conflicts arise when another session holds a more restrictive lock, like an exclusive lock, which prevents both reading and writing by other processes.

Causes of LCK_M_S Wait

The LCK_M_S wait type occurs when:

  1. Exclusive locks are held by other sessions, usually because of:
  • Long-running transactions that have modified data and haven’t yet committed or rolled back.
  • Table or row-level locking during data modifications (e.g., UPDATE, DELETE).
  1. Blocked by an Update Lock:
  • Another session might have taken an update lock (U) as part of a query that is transitioning to an exclusive lock, preventing a shared lock.

Common Scenarios

  • Heavy writes while reads are occurring: If you have a mix of read and write workloads, shared locks are often blocked by exclusive locks. For instance, an update or delete operation holding an exclusive lock can block select queries waiting for shared locks.
  • Long-running transactions: Transactions that take a long time to complete, especially if they involve data modifications, can hold exclusive locks for longer periods, causing LCK_M_S waits for reading queries.

How to Resolve LCK_M_S Waits

  • Optimize Queries: Look for inefficient queries that hold locks longer than necessary. Consider optimizing them for faster execution.
  • Shorten Transaction Durations: Minimize the duration of transactions, especially those modifying data. Commit or roll back as soon as possible to release locks.
  • Use Appropriate Isolation Levels: Consider less restrictive isolation levels like Read Committed Snapshot Isolation (RCSI), which can reduce locking contention by allowing readers to access a version of the data without waiting for locks.
  • Indexing: Ensure that appropriate indexes exist so that queries can be executed efficiently without holding locks for longer periods.

Monitoring LCK_M_S Wait Type

You can monitor LCK_M_S waits using tools like Database Health Monitor or via SQL Server’s Dynamic Management Views (DMVs), such as:

SELECT 
    session_id,
    wait_type,
    blocking_session_id,
    wait_duration_ms,
    resource_description
FROM sys.dm_exec_requests
WHERE wait_type = 'LCK_M_S';

If you’re experiencing frequent or prolonged LCK_M_S waits, it’s a good indication that there’s contention between reading and writing queries in your system, and it might be time to tune your workload.

For more in-depth analysis and tuning, Stedman Solutions’ Managed Services can help by reviewing your locking strategy and overall SQL Server performance to prevent bottlenecks like these.

For more insights, check out Databasehealth.com for more!