I have a situation where a deadlock has occured because of keylocks being placed. I am trying to understand how keylocks work and how to resolve this. Deadlock graph has the below info: 1. SPID 217 is an adhoc update statement on a table 'Zip' and holds an X lock on index PK_ZIP. It now requests for an X lock on index IDX_Zip. 2. SPID 4929 is a select statement with rowlock and updlock hints, has an U lock on index IDX_Zip and requests for a U lock on index PK_Zip. I am trying to understand how the locks on indexes work. How do I make sure the above deadlock does not occur once again? Thanks Satya
Hello Satya, I believe taking a look at the below links might help you.. http://www.sql-server-performance.com/articles/per/advanced_sql_locking_p1.aspx http://support.microsoft.com/kb/323630 http://www.sqlservercentral.com/
Hi Arun Thanks for your input, turns out the problem I was having is explained below: http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx Creating a covered index will solve my problem.