I've spent the better part of two days trying to figure out what exactly is being locked in one of my tables and why I'm getting deadlocks that seem to make no sense at face value.
I've found numerous blogs explaining how to use the undocumented %%lockres%% function to get the hash for a specific row in a table. But each of these guides only give the example where the lock in question is on the primary key for the table. I have a strange situation where there is a lock on the primary key and a unique key as well.
For context: My primary key is a clustered index on a UUID string. The only other index in this table is a composite unique key on two columns (not including the pk). When I do an INSERT into this table, I can see in
sys.dm_tran_locks that there are two
X KEY locks on this table: one for the pk and one for the unique constraint.
My deadlock report seems to imply (unless I'm reading it wrong -- you can see my other question here) that the deadlock is caused by a second query also locking on the unique index.
I've been experimenting on a different database using the same schema to try and figure out how I can determine if the cause of the deadlock is avoidable or not. I did an INSERT in an open transaction and compared the resource description to the %%lockres%% of all records in the locked table, only to find out that the lock on the primary key maps to the row that I added, but the lock on the unique index does not match anything in the table at all.
Does anybody here know what %%lockres%% is for this unique index? It clearly isn't a specific record in my table.
For context, here is the queries I ran to see this information:
This query lists the locks on my current database. Output below.
SELECT dm_tran_locks.request_session_id, dm_tran_locks.resource_database_id, CASE WHEN resource_type = 'object' THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id) ELSE OBJECT_NAME(partitions.OBJECT_ID) END AS ObjectName, partitions.index_id, indexes.name AS index_name, dm_tran_locks.resource_type, dm_tran_locks.resource_description, dm_tran_locks.resource_associated_entity_id, dm_tran_locks.request_mode, dm_tran_locks.request_status, cleanlockrs FROM sys.dm_tran_locks LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id CROSS APPLY( SELECT LEFT(SUBSTRING(resource_description, 2, LEN(resource_description)), LEN(resource_description) - 2) ) clean(cleanlockrs) WHERE resource_associated_entity_id > 0 AND resource_database_id = DB_ID() and resource_type = 'KEY' ORDER BY request_session_id, resource_associated_entity_id
When I then execute the following query, I only get back a result for the resource description for the first index.
-- This returns the row I just added. select * from entities where %%lockres%% like '%27f49aa9c0ac%' -- This does not return anything. select * from entities where %%lockres%% like '%7e24236fccb8%'