May 5, 2015 at 7:57 am
In the graph, it shows that both queries want an intent exclusive lock. However, the queries are only doing a select in read committed. Can anyone help explain what I am seeing? Attaching xdl file.
Jared
CE - Microsoft
May 5, 2015 at 10:04 am
Hey Jared!
I'm not sure. Are these statements part of a larger batch? Do you have triggers on the system? How about CDC or Audit? It seems like something other than simply two SELECT statements would cause a deadlock, especially with the IX lock in question. That's not something you get from a standard query.
Have you looked at the Object_ID to determine exactly what is being deadlocked on? That might help.
"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
May 5, 2015 at 10:50 am
Hey Grant! I have a feeling that this Entity Framework is doing more than just the sql coming in from input buffer on the deadlock graph. I'm just curious why the graph doesn't catch it.
No CDC, triggers, or audit. The table has a clustered index on an identity column and a foreign key constraint to an identity column in another table. Object id just shows the table in question. Strange, huh!
FYI: PR Deadlift 195lbs, no straps 🙂
Jared
CE - Microsoft
May 5, 2015 at 10:56 am
That's weird. What's the isolation level on the connections do you know?
Sorry I don't have better info. This one looks really odd.
Nice job on the PR.
"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
May 5, 2015 at 11:35 am
According to the xml it is read committed. In addition, I looked at my logging table for a snapshot of requests, and this one is coming in at read committed as well.
Jared
CE - Microsoft
May 5, 2015 at 12:40 pm
That is intriguing.
Does passing the 0x020000000b814f2b111b62d3b9a53282e0b2aaccbc90630b sql_handle to sys.dm_exec_sql_text just return the same SELECT statement?
May 5, 2015 at 12:50 pm
Unfortunately, yes.
Jared
CE - Microsoft
May 5, 2015 at 2:12 pm
Hmmm...that is frustrating.
I suppose you could read the log/log backup looking for the XactIDs mentioned in the xml or run a trace to see what batches typically precede those SELECTs, but I'm not sure what else could be done if the deadlock xml is omitting that information.
Cheers!
May 5, 2015 at 5:10 pm
I'd set up an extended event session and include causality tracking. I'll bet this is part of some extended transaction and just evidencing the deadlock in a funny way.
"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
May 6, 2015 at 4:31 am
Good idea Grant! I just recreated my event with this. We'll see what I find. Also, I track requests on a poling interval in a table and I am going to try to look and see if I can capture the activity of this spid tracking this action.
Jared
CE - Microsoft
May 6, 2015 at 5:00 am
Update: I tired to use CAUSALITY_TRACKING, but I am not familiar with it too much and I believe I would have had to add another event like sql_statement_starting and I couldn't figure out how to filter it.
Looking back through requests, though... I found this little gem:
(@0 char(3),@1 decimal(6,2))INSERT [dbo].[TowBatch]([TowBatchStatus], [GeneratedRoute], [Distance])
VALUES (@0, NULL, @1)
SELECT [TowBatchID]
FROM [dbo].[TowBatch]
WHERE @@ROWCOUNT > 0 AND [TowBatchID] = scope_identity()
I believe this to be the culprit. How I love Entity Framework!
Jared
CE - Microsoft
May 6, 2015 at 6:19 am
Sounds likely.
Just so you know, the causality tracking adds an additional value to the events. If any set of events are linked, they'll all have the same value, so you can group them. There's also an order value so you can sort them. And yeah, probably, guessing, you'd need to add an event. I suspect rpc_complete and batch_complete probably wouldn't be all you'd need, but I'd still start there when doing the experiment.
"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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply