January 28, 2014 at 4:55 am
I am capturing deadlock graph by using extent events script and verified the resource and process lists
Resource list
1. owner id mode="X" (Exclusive lock & shared lock)
2. waiter id mode = "S"
Process list
1. isolation level - READ COMMITTED
Pls. suggestion me, how to avoid these deadlock next time?
<deadlock>
<victim-list>
<victimProcess id="processb2db6088" />
</victim-list>
<process-list>
<process id="processb2db6088" taskpriority="0" logused="0" waitresource="KEY: 6:72057595317321728 (bd33db696ebe)" waittime="787" ownerId="4354080876" transactionname="SELECT" lasttranstarted="2014-01-28T12:50:18.493" XDES="0x273cad760" lockMode="S" schedulerid="1" kpid="2428" status="suspended" spid="382" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-01-28T12:50:18.460" lastbatchcompleted="2014-01-28T12:50:18.450" hostpid="3428" loginname="ril_prod" isolationlevel="read committed (2)" xactid="4354080876" currentdb="6" lockTimeout="4294967295" clientoption1="671350816" clientoption2="128056">
<executionStack>
<frame procname="" line="1" stmtstart="594" sqlhandle="0x02000000b477fe104280901a385a0bfbe924d3eb6860d83e" />
<frame procname="" line="1" sqlhandle="0x02000000f3369839d79e8116b1fe3d7bcf5bb94ee7bd3d5d" />
</executionStack>
<inputbuf>
Select L.VALUE, Count(*) From SAMPLE SAMPLE, LIST_ENTRY L Where L.NAME = SAMPLE.STATUS And L.LIST = 'SAMPLE_STATUS' And (SAMPLE.GROUP_NAME IN ( 'SYSTEM' , 'PMD' , 'DEFAULT')) And SAMPLE.T_PLANT IN ('PTA_PMD','EC_PMD','PX_PMD','LAB_PMD','TF_PMD') And SAMPLE.STATUS In ('I','P','C','A','U','H','L','S') AND SAMPLE.ALIQUOT_GROUP IS NULL And SAMPLE.SAMPLED_DATE >={ts '2014-01-27 12:50:18'} And SAMPLE.SAMPLED_DATE <={ts '2014-01-28 12:50:18'} GROUP By L.VALUE ORDER By L.VALUE Desc </inputbuf>
</process>
<process id="process50cabc8" taskpriority="0" logused="308" waitresource="KEY: 6:72057595317190656 (53d7f1749a9d)" waittime="795" ownerId="4354080893" transactionname="UPDATE" lasttranstarted="2014-01-28T12:50:18.517" XDES="0x199633620" lockMode="X" schedulerid="10" kpid="8816" status="suspended" spid="532" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-01-28T12:50:18.517" lastbatchcompleted="2014-01-28T12:50:18.513" hostpid="20128" loginname="ril_prod" isolationlevel="read committed (2)" xactid="4354080893" currentdb="6" lockTimeout="4294967295" clientoption1="671350816" clientoption2="128056">
<executionStack>
<frame procname="" line="1" stmtstart="164" sqlhandle="0x02000000d5e7012dfd2c0db24c1489a3a4dc652cb414b4f0" />
<frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
(@P1 datetime2,@P2 varchar(1),@P3 varchar(1),@P4 varchar(1),@P5 datetime2,@P6 int)UPDATE SAMPLE SET DATE_STARTED=@P1, STARTED=@P2, OLD_STATUS=@P3, STATUS=@P4, CHANGED_ON=@P5 WHERE SAMPLE_NUMBER=@P6 </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057595317321728" dbid="6" objectname="" indexname="" id="lock26ea65280" mode="X" associatedObjectId="72057595317321728">
<owner-list>
<owner id="process50cabc8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processb2db6088" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057595317190656" dbid="6" objectname="" indexname="" id="lock1d088d880" mode="S" associatedObjectId="72057595317190656">
<owner-list>
<owner id="processb2db6088" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process50cabc8" mode="X" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
Thanks
ananda
January 29, 2014 at 12:17 am
There are multiple ways...
1. If possible change the execution timings if there are more than one jobs or proc executed at same time. Or
2. If possible use nolock option in query having issue. or
3. Get in touch with development team to change the code to avoid the exclusive lock.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 30, 2014 at 11:28 pm
There is one way deadlock resolution as below steps
Step 1: captured the SELECT Query from Deadlock graph
Step 2: created actual execution plan and found the Total Query cost is 0 but Primary key Look-up cost is 99%, because that Query not using cluster Index on table, so I look at the another side if any missing index will suggested by SQL Optimizer with help of actual execution plan. Yes Optimizer suggested me as below
/*
Missing Index Details from SQLQuery4.sql -
The Query Processor estimates that implementing the following index could improve the query cost by 99.5429%.
*/
GO
CREATE NONCLUSTERED INDEX [SAMPLE_Status_IX]
ON [dbo].[SAMPLE] ([ALIQUOT_GROUP],[STATUS],[SAMPLED_DATE],[GROUP_NAME],[T_PLANT])
GO
I checked out dated stats and existing index frag., before creating index.
Step 3: I create Non clustered on SAMPLE table as above command then again created actual execution plan as attached here, it was improved and Primary key coast is 13 % , before it was 99%. And there is no more wait time for execution that particular query..
what about that UPDATE statement? or need to be tuning? as per deadlock graph.
Looking for your suggestions above steps.
Thanks
ananda
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply