No results visible with the %%lockres%% function

Question

We are encountering deadlocks in our system and the deadlock graph shows this format of wait resources.

waitresource="KEY: 500:xxxxxxxxxx (f4d477997e11) waitresource="KEY: 500:xxxxxxxxxx (8d4830b45673)"

Now both the victim and the winner have the same HOBTID and are pointing to the same object - In our case a non clustered index on a table.Only the hash value is different and so I'm guessing its different rows.

When I use the

select *,%%lockres%%
from   dbo.myTabke 
where    %%lockres%% IN('(f4d477997e11)','(8d4830b45673)')

Now this yields no results.

Also my index itself which is my deadlock wait resource has columns in this order -

test_NonClustIndex(colA, col B, colC) The statements in my sessions(both updates and selects on the same table) have predicates like

WHERE colA = @a and colB = @b and colC = @c.

Now for both the winner and the victim sessions, @a and @b is the same value. Only @c is different.

Will I be able to avoid the deadlock if I flip the order of index to test_NonClustIndex(colC, colA, col B)?

There are other indexes on the table that are don't show up as a wait resource on the deadlock graph.

score 0 · Answer 1 · answered Aug 18 '18 at 13:44

Regarding not finding the %%lockres%% value, you'll need to specify an index hint for the non-clustered index so that the hash of the non-clustered key is returned instead of the clustered key:

--clustered key hashes
SELECT *,%%lockres%%
FROM dbo.myTabke
WHERE %%lockres%% IN('(f4d477997e11)','(8d4830b45673)');

--non-clustered key hashes
SELECT *,%%lockres%%
FROM dbo.myTabke WITH(INDEX=test_NonClustIndex)
WHERE %%lockres%% IN('(f4d477997e11)','(8d4830b45673)');

I can't say whether changing the non-clustered index key order will help void the deadlock without additional details but I wouldn't expect that to be the solution. Add the deadlock xml to your question.

No results visible with the %%lockres%% function

1 Answers1