deadlock graph

Question

deadlock graph

lglass

SSC Journeyman

Points: 99
More actions
July 6, 2011 at 8:08 am

#254011

In all the examples that I've seen retrieving deadlock graphs with SQL Profiler, traces or extended events, the text of the sql statement that is running is inserted into the graph after the sqlhandle. We are not seeing this in any of our output. Looking at recent graphs, sometimes I can query the statement using the sqlhandle but other times there are no rows returned from sys.dm_exec_sql_text. Is there some setting or startup parameter that would resolve the handles to the query in the graph?
Thanks in advance

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Post an example graph.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lglass SSC Journeyman Points: 99 More actions · Answer 2

<deadlock-list>

</victim-list><process-list>

</frame>

</executionStack>

Proc [Database Id = 2 Object Id = 1583978284] </inputbuf>

</process>

</frame>

</executionStack>

Proc [Database Id = 2 Object Id = 722383553] </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</pagelock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</pagelock>

</resource-list>

</deadlock>

</deadlock-list>

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Are you using temporary procedures? The proc references DB 2 - TempDB. Real procedures shouldn't be in there.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lglass SSC Journeyman Points: 99 More actions · Answer 4

As a matter of fact, just yesterday we discovered that our jdbc driver is creating temporary procedures for each statement. We are actually going to test changing the parameter that controls that. We're not sure why it was turned on. I can look for a graph where at least one side is not a temporary procedure.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

I'm guessing that's it. It's clearly noting the proc as an object in TempDB. Probably some complex reason why they can't get the text from a temporary proc.

If you've got one with permanent objects that shows the same behaviour I'd like to see it.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lglass SSC Journeyman Points: 99 More actions · Answer 6

Looking further, I see that <inputbuf> is nearly always "Proc [Database Id = 2..." on that server, even if the stored procedure that's running is in a different database.

On a different server, where the 'create temporary stored procedure' parameter is turned off, I see this in one graph:

</frame>

</frame>

</executionStack>

(@P0 bigint,@P1 datetime)EXEC Test_db1.dbo.sp_CreateDynamic @P0, @P1 </inputbuf>

</process>

<inputbuf> has a value, but the current sql statement still isn't after the sqlhandle. I'm guessing this is because of the parameters in front of the statement. In this case, I can pull up that statement using the sqlhandle and offsets.

So I hope that the change to that setting will at least give us the full transaction in inputbuf. Please let me know if you have any other suggestions.

Thank you.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Can you post the entire of that deadlock graph please?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lglass SSC Journeyman Points: 99 More actions · Answer 8

<deadlock-list>

<victim-list/>

</victim-list><process-list>

</frame>

</frame>

</executionStack>

</inputbuf>

</process>

</frame>

</frame>

</executionStack>

</inputbuf>

</process>

</frame>

</frame>

</executionStack>

(@P0 bigint, @P1 datetime)EXEC Test_db1.dbo.sp_CreateDynamic @P0, @P1 </inputbuf>

</process>

</frame>

</frame>

</executionStack>

</inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</exchangeEvent>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</exchangeEvent>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</exchangeEvent>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</exchangeEvent>

</resource-list>

</deadlock>

</deadlock-list>

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

That's a intra-query parallelism deadlock. If you look, all the processes have the same SPID. That's actually a bug, they shouldn't occur.

The lack of query text is most likely a side effect of that. It's not a true lock-related deadlock.

To fix that, add the MAXDOP hint to that query, MAXDOP 1, and maybe consider logging a case with CSS, those are bugs that should be submitted. Can't guarantee that they will be able to get you a fix though.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 10

Which jdbc driver are you using? And can you post the code for the temporary stored proc?

(@P0 bigint, @P1 datetime)EXEC Test_db1.dbo.sp_CreateDynamic @P0, @P1

</curious>

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

lglass SSC Journeyman Points: 99 More actions · Answer 11

This one isn't a temp stored procedure, it's a 3000 line stored procedure in one of our databases.

We are using jtDS 1.2.2, the parameter in question is prepareSQL. One server is set to create temporary stored procedures.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

3000 lines? Eeep.

What's the following fragment of that proc?

line="239" stmtstart="16530" stmtend="17452"

(stmtstart and stmtend are character offsets. So looking for about 1000 characters starting 16530 characters into the proc)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

opc.three (7/6/2011)
Which jdbc driver are you using? And can you post the code for the temporary stored proc?
(@P0 bigint, @P1 datetime)EXEC Test_db1.dbo.sp_CreateDynamic @P0, @P1
</curious>

You can attach as a file.