I’ve just read a very good, very deep, and very interesting blog post by James Rowland-Jones. In the post, James investigates some locking issues using a variety of means and explains about the undocumented %%lockres%% function with you can use to figure out what the wait resource will be for individual table rows (basically the lock-hash value). He also shows something I’ve known about but never seen before – how the lock hash algorithm isn’t perfect and can actually cause lock collisions where you wouldn’t expect them – and how to mitigate the problem.
Excellent post and well worth reading. Check it out at The Curious Case of the Dubious Deadlock and the Not So Logical Lock.