deciphering deadlock resource list

  • Hi can somebody help me decipher the following resource list from a deadlock graph.

    inputbuf

    Proc [Database Id = 5 Object Id = 1213715872]

    resource-list

    pagelock fileid=1 pageid=539 dbid=5 objectname=databaseA_Live.dbo.TIMESHEET_HEADER id=lock30fdc4c00 mode=SIX associatedObjectId=72057602758672384

    owner-list

    owner id=process190617708 mode=SIX

    waiter-list

    waiter id=process4c87288 mode=S requestType=wait

    pagelock fileid=1 pageid=539 dbid=5 objectname=databaseA_Live.dbo.TIMESHEET_HEADER id=lock30fdc4c00 mode=SIX associatedObjectId=72057602758672384

    owner-list

    waiter-list

    waiter id=process443d048 mode=S requestType=wait

    pagelock fileid=1 pageid=72924 dbid=5 objectname=DatabaseA_Live.dbo.TIMESHEET_LINE id=lock310155980 mode=IX associatedObjectId=72057602759000064

    owner-list

    owner id=process443d048 mode=IX

    waiter-list

    waiter id=process190617708 mode=S requestType=wait

  • This may be of use

    https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

    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
  • Thanks Gail. If i break it down can you advise if mu summary is correct ? I need to know if I reading the sections that refer to each other correctly

    inputbuf

    Proc [Database Id = 5 Object Id = 1213715872]

    resource-list

    pagelock fileid=1 pageid=539 dbid=5 objectname=databaseA_Live.dbo.TIMESHEET_HEADER id=lock30fdc4c00 mode=SIX associatedObjectId=72057602758672384

    owner-list

    owner id=process190617708 mode=SIX

    waiter-list

    waiter id=process4c87288 mode=S requestType=wait

    pagelock fileid=1 pageid=539 dbid=5 objectname=databaseA_Live.dbo.TIMESHEET_HEADER id=lock30fdc4c00 mode=SIX associatedObjectId=72057602758672384

    So this first section tells me that Process 08 had a SIX Page lock on pageid539 dbid 5 and that from the waiter list I can see Process 88 had requested an S lock on the same object ?

    if the above is correct - can you talk me through the below section as I am having trouble applying the same logic to it as I did above.

    owner-list

    waiter-list

    waiter id=process443d048 mode=S requestType=wait

    pagelock fileid=1 pageid=72924 dbid=5 objectname=DatabaseA_Live.dbo.TIMESHEET_LINE id=lock310155980 mode=IX associatedObjectId=72057602759000064

    owner-list

    owner id=process443d048 mode=IX

    waiter-list

    waiter id=process190617708 mode=S requestType=wait

  • No, you're breaking it up wrong (because the indentation's been lost)

    Resource 1:

    pagelock fileid=1 pageid=539 dbid=5 objectname=databaseA_Live.dbo.TIMESHEET_HEADER id=lock30fdc4c00 mode=SIX associatedObjectId=72057602758672384

    owner-list

    owner id=process190617708 mode=SIX

    waiter-list

    waiter id=process4c87288 mode=S requestType=wait

    Resource 2 (which is actually the exact same page as resource 1, why it's there twice I'm not sure.):

    pagelock fileid=1 pageid=539 dbid=5 objectname=databaseA_Live.dbo.TIMESHEET_HEADER id=lock30fdc4c00 mode=SIX associatedObjectId=72057602758672384

    owner-list

    waiter-list

    waiter id=process443d048 mode=S requestType=wait

    Resource 3:

    pagelock fileid=1 pageid=72924 dbid=5 objectname=DatabaseA_Live.dbo.TIMESHEET_LINE id=lock310155980 mode=IX associatedObjectId=72057602759000064

    owner-list

    owner id=process443d048 mode=IX

    waiter-list

    waiter id=process190617708 mode=S requestType=wait

    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
  • ah ha ! Thanks very much Gail - I am now able to at least draw a graphical plan on paper of what was happening - got it thanks a lot :-):-)

Viewing 5 posts - 1 through 5 (of 5 total)

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