January 4, 2013 at 11:45 am
Hi,
I've been doing a lot of reading on how to analyze a Deadlock Graph and I still can't seem to understand what is causing it. From the xml graph below, I can see that spid 163 is doing an UPDATE but I don't see any SQL or username associated with it. When I check SQL processes it is already gone. In the lower part I see that spid 378 is also trying to do an UPDATE and I see the SQL code and username and this is the VICTIM so it is causing our jobs to die. What I can't figure out is what process is the first one? The spid 378 is an automatic job that kicks off in our ERP system to update the table O001018 and it's dying becuase the other process is causing the deadlock. I have setup a SQL Audit on this table for any SELECT or UPDATE but it is not picking up anything so I'm not able to tell what is causing the deadlock. Is there a better way to read this Deadlock graph?
<deadlock>
<victim-list>
<victimProcess id="process5a2c53048" />
</victim-list>
<process-list>
<process id="process5a2c53048" taskpriority="0" logused="0" waitresource="PAGE: 5:18:3908131" waittime="3632" ownerId="3105107059" transactionname="UPDATE" lasttranstarted="2013-01-04T10:12:17.487" XDES="0x80005950" lockMode="U" schedulerid="7" kpid="4444" status="suspended" spid="163" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2013-01-04T10:12:17.487" lastbatchcompleted="2013-01-04T10:12:17.487" hostpid="4508" isolationlevel="read committed (2)" xactid="3105107059" currentdb="5" lockTimeout="120000" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="1" stmtstart="964" sqlhandle="0x020000007fd11b0e9277f5657526adf4e320ad4e5becad1a" />
<frame procname="" line="1" sqlhandle="0x0400ff7fb925310a01000000000000000000000000000000" />
</executionStack>
<inputbuf />
</process>
<process id="processe34546088" taskpriority="0" logused="200" waitresource="PAGE: 5:18:925886" waittime="3645" ownerId="3105107061" transactionname="UPDATE" lasttranstarted="2013-01-04T10:12:17.487" XDES="0x12a1079f0" lockMode="U" schedulerid="6" kpid="6148" status="suspended" spid="378" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2013-01-04T10:12:17.487" lastbatchcompleted="2013-01-04T10:12:17.487" hostpid="4508" isolationlevel="read committed (2)" xactid="3105107061" currentdb="5" lockTimeout="120000" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="1" stmtstart="964" sqlhandle="0x020000007fd11b0e9277f5657526adf4e320ad4e5becad1a" />
<frame procname="" line="1" sqlhandle="0x0400ff7fb925310a01000000000000000000000000000000" />
</executionStack>
<inputbuf />
</process>
<process id="process547388e08" taskpriority="0" logused="10000" waittime="3585" schedulerid="5" kpid="2108" status="suspended" spid="378" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-04T10:12:17.487" lastbatchcompleted="2013-01-04T10:12:17.487" lastattention="2013-01-04T09:55:31.890" hostpid="4508" loginname="MDBUSR" isolationlevel="read committed (2)" xactid="3105107061" currentdb="5" lockTimeout="120000" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="1" stmtstart="964" sqlhandle="0x020000007fd11b0e9277f5657526adf4e320ad4e5becad1a" />
<frame procname="" line="1" sqlhandle="0x0400ff7fb925310a01000000000000000000000000000000" />
</executionStack>
<inputbuf>
(@P1 decimal(15,6),@P2 decimal(15,6),@P3 decimal(15,6),@P4 decimal(15,6),@P5 decimal(15,6),@P6 decimal(3,2),@P7 decimal(15,6),@P8 decimal(15,6),@P9 decimal(1,0),@P10 decimal(1,0),@P11 decimal(1,0),@P12 decimal(1,0),@P13 decimal(3,2),@P14 decimal(15,6),@P15 decimal(3,2),@P16 decimal(1,0),@P17 decimal(3,0),@P18 nvarchar(3),@P19 decimal(2,0),@P20 nvarchar(3),@P21 decimal(2,0),@P22 nvarchar(4),@P23 decimal(4,0),@P24 decimal(2,0),@P25 nvarchar(3),@P26 nvarchar(15),@P27 nvarchar(4)) UPDATE SCHEMA.O001018 SET UCDEMA=@P1,MFCFOR=@P2,MFMFOR=@P3,MFNCFO=@P4,MFMADJ=@P5,MFALFF=@P6,MFMADV=@P7,MFAVER=@P8,MFALA1=@P9,MFALA2=@P10,MFALA3=@P11,MFALAO=@P12,MFTREF=@P13,MFTREQ=@P14,MFSEAF=@P15,MFPSTA=@P16 WHERE OSCONO=@P17 AND OSDIVI=@P18 AND OSLEVL=@P19 AND UCDIVI=@P20 AND OSSSTT=@P21 AND OSBVER=@P22 AND OSYEA4=@P23 AND OSPERI=@P24 AND UCWHLO=@P25 AND UCITNO=@P26 AND UCSMCD=@P27 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="18" pageid="3908131" dbid="5" objectname="" id="lock55e1ea500" mode="U" associatedObjectId="72057595135000576">
<owner-list>
<owner id="process547388e08" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process5a2c53048" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="18" pageid="925886" dbid="5" objectname="" id="lock55e439e80" mode="U" associatedObjectId="72057595135000576">
<owner-list>
<owner id="process5a2c53048" mode="U" />
</owner-list>
<waiter-list>
<waiter id="processe34546088" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<exchangeEvent id="Pipedad9c8700" WaitType="e_waitPipeGetRow" nodeId="3">
<owner-list>
<owner id="processe34546088" />
</owner-list>
<waiter-list>
<waiter id="process547388e08" />
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>
Thanks,
Isabelle
Thanks!
Bea Isabelle
January 4, 2013 at 12:09 pm
I see that you have captured three processes in this deadlock graph. All three of these are running the same code. Have you checked what the code is that is related to sql_handle
0x020000007fd11b0e9277f5657526adf4e320ad4e5becad1a and sql_handle 0x0400ff7fb925310a01000000000000000000000000000000
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2013 at 12:41 pm
Hi,
Yes...the first one is for the just comes up as sp_execute
select *
from sys.dm_exec_sql_text(0x0400ff7fb925310a01000000000000000000000000000000)
dbidobjectidnumberencryptedtext
3276717099308110sp_execute
And the 2nd one is the UPDATE that is the victim
select *
from sys.dm_exec_sql_text(0x020000007fd11b0e9277f5657526adf4e320ad4e5becad1a)
(@P1 decimal(15,6),@P2 decimal(15,6),@P3 decimal(15,6),@P4 decimal(15,6),@P5 decimal(15,6),@P6 decimal(3,2),@P7 decimal(15,6),@P8 decimal(15,6),@P9 decimal(1,0),@P10 decimal(1,0),@P11 decimal(1,0),@P12 decimal(1,0),@P13 decimal(3,2),@P14 decimal(15,6),@P15 decimal(3,2),@P16 decimal(1,0),@P17 decimal(3,0),@P18 nvarchar(3),@P19 decimal(2,0),@P20 nvarchar(3),@P21 decimal(2,0),@P22 nvarchar(4),@P23 decimal(4,0),@P24 decimal(2,0),@P25 nvarchar(3),@P26 nvarchar(15),@P27 nvarchar(4)) UPDATE SCHEMA.O001018 SET UCDEMA=@P1,MFCFOR=@P2,MFMFOR=@P3,MFNCFO=@P4,MFMADJ=@P5,MFALFF=@P6,MFMADV=@P7,MFAVER=@P8,MFALA1=@P9,MFALA2=@P10,MFALA3=@P11,MFALAO=@P12,MFTREF=@P13,MFTREQ=@P14,MFSEAF=@P15,MFPSTA=@P16 WHERE OSCONO=@P17 AND OSDIVI=@P18 AND OSLEVL=@P19 AND UCDIVI=@P20 AND OSSSTT=@P21 AND OSBVER=@P22 AND OSYEA4=@P23 AND OSPERI=@P24 AND UCWHLO=@P25 AND UCITNO=@P26 AND UCSMCD=@P27
This is why I am having a hard time trying to determine what that first one is.
Thanks,
Isabelle
Thanks!
Bea Isabelle
January 4, 2013 at 12:50 pm
Each of those plans are associated to each process that is captured. It looks as though the sp_executesql is executing the parameterized query. You have the same query running multiple times and one of them is deadlocking the other.
Look to optimize that query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2013 at 12:50 pm
Here is the prepared SQL from one of the processed shown in the graph (re-formatted by me). Does it look familiar?
(@P1 DECIMAL(15, 6),
@P2 DECIMAL(15, 6),
@P3 DECIMAL(15, 6),
@P4 DECIMAL(15, 6),
@P5 DECIMAL(15, 6),
@P6 DECIMAL(3, 2),
@P7 DECIMAL(15, 6),
@P8 DECIMAL(15, 6),
@P9 DECIMAL(1, 0),
@P10 DECIMAL(1, 0),
@P11 DECIMAL(1, 0),
@P12 DECIMAL(1, 0),
@P13 DECIMAL(3, 2),
@P14 DECIMAL(15, 6),
@P15 DECIMAL(3, 2),
@P16 DECIMAL(1, 0),
@P17 DECIMAL(3, 0),
@P18 NVARCHAR(3),
@P19 DECIMAL(2, 0),
@P20 NVARCHAR(3),
@P21 DECIMAL(2, 0),
@P22 NVARCHAR(4),
@P23 DECIMAL(4, 0),
@P24 DECIMAL(2, 0),
@P25 NVARCHAR(3),
@P26 NVARCHAR(15),
@P27 NVARCHAR(4))
UPDATE [SCHEMA].O001018
SET UCDEMA = @P1,
MFCFOR = @P2,
MFMFOR = @P3,
MFNCFO = @P4,
MFMADJ = @P5,
MFALFF = @P6,
MFMADV = @P7,
MFAVER = @P8,
MFALA1 = @P9,
MFALA2 = @P10,
MFALA3 = @P11,
MFALAO = @P12,
MFTREF = @P13,
MFTREQ = @P14,
MFSEAF = @P15,
MFPSTA = @P16
WHERE OSCONO = @P17
AND OSDIVI = @P18
AND OSLEVL = @P19
AND UCDIVI = @P20
AND OSSSTT = @P21
AND OSBVER = @P22
AND OSYEA4 = @P23
AND OSPERI = @P24
AND UCWHLO = @P25
AND UCITNO = @P26
AND UCSMCD = @P27
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 4, 2013 at 12:57 pm
Hi,
Yes, the query in the 2nd part is familiar and I know exactly what it is and why is it running. It is a normal part of our ERP routine and it automatically kicks off every 10 mins to update the dataset tables. What I don't know is the first spid that is kicking off which is causing the deadlock. I can't find anything to show what user or process is running the same UPDATE. Unfortunately the 2nd query that is being chosen as the victim is the automated process and everytime this happens, our jobs die causing problems. I understand what is happening but I'm just stumped on trying to figure out what that first process is. It only displays sp_execute and the xml deadlock graph doesn't show any username or any other useful information.
Thanks for your replies. I will keep looking.
Isabelle
Thanks!
Bea Isabelle
January 4, 2013 at 1:00 pm
What is the average run time for your ERP process?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2013 at 1:15 pm
I was typing while you guys had two more responses, sorry if my last post seemed redundant.
From the resource-list it looks like you might have things accessing resources in a different order. Check all the places where the table in the prepared query is accessed. If any access is done inside an explicit transaction and there are other tables are accessed in the transaction, then the tables need to always be accessed in the same order.
e.g. three batches like this can cause the type of deadlock you have
BEGIN TRAN
UPDATE TABLE_A
UPDATE TABLE_B
COMMIT TRAN
BEGIN TRAN
UPDATE TABLE_B
UPDATE TABLE_C
COMMIT TRAN
BEGIN TRAN
UPDATE TABLE_C
UPDATE TABLE_A
COMMIT TRAN
I think changing the third batch to this would fix your issue:
BEGIN TRAN
UPDATE TABLE_A
UPDATE TABLE_C
COMMIT TRAN
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 4, 2013 at 1:37 pm
opc.three (1/4/2013)
I was typing while you guys had two more responses, sorry if my last post seemed redundant.From the resource-list it looks like you might have things accessing resources in a different order. Check all the places where the table in the prepared query is accessed. If any access is done inside an explicit transaction and there are other tables are accessed in the transaction, then the tables need to always be accessed in the same order.
e.g. three batches like this can cause the type of deadlock you have
BEGIN TRAN
UPDATE TABLE_A
UPDATE TABLE_B
COMMIT TRAN
BEGIN TRAN
UPDATE TABLE_B
UPDATE TABLE_C
COMMIT TRAN
BEGIN TRAN
UPDATE TABLE_C
UPDATE TABLE_A
COMMIT TRAN
I think changing the third batch to this would fix your issue:
BEGIN TRAN
UPDATE TABLE_A
UPDATE TABLE_C
COMMIT TRAN
I was typing when the email alert came in. This pretty much summarizes what I was going to say.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2013 at 2:28 pm
Hi,
Thank you for your suggestions. I will definitely look into the order of the transactions to see if we can understand what is happening and why it is now deadlocking.
Isabelle
Thanks!
Bea Isabelle
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply