LCK_M_IS

Description

This wait occurs when a request is waiting to acquire an intent shared lock. These locks are required to protect the object or data from other requests. For example when a query is run on a table an Intent Shared lock is placed on the table and database itself. This allows other requests to read the table but requests to modify the table or database (e.g. rename) will be blocked. This wait typically occurs while requests are being blocked by another write transaction (implicit or explicit) that has been kept open for an extended period of time

Resolved by

DBAs and Developers

Suggested solutions

  1. Keep transaction durations short
  2. Ensure transactions isolation levels are appropriate (avoid SERIALIZEABLE and REPEATABLE READ if possible)
  3. Investigate enabling the READ_COMMITTED_SNAPSHOT database setting
  4. Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked
  5. Indexing, ensure that long running queries and transactions are optimized
  6. Ensure that row, page and table locks are being escalated correctly. If the table is partitioned consider enabling partition escalation

Additional research

Lock Modes
Reducing SQL Server Locks
Understanding Isolation Levels
Identify the Cause of SQL Server Blocking
Lock Escalations

See more

Consider using ApexSQL Monitor, a Windows and SQL Server monitoring tool able to track SQL Server wait types, including the individual query wait statistics

⇐ Back to index