Recurring deadlock in sharepoint 2010 on sql 2008

Question

Post reply

Recurring deadlock in sharepoint 2010 on sql 2008

jspatz

Ten Centuries

Points: 1314
More actions
September 21, 2010 at 10:09 am

#229603

We are having a frequent recurring deadlock on sharepoint, here is the deadlockgraph. I was assuming I would see some statements that were causing the blocks, but all I can tell is that it is happening on the searchcrawldb within sharepoint.. Any help would be greatly appreciated
<deadlock-list>
<deadlock>
<victim-list>
<victimProcess id="process5a3a748" />
</victim-list>
<process-list>
<process id="process5a3a748" taskpriority="0" logused="276" waitresource="RID: 23:1:6160:0" waittime="5038" ownerId="23502838" transactionname="user_transaction" lasttranstarted="2010-09-21T11:42:31.863" XDES="0x20b3b43b0" lockMode="S" schedulerid="6" kpid="6092" status="suspended" spid="152" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2010-09-21T11:42:31.800" lastbatchcompleted="2010-09-21T11:42:31.800" clientapp="Microsoft SharePoint Foundation" hostname="UHMC-SP-APP01" hostpid="5260" loginname="UHMC\SPWSSSearchSvc" isolationlevel="read committed (2)" xactid="23502838" currentdb="23" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="" line="47" stmtstart="2770" stmtend="3754" sqlhandle="0x03001700c2f82d28c42ff600a39d00000100000000000000" />
<frame procname="" line="556" stmtstart="64710" stmtend="64964" sqlhandle="0x03001700316f56106a2ff600a39d00000100000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 23 Object Id = 274100017] </inputbuf>
</process>
<process id="process1f63b2bc8" taskpriority="0" logused="276" waitresource="RID: 23:3:216:0" waittime="5005" ownerId="23502902" transactionname="user_transaction" lasttranstarted="2010-09-21T11:42:31.947" XDES="0x163c23950" lockMode="U" schedulerid="2" kpid="2900" status="suspended" spid="153" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-09-21T11:42:31.887" lastbatchcompleted="2010-09-21T11:42:31.887" clientapp="Microsoft SharePoint Foundation" hostname="UHMC-SP-APP01" hostpid="5260" loginname="UHMC\SPWSSSearchSvc" isolationlevel="read committed (2)" xactid="23502902" currentdb="23" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="" line="55" stmtstart="4198" stmtend="4414" sqlhandle="0x03001700a5436923b12ff600a39d00000100000000000000" />
<frame procname="" line="69" stmtstart="4372" stmtend="4600" sqlhandle="0x03001700c2f82d28c42ff600a39d00000100000000000000" />
<frame procname="" line="556" stmtstart="64710" stmtend="64964" sqlhandle="0x03001700316f56106a2ff600a39d00000100000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 23 Object Id = 274100017] </inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="6160" dbid="23" objectname="" id="lockc9cbbb00" mode="X" associatedObjectId="72057594043891712">
<owner-list>
<owner id="process1f63b2bc8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process5a3a748" mode="S" requestType="wait" />
</waiter-list>
</ridlock>
<ridlock fileid="3" pageid="216" dbid="23" objectname="" id="lock248c01900" mode="X" associatedObjectId="72057594043891712">
<owner-list>
<owner id="process5a3a748" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process1f63b2bc8" mode="U" requestType="wait" />
</waiter-list>
</ridlock>
</resource-list>
</deadlock>
</deadlock-list>

Viewing 5 posts - 1 through 4 (of 4 total)

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

Geoff A SSChampion Points: 11511 More actions · Answer 1

if you want to see the statement causing the block, you have to set your instance to do so;

DBCC TRACEON (3605,1204,1222,-1)

or alter the startup parameter with the same values 😉

jspatz Ten Centuries Points: 1314 More actions · Answer 2

Thank you for the reply .. I did run that dbcc traceon .. waited for another deadlock and the output is the same .. I am using this commands to see the deadlocks

select CAST(

REPLACE(

REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),

'<victim-list>', '<deadlock><victim-list>'),

'<process-list>','</victim-list><process-list>')

as xml) as DeadlockGraph

FROM

(select CAST(target_data as xml) as TargetData

from sys.dm_xe_session_targets st

join sys.dm_xe_sessions s on s.address = st.event_session_address

where name = 'system_health') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

<deadlock-list>

<victim-list>

</victim-list>

<process-list>

</executionStack>

Proc [Database Id = 23 Object Id = 274100017] </inputbuf>

</process>

</executionStack>

Proc [Database Id = 23 Object Id = 274100017] </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

</deadlock-list>

Geoff A SSChampion Points: 11511 More actions · Answer 3

use this instead;

EXEC master.dbo.xp_readerrorlog

scroll towards the bottom and look for

Deadlock encountered .... Printing deadlock information

subbu_e SSCrazy Points: 2431 More actions · Answer 4

you can also configure the trace for deadlock which will give you victim and the query, or sp.

Note: run the profiler on the different machine as it is resource intensive process.

Regards,
Subbu
Click here to Get Speedy answer or solution