LCK_M_IS

A transaction is waiting to acquire an intent shared lock. When granted at one level in the database hierarchy, this lock protects acquired or requested shared locks on certain objects at lower levels. For example, when placed at the table level, it indicates that a transaction intends to place a shared lock on pages or rows in that table. This  prevents other transactions from modifying data, but allow multiple concurrent read (SELECT) operations. 

The SQL Server Database Engine uses locking to manage the way multiple users access the same data at the same time. Every transaction must first request a lock on the resource it wants to modify. This lock stops other transactions from modifying the same resource in a way that might cause conflict. The lock is only released once the transaction no longer needs it. For more information, including details of lock type compatibility, see SQL Server Transaction Locking and Row Versioning Guide (TechNet).

Lock waits commonly occur on busy servers where concurrent transactions demand the same resource, resulting in poor performance. A high number of locking waits may indicate blocking problems and should be investigated.

Investigating

  • Check the affected queries and the Top 10 queries table. Tune queries so they run faster and require fewer locks. See Query tuning (TechNet).

  • On the Analysis page, check these metrics for additional details on locking behavior:

    • Lock timeouts/sec

    • Lock timeouts/sec

    • Avg. lock wait time

  • On the Analysis page, check these metrics to see whether memory problems or I/O bottlenecks are causing locks to be held for longer than usual:

    • Machine: memory used

    • Memory pages/sec

    • Disk avg. read time

    • Disk avg. write time

    • Buffer cache hit ratio

    • Buffer page life expectancy

See also Investigating I/O bottlenecks.



Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?