In the 4th article of the Wait Statistics series we will dive into LCK_M_xx waits. This is another wait type you are sure to see in your environment!

LCK_M_xx waits

As you can guess the LCK_M_xx waits have a relation with locking, they are generated when a query wants to place a lock on an object but there already is an incompatible lock on that object.

(check out http://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx for the lock compatibility matrix)

There are a couple of different LCK_M_xx wait types, these are the most common ones:

• LCK_M_S, occurs when we are waiting to place a Shared Lock
• LCK_M_U, occurs when we are waiting to place a Update Lock
• LCK_M_X, occurs when we are waiting to place an Exclusive lock

There are also the IS, IU and IX types, these are used for the locking hierarchy by SQL Server to indicate a lock is being held on an object lower in the hierarchy.

A good example is a query that updates some rows in a table. For that update Exclusive locks will be placed on the rows. If another query wants to select those rows then a Shared lock need to be placed on the rows. Since a Shared lock is not compatible with an Exclusive lock, a LCK_M_S wait will occur until our query manages to get a Shared lock on the rows it’s requesting.

I executed some queries against the AdventureWorks2012 database to show this behavior.

In my first query windows I executed the following query:

USE AdventureWorks2012
GO
BEGIN TRAN
UPDATE Sales.SalesOrderDetail
SET CarrierTrackingNumber = '4E0A-4F89-AD'
WHERE SalesOrderID = '43661'

I didn’t execute a COMMIT TRAN because I want the transaction (and the locks) to remain open.

Now open up another query windows a run this query:

SELECT * FROM AdventureWorks2012.Sales.SalesOrderDetail

You shouldn’t get any results because we are trying to place a Shared lock but can’t since there already is an Exclusive lock on the rows we are requesting.

If we would take a look at our waiting tasks you should see something like this:

SELECT
session_id,
wait_duration_ms,
wait_type,
blocking_session_id,
resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id > 50

The session that is running my SELECT query is 55 and as you can see we are waiting to place a Shared lock (LCK_M_S wait type). We are being blocked by session ID 54, which is the session that is running the UPDATE query in the first window.

If we COMMIT or ROLLBACK the UPDATE query in session 54 our SELECT query will be free to place the Shared lock we are waiting for and the wait will go away.

Uses for LCK_M_xx waits

LCK_M_xx waits can help you out with concurrency problems as they will indicate blocking. Using this information we can use the sys.dm_os_waiting_tasks DMF to detect blocking occurring and find the queries that are causing the blocking, we can use the “blocking_session_id” column to find the head blocker.

Reducing LCK_M_xx waits

Reducing LCK_M_xx waits can be a challenge since they are directly connected to the locking and blocking mechanism in SQL Server. If you can reducing blocking you will also lower your LCK_M_xx waits.

The performance of your SQL Server can play a large role in your blocking behavior. The longer it takes to run a query the longer locks will be held.

Also the isolation mode of your database will have impact on LCK_M_xx waits, using Snapshot isolation can reduce the number of blocking that is occurring.