April 3, 2012 at 4:32 am
Hi , I am performing Archiving Process and also running one more process simultaneously and I am getting Deadlock,Below is the DeadLock Graph output ,I am not able to understand what is the possible reason of error please suggest me something.
<event name="xml_deadlock_report" package="sqlserver" id="123" version="1" timestamp="2012-04-03T10:07:25.311Z">
<data name="xml_report">
<type name="unicode_string" package="package0" />
<value><deadlock-list>
<victim-list>
<victimProcess id="process13f948"/>
<process-list>
<process id="process13f948" taskpriority="0" logused="10000" waittime="3075" schedulerid="1" kpid="6136" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-03T14:58:14.473" lastbatchcompleted="2012-04-03T14:14:08.597" lastattention="2012-04-02T18:41:26.480" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WIN2008" hostpid="256" loginname="sa" isolationlevel="read committed (2)" xactid="15381719" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="" line="1683" stmtstart="674608" stmtend="674954" sqlhandle="0x03000700ef14314b6da4f60028a000000100000000000000">
</frame>
<frame procname="" line="1" sqlhandle="0x010007008a4ec30900d4b553010000000000000000000000">
</frame>
</executionStack>
<inputbuf>
Exec SP_BatchArchiveAccounts 10,APARWANI_FV_CLSource,APARWANI_FV_CASource
</inputbuf>
</process>
<process id="process15c1be2c8" taskpriority="0" logused="61868" waitresource="OBJECT: 7:925962375:0 " waittime="4025" ownerId="15389580" transactionname="user_transaction" lasttranstarted="2012-04-03T15:37:56.203" XDES="0x8b973950" lockMode="IS" schedulerid="2" kpid="4980" status="suspended" spid="76" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-04-03T15:38:13.330" lastbatchcompleted="2012-04-03T15:38:13.320" clientapp="APP_wfTnpNad" hostname="APARWANI" hostpid="7024" loginname="sa" isolationlevel="read committed (2)" xactid="15389580" currentdb="7" lockTimeout="4294967295" clientoption1="536870944" clientoption2="128056">
<executionStack>
<frame procname="" line="6" stmtstart="298" stmtend="632" sqlhandle="0x030007004f32d23e09eac000c19d00000100000000000000">
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1053962831] </inputbuf>
</process>
<process id="process13f4c8" taskpriority="0" logused="10505484" waitresource="KEY: 7:72057594067091456 (1a68cc0ba9a2)" waittime="3967" ownerId="15381719" transactionname="user_transaction" lasttranstarted="2012-04-03T15:35:49.970" XDES="0x164a87cf0" lockMode="U" schedulerid="1" kpid="4756" status="suspended" spid="57" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2012-04-03T14:58:14.473" lastbatchcompleted="2012-04-03T14:14:08.597" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WIN2008" hostpid="256" isolationlevel="read committed (2)" xactid="15381719" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="" line="1683" stmtstart="674608" stmtend="674954" sqlhandle="0x03000700ef14314b6da4f60028a000000100000000000000">
</frame>
<frame procname="" line="1" sqlhandle="0x010007008a4ec30900d4b553010000000000000000000000">
</frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
</process-list>
<resource-list>
<exchangeEvent id="Pipe15b7fa580" WaitType="e_waitPipeGetRow" nodeId="2">
<owner-list>
<owner id="process13f4c8"/>
</owner-list>
<waiter-list>
<waiter id="process13f948"/>
</waiter-list>
</exchangeEvent>
<objectlock lockPartition="0" objid="925962375" subresource="FULL" dbid="7" objectname="" id="lock1628e6700" mode="X" associatedObjectId="925962375">
<owner-list>
<owner id="process13f948" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process15c1be2c8" mode="IS" requestType="wait"/>
</waiter-list>
</objectlock>
<keylock hobtid="72057594067091456" dbid="7" objectname="" indexname="" id="locke6bc3300" mode="X" associatedObjectId="72057594067091456">
<owner-list>
<owner id="process15c1be2c8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process13f4c8" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
</value>
<text />
</data>
</event>
</RingBufferTarget>
April 3, 2012 at 5:01 am
If you look at the input buffers you'll see a stored procedure call and something with an object id: Object Id = 1053962831
I'd start there. What does that Object id map to? Further, you can see the locked resources as identified by the database id and object id within the database. That'll tell you what was locked that caused the deadlock. Also, you have the query handle for each of the queries and you can still access these from cache (most of the time) after a deadlock to get the query itself as well as the execution plan. All that information should get you started.
"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, 2012 at 5:19 am
Hi,thanks For your reply but can u tell me one thing more that how can i trace using object id
April 3, 2012 at 5:39 am
You just use the OBJECT_NAME function to identify the object in question.
"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, 2012 at 6:05 am
Hi,In Trace I think there are three types of Locks
1.exchangeEvent id="Pipe11cd18580" WaitType="e_waitPipeGetRow" nodeId="2">
owner-list>
owner id="process13f708"/>
/owner-list>
waiter-list>
waiter id="process13ee08"/>
/waiter-list>
/exchangeEvent>
2.objectlock lockPartition="0" objid="925962375" subresource="FULL" dbid="7" objectname="" id="lock118262c00" mode="X" associatedObjectId="925962375">
owner-list>
owner id="process13ee08" mode="X"/>
/owner-list>
waiter-list>
waiter id="process3c3288" mode="IS" requestType="wait"/>
/waiter-list>
/objectlock>
3.keylock hobtid="72057594067091456" dbid="7" objectname="" indexname="" id="locke421ce00" mode="X" associatedObjectId="72057594067091456">
owner-list>
owner id="process3c3288" mode="X"/>
/owner-list>
waiter-list>
waiter id="process13f708" mode="U" requestType="wait"/>
/waiter-list>
/keylock>
am I right ,then what's the difference between them.Please tell me
April 3, 2012 at 6:35 am
The things I'd look at first are the two processes. Each of those shows you the lock that it was holding. That's where the conflict comes in, so that's the area you focus on. The input buffers tell you what was running. So you see what was running and what was being held and you figure out what to do from there.
"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, 2012 at 7:09 am
Here is a good resource for a novice looking to resolve deadlocking issues:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Note there are 2 additional parts to this blog series.
I warn you that it can get VERY complicated trying to figure these things out. And it is doubtful we can provide much help here unless the objects involved are dirt simple. You may need to get a professional to help you out.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply