January 18, 2011 at 6:01 am
Firstly, can I say that I don't want any more deadlocks, I have more than enough, thank you.
Secondly, would like to say thanks to Geoff Albin for an article on this site, I now have deadlock info emailed to an account for further investigation. This includes the deadlock graph info which is relatively helpful once you can understand it. and that's where I am having trouble.
According to an article on support.microsoft.com the important details are in the line "PAG: 7:1:1738555".
PAG I'm guessing from reading between the lines is a Page Lock.
7 is identified as the DBID which cross references through sp_helpdb to a database called 'Reservations' which is what I expected.
1 is then identified as the ObjectId which in this case is the sysobjects table, didn't expect this although I can accept it.
The 'biggie' is the last reference which is supposed to be an IndId. Seems a bit big for me. All the indid values on the server seem to be in the range 0-255. Just for the record, the two nodes in the deadlock graph are both in the form '7:1:xxxxxxx' as are other deadlocks but the final number has never been replicated, it is always different, almost like a Row identifier or something like that.
The Event in both nodes is sp_executesql which indicates that it is deadlocking on itself (which also doesn't surprise me), I just don't know how to go from here because of the unusual figures in the Lock information string.
Have included the deadlock graph in case I have missed something.
January 18, 2011 at 6:18 am
SequelSurfer (1/18/2011)
1 is then identified as the ObjectId which in this case is the sysobjects table, didn't expect this although I can accept it.The 'biggie' is the last reference which is supposed to be an IndId. Seems a bit big for me. All the indid values on the server seem to be in the range 0-255.
Nope.
The set of numbers that identify a page are DatabaseID:FileID:PageNumber. No object_id, no index_id.
To get the object that the page belongs to, you'll need an undocumented command.
DBCC TRACEON(3604)
DBCC PAGE(7,1,1738555)
DBCC TRACEOFF(3604)
That will print out the header of the page in question. Among all the info posted there you'll see objectid and indexid. Those identify what object and index this page belongs to. You can then use that to query sysobjects and sysindexes for the names.
The Event in both nodes is sp_executesql which indicates that it is deadlocking on itself (which also doesn't surprise me), I just don't know how to go from here because of the unusual figures in the Lock information string.
It won't be sp_executesql that's deadlocking. It'll be whatever dynamic SQL it's running that's causing the deadlocks. Unfortunately, to fix the deadlock we need to see what that dynamic SQL is.
Does your system use stored procedures or ad-hoc SQL from the front end?
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
January 18, 2011 at 6:58 am
Thanks for the prompt reply Gail.
Even bigger thanks for the undocumented Command. That has helped identify the table and index that are at the root of the problem. Now to start digging into the stored procedures, fortunately in an organised direction.
If I am unable to get to a resolution I will post back on here but really appreciate your assistance so far.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply