SQL Server Performance

Deadlock involving a keylock on index

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, Feb 11, 2010.

  1. satya.sqldba New Member

    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
  2. arunyadav Member

    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/
  3. satya.sqldba New Member

    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.
  4. preethi Member

    Thank you for pointing the resource.

Share This Page