Deadlock on single Table, when DELETE

  • Hi SQL Devs,

    Description ablut table.

    Table name is FACILITY_FEE (Child table of FACILITY) has 2 NON-Cluster indexs on facility_fee_id (PK) and facility_id (fk).

    And, Resource list is showing that, there is RIDLock with lock combination X-S = X-S, but with different PageIDs.

    ridlock fileid=1 pageid=91609 dbid=10 objectname=FACILITY_FEE id=lock4f0ca00 mode=X associatedObjectId=72057594043367424

    owner-list

    owner id= process3c12ef8 mode=X

    waiter-list

    waiter id= process7808478 mode=S requestType=wait

    ridlock fileid=1 pageid=20307 dbid=10 objectname=FACILITY_FEE id=lock7b19e80 mode=X associatedObjectId=72057594043367424

    owner-list

    owner id= process7808478 mode=X

    waiter-list

    waiter id= process3c12ef8 mode=S requestType=wait

    Note that, Delete SP dletefacility table has 2 row Facility (1 for current, and 1 for old version. Both are related with source_facility_id).

    Attached are the Script to generate Test data. Also Stored procedures are attached.

    As per my understanding, this deadlock was due to operating both transactions on same data. Also not having a cluster index could be a reason.

    Can any one explain the technical reasons behind this deadlock?

    I would appriciate any help to resolve this.

    Thank You,

    Pradeep.

  • I'm honestly not sure why this is happening.

    Is there any reason you have no clustered index? If there's no good reason, maybe make the pk clustered, see if it helps.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You might also consider saving your Word docs as a Word 97-2003 compatible doc because you cut out a lot of people who don't have Word 2007 from helping you. Like, me. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Actually, I'd suggest either saving the xml deadlock graph in a text file or posting the deadlock graph directly into your post, rather than giving us what looks like your company's deadlock problem and resolution document.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Even better.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Gail Shaw,

    There is no reason for not having a CLUSTER index. I can do it.

    But I want to know, the technical reason behind this deadlock, and how to reproduce this?

  • Jeff Moden,

    Sorry for inconvenience. I have posted deadlock log file in .TXT format.

  • ypradeep (1/17/2010)


    But I want to know, the technical reason behind this deadlock, and how to reproduce this?

    As I said, I'm honestly not sure why this is happening.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi ,

    Try to use tablock in delete Statement. it may work.:w00t:

    Regards,
    Saravanan

  • Saravanan T (1/18/2010)


    Hi ,

    Try to use tablock in delete Statement. it may work.:w00t:

    It'll prevent the deadlock sure. It'll also prevent anyone else from doing anything at all to the table for the duration of the delete.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply