Examining KEYLOCKs in SQL Server 2005

Question

I've been trying to solve various deadlocks we are seeing in production. We've enabled deadlock tracing. The traces show lots of blocking on KEYLOCKs like this:

01/15/2010 08:25:07,spid15s,Unknown,keylock hobtid=72057594047758336 dbid=2
objectname=tempdb.dbo.MyTable indexname=IX_MyTable id=lock36977900 mode=X
associatedObjectId=72057594047758336

From what I understand, the keylock locks the index to prevent records from being inserted, updated, or deleted while the transaction is doing its own inserts, updates, and deletes.

I'm guessing there are bad query plans causing bad locks to be requested. I can run the same queries on my develoment system and run sp_lock to examine the locks the query required, and I see some KEYLOCKs in the list. How does one examine the range of keys the KEYLOCK was locking?

Answer 1

This link supposedly explains how to do it. It's a bit long, so I'm not going to cut and paste it here, but when I have a chance to test it and whittle it down I'll post an overview so that it's saved forever on SO.

Forum post on finding the row being key locked

Answer 2

For KEY locks, the value of resource in sp_lock is a hashed value of the key being locked.

The first 2 bytes are the lower 2 bytes of the key value, the other bytes are the hash or the value.

Use this query to find out the rows being locked:

SELECT  *
FROM    mytable
WHERE   %%LOCKRES%% = '{0000ABCDEFAB}'

, where the string is the value of the locked resource from sp_lock.

If this query returns two rows then you have encountered a hash collision which is quite improbable but possible, with probability rising as the table size grows (due to the birthday paradox).