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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy