March 31, 2017 at 4:07 am
Hi All,
I’m wondering why one of the processes (4025498) involved in a deadlock has taken an IX lock on object XXX.dbo.RM10101?
The process 4025498 was running a SELECT statement on a different table (XXX.dbo.RM00401) via the stored procedure XXX.dbo.zDP_RM00401SS_1 but this doesn’t touch XXX.dbo.RM10101 so I’m wondering why there's a IX lock being held on this object by process 4025498? Could this be because process 4025498 was part of a larger transaction that had locks on RM10101 that hadn't committed?
I’ve attached the information on the deadlock that was output into the error log and the stored procedures involved. (please note these are from the third party application Dynamics GP)
Many thanks for taking the time to read this and for any assistance.
Gavin
March 31, 2017 at 9:06 am
Yep. Larger transaction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2017 at 2:52 am
Hi Grant,
Many thanks for taking the time to read and reply to my question.
What would be the best way to troubleshoot this? Is it just a question of setting up a trace to get exactly what's happening or is there a better way?
April 3, 2017 at 3:06 am
Gavin Whittaker - Monday, April 3, 2017 2:52 AMIs it just a question of setting up a trace to get exactly what's happening?
In general, yes.
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
April 3, 2017 at 4:06 am
Gavin Whittaker - Monday, April 3, 2017 2:52 AMHi Grant,
Many thanks for taking the time to read and reply to my question.
What would be the best way to troubleshoot this? Is it just a question of setting up a trace to get exactly what's happening or is there a better way?
Yes. Although on a 2012 server I'd recommend using extended events. They have a lower overhead and lot more flexibility than trace.
You need to know the full set of queries within the transaction. Most of the time what's happening in a deadlock situation is you have two batches, let's call 'em BatchA & BatchB. BatchA does something like this:
Begin Tran
Query TablA WHERE Value=42
Update TableB WHERE Value=41
Commit Tran
BatchB does something like this:
Begin Tran
Query TableB WHERE Value=41
Update TableA WHERE value =42
Commit tran
That's radically abbreviated and most of the time production is much more complex. However, the concept is clear. If these run completely independently and finish very fast, they'll never see each other. However, let's say, just for the example, that both tables have poor indexing, so retrieving & updating these values takes a long time. Now we're sure that BatchA & BatchB are going to run into each other. One of them has an exclusive lock on TableA and needs TableB. The other has an exclusive lock on TableB and needs TableA. Who wins? We'll sit here forever unless something is done. This is a deadlock. SQL Server will choose the plan with the lowest estimated cost and make it a deadlock victim and rollback the transaction. The error is going to be about whatever query was hung, not specifically on what cased it. You have to track down full call statck of BatchA & BatchB to understand what's happening.
If you're getting errors, the transactions will rollback, so you may need to capture the call stack using RPC & Batch starting, not RPC & Batch completed. Also, if these things are within a stored procedure, your job gets way easier because you can just get the objectid and look that up. However, if it's ad hoc queries you'll need to capture them to make sense of what's happening.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2017 at 2:29 am
Hi Grant, Gail,
Many thanks for your input. Very much appreciated.
Thanks
Gavin
April 4, 2017 at 2:49 am
April 4, 2017 at 3:22 am
Shameless self-promotion: 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
April 4, 2017 at 8:21 am
Hi John, Gail,
Many thanks for your assistance with this.
Thanks
Gavin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply