December 10, 2014 at 8:53 am
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
December 10, 2014 at 9:07 am
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
December 10, 2014 at 9:34 am
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
December 10, 2014 at 2:17 pm
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
December 10, 2014 at 4:13 pm
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