July 27, 2010 at 2:19 am
Getting a lot of deadlocks recently (starting maybe a month back) and this seems to be the most perplexing to me.
Two inserts into the same history table, whose transactions start about 5 seconds apart from each other deadlocked. This isn't a one off fluke, maybe 25% of our deadlocks.
some basic stats (From SQL Sentry Console):
Mode: both are exclusive lock on the table for the insert
Status: Both are suspended
Isolation Level: ReadCommited (2)
Wait Time: The loser has a wait time of 1953
I see whats happening, but I don't see why they're not just queuing up like a good table would do. I do a lot more to other tables (deletes, updates, inserts, reads) and they'll get slow, but not deadlock.
The only thing that's really looking any fishy is the readcommited(2) because we use READ_COMMITTED_SNAPSHOT. I understand it acts like 2 in many ways, but this table doesn't update or delete, just insert and read. The READ_COMMITTED_SNAPSHOT should handle the read part. Those should be going to the snapshot and not causing any locking issues with the table.
If anyone has some ideas on whats causing this, I would love to hear. Any suggestions for tracking this down would also be a great help. I'm looking at two sql products for monitoring a server right now (Redgate and SQL Sentry) and I'm not entirely sold on one, though I'm leaning towards Sentry at the moment.
July 27, 2010 at 2:27 am
STherrien-978820 (7/27/2010)
Two inserts into the same history table, whose transactions start about 5 seconds apart from each other deadlocked.
separation is being done with some kind of Sql job ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 27, 2010 at 2:39 am
These inserts are generated off of the after update/insert trigger on a table. Basically they run through a before after comparison and creates the history record with this information.
It IS a decent bit of processing, but comment out the insert and I can abuse the trigger with a barrage of updates from many connections and no deadlocks, it just queues up and powers through it.
Add in that insert and the inserts are tripping over each other.
July 27, 2010 at 4:30 am
Enable traceflag 1222. That will make the SQL engine write a deadlock graph into the error log every time a deadlock occurs. Post the deadlock graph and definitions of all objects that appear in there to this thread.
DBCC TRACEON (1222,-1)
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
July 27, 2010 at 3:11 pm
I'm not sure how to obtain such a graph. In SQL Response the data from the graph is displayed, some of it as a graphical representation as well.
But we found the issue. Both inserts were going for an Intent Exclusive, and SQL Server wasn't handing it out properly. We changed the insert to add With (TablockX) which just had the effect of making one insert just grab an Exclusive Lock and the others just waited for that Exclusive Lock.
So now thats working. I'm not sure why this change happened, but I'm going to open up a new thread for that topic.
July 27, 2010 at 3:16 pm
STherrien-978820 (7/27/2010)
I'm not sure how to obtain such a graph.
Enable traceflag 1222. That will make the SQL engine write a deadlock graph into the error log every time a deadlock occurs. Open the error log, copy the text deadlock graph out and post it here.
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
July 27, 2010 at 3:24 pm
Hi,
I'm with SQL Sentry and noticed your post. SQL Sentry provides a complete graphical representation of all deadlocks with a grid display listing all nodes organized by resource, owners and waiters, and includes the full SQL for all nodes from the Deadlocks tab in Performance Advisor. Additionally you can double click on any deadlock you see in the Event Manager calendar view to jump right to this information. All of this is provided without any need to change any trace flags. Feel free to contact us at support with any questions.
Thanks,
Steve
July 27, 2010 at 3:24 pm
I'm having trouble getting this from the error log. It seems to be a wall of text, we were having 20+ per hour and each is multiple lines.
Here is what Redgates SQL Response parses out from the error log. I hope it has all of the information:
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
OBJECT: 5:124683642:11 CleanCnt:3 Mode:IX Flags: 0x0
Grant List 2:
Owner:0x00000002241DDC80 Mode: IX Flg:0x0 Ref:28 Life:02000000 SPID:66 ECID:0 XactLockInfo: 0x00000002BA0243A8
SPID: 66 ECID: 0 Statement Type: INSERT Line #: 191
Input Buf: Language Event: (@LeaveDate smalldatetime,@Status char(2),@ID int)SET NOCOUNT OFF UPDATE [Edimain].[ems].[Student] SET [LeaveDate] = @LeaveDate, [Status] = @status WHERE ([ID] = @ID)
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x0000000857FBA370 Mode: X SPID:145 BatchID:2 ECID:0 TaskProxy:(0x0000000840146598) Value:0xe2b181c0 Cost:(0/5188)
Node:2
OBJECT: 5:124683642:0 CleanCnt:2 Mode:X Flags: 0x0
Grant List 2:
Owner:0x00000003E3AA5140 Mode: X Flg:0x0 Ref:0 Life:20000000 SPID:145 ECID:0 XactLockInfo: 0x0000000857FBA3A8
SPID: 145 ECID: 0 Statement Type: INSERT Line #: 191
Input Buf: Language Event: (@id int output,@ClientId int,@ClientStudentId int,@StudentId int,@CourseDescription varchar(9),@Credits decimal(5,2),@GradeLevel tinyint,@Semester int,@SchoolName varchar(31),@AttemptedCredits decimal(5,2),@Grade int,@IsOfficial bit)SET NOCOUNT OFF INSER
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x00000002BA024370 Mode: X SPID:66 BatchID:2 ECID:0 TaskProxy:(0x000000082994A598) Value:0xe3276140 Cost:(0/1052)
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x00000002BA024370 Mode: X SPID:66 BatchID:2 ECID:0 TaskProxy:(0x000000082994A598) Value:0xe3276140 Cost:(0/1052)
July 27, 2010 at 3:33 pm
That looks like traceflag 1204's output. Limited and hard to read.
Switch traceflag 1204 off, switch 1222 on. The deadlock graph will have two sections, where it shows the processes involved and where it shows the resources involved. If you past too much it's not a problem. Each deadlock graph can be a full page of text.
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
July 27, 2010 at 3:34 pm
Steve Wright-233748 (7/27/2010)
Hi,I'm with SQL Sentry and noticed your post. SQL Sentry provides a complete graphical representation of all deadlocks with a grid display listing all nodes organized by resource, owners and waiters, and includes the full SQL for all nodes from the Deadlocks tab in Performance Advisor. Additionally you can double click on any deadlock you see in the Event Manager calendar view to jump right to this information. All of this is provided without any need to change any trace flags. Feel free to contact us at support with any questions.
Thanks,
Steve
Yes, SQL Sentry did provide us with the way to track down what was causing the deadlocks and whats locking what and waiting for what and who got killed to let the other through. I've found some partial SQL, but not Full SQL as you mention. The partial got me not only the code that caused the issue, but what called that code.
I see it's showing me all the graph information, but I'm not sure how to see it in a text fashion to just post up here, so I used the other tool I'm evaluating from Redgate, since that plain text seems to be about all it shows you in this case.
August 4, 2010 at 5:55 am
This sounds a lot like another thread some time ago. In that case the problem was caused by having ALLOW_ROW_LOCKS=OFF in one of the indexes in the affected table.
See http://www.sqlservercentral.com/Forums/FindPost944539.aspx
August 4, 2010 at 11:48 am
See here and the related 2 blog posts for some goodness on deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply