Learning KEYLOCK in SQL Server 2005

Question

I tried to solve the various dead ends that we see in production. We have enabled deadlock tracking. Traces show many locks on KEYLOCKs as follows:

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, a lock lock locks an index to prevent insertion, updating, or deleting of records when a transaction performs its own insertions, updates, and deletes.

I assume that there are bad query plans that request bad locks. I can run the same queries on my development system and run sp_lock to check the locks of the requested query, and I see some KEYLOCK in the list. How to explore the range of keys that KEYLOCK has blocked?

Answer 1

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

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

Use this query to find out locked rows:

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

where string is the value of the blocked resource from sp_lock .

If this query returns two rows, you encounter a hash collision, which is very improbable, but possible, with a probability increases with the size of the table (due to the birth paradox).

Answer 2

This link allegedly explains how to do this. It's a little longer, so I'm not going to cut and paste it here, but when I have the opportunity to check it and destroy it, I will post a review so that it is saved forever in SO.

Message from the forum that the row is locked.