Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reading Deadlock Trace


Reading Deadlock Trace

Author
Message
Kwisatz78
Kwisatz78
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 1821
Hi I wonder if anyone can help me troubleshoot this deadlock?

The input Buffer for SPID 829 is Proc [Database Id = 7 Object Id = 1003150619], I have found out what proc this is, but now what do I do to resolve the issue?

Thanks for any help


<deadlock-list>
<deadlock victim="processff0ef8">
<process-list>
<process id="processfda868" taskpriority="0" logused="4288" waitresource="KEY: 7:72057594042777600 (9b0130c3a9ea)" waittime="5000" ownerId="76534496436" transactionname="user_transaction" lasttranstarted="2010-07-20T10:03:26.843" XDES="0x13c227030" lockMode="U" schedulerid="3" kpid="9960" status="suspended" spid="829" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-07-20T10:03:26.843" lastbatchcompleted="2010-07-20T10:03:26.843" clientapp="Windows SharePoint Services" hostname="NIHR-MOSS1" hostpid="3112" loginname="xxx\xxx" isolationlevel="read committed (2)" xactid="76534496436" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="WSS_Search_NIHR-MOSS1.dbo.proc_MSS_ProcessDelete" line="162" stmtstart="13936" stmtend="14066" sqlhandle="0x03000700e2b8d63aa9ca57014a9b00000100000000000000">
DELETE FROM MSSCrawlQueue WHERE DocID=@DocID AND BatchID = 0 </frame>
<frame procname="WSS_Search_NIHR-MOSS1.dbo.proc_MSS_ProcessCommitted" line="251" stmtstart="21118" stmtend="22772" sqlhandle="0x030007001bddca3badca57014a9b00000100000000000000">
EXEC dbo.proc_MSS_ProcessDelete
@ProjectID,
@HisContentSourceID,
@StartAddressID,
@DocID,
@CrawlID,
@HisCrawlID,
@HisCommitCrawlID,
@Scope,
@HisTransactionFlags,
@UseChangeLog,
@ChangeLogCookie,
@ChangeLogBatchID,
@DeleteReason,
@TransactionType,
@HostDepth,
@EnumerationDepth,
@HisParentDocID,
@EndPathFlag,
@HostID,
@LCID,
@ErrorID,
@ErrorLevel,
@AccessURL,
@AccessHash,
@CompactURL,
@CompactHash,
@DisplayURL,
@DisplayHash,
@MaxDocId </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1003150619] </inputbuf>
</process>
<process id="processff0ef8" taskpriority="0" logused="820" waitresource="KEY: 7:72057594042580992 (020068e8b274)" waittime="10000" ownerId="76534496514" transactionname="user_transaction" lasttranstarted="2010-07-20T10:03:26.843" XDES="0x2c0e5af70" lockMode="X" schedulerid="4" kpid="7460" status="suspended" spid="533" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-07-20T10:03:26.843" lastbatchcompleted="2010-07-20T10:03:26.843" clientapp="Windows SharePoint Services" hostname="NIHR-MOSS1" hostpid="3112" loginname="xxx\xxx" isolationlevel="read committed (2)" xactid="76534496514" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="WSS_Search_NIHR-MOSS1.dbo.proc_MSS_UpdateHostStats" line="35" stmtstart="3098" sqlhandle="0x03000700a994e239a4ca57014a9b00000100000000000000">
UPDATE MSSCrawlHostList SET
SuccessCount = CASE WHEN SuccessCount + @SuccessCount > 0 THEN SuccessCount + @SuccessCount ELSE 0 END,
ErrorCount = CASE WHEN ErrorCount + @ErrorCount > 0 THEN ErrorCount + @ErrorCount ELSE 0 END,
WarningCount = CASE WHEN WarningCount + @WarningCount > 0 THEN WarningCount + @WarningCount ELSE 0 END
WHERE HostID = @HostID </frame>
<frame procname="WSS_Search_NIHR-MOSS1.dbo.proc_MSS_ProcessCommitted" line="134" stmtstart="13172" stmtend="13500" sqlhandle="0x030007001bddca3badca57014a9b00000100000000000000">
EXEC dbo.proc_MSS_UpdateHostStats @HostID, @TransactionType, @MarkDelete, @ErrorID, @ErrorLevel, @HisCommitCrawlID, @HisErrorLevel, @TrackIDDel, @DelErrorLevel </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1003150619] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594042580992" dbid="7" objectname="WSS_Search_NIHR-MOSS1.dbo.MSSCrawlHostList" indexname="PK_MSSCrawlHostList" id="lockc3091d00" mode="X" associatedObjectId="72057594042580992">
<owner-list>
<owner id="processfda868" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processff0ef8" mode="X" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594042777600" dbid="7" objectname="WSS_Search_NIHR-MOSS1.dbo.MSSCrawlQueue" indexname="IX_MSSCrawlQueue_Cluster" id="lockdeaeef80" mode="X" associatedObjectId="72057594042777600">
<owner-list>
<owner id="processff0ef8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processfda868" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>


1.
DataDog
DataDog
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 426
it is not easy to answer without knowing more, but ...

change either:

dbo.proc_MSS_ProcessDelete
-- or --
dbo.proc_MSS_UpdateHostStats

to access tables:

WSS_Search_NIHR-MOSS1.dbo.MSSCrawlHostList
-- and --
WSS_Search_NIHR-MOSS1.dbo.MSSCrawlQueue

in the same order

if the existing logic doesn't lend itself to a re-ordering
consider using a select to obtain and hold a lock
Kwisatz78
Kwisatz78
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 1821
Hi thanks for your reply. I think I am reading the XML incorrectly. I had thought that the code in the Input Buffer was the one executing during the deadlock.

In this case for both SPIDS it says that Proc [Database Id = 7 Object Id = 1003150619] was the code running. This is a Stored Proc called proc_MSS_ProcessComitted.

So am I wrong in thinking then that at the time of the deadlock both SPIDS were executing proc_MSS_ProcessComitted and it is that that requires the investigation?

Any help on actually reading the Execution Stack would be helpful as its really confusing to me.

Thanks
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8433 Visits: 10473
This code might help you out. Just take the deadlock XML info you posted above, put it into the @deadlock variable, and run.
declare @deadlock xml
set @deadlock = 'put your deadlock graph here'

select
[PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
[DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)



From this, I see that you are running an UPDATE and a DELETE at the same time from your sharepoint portal (different procs).
If you were to look at the execution plans for these commands, I bet you would find that one or both is doing table scans, effectively locking the entire table.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Kwisatz78
Kwisatz78
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 1821
Oh wow I was just looking at some of your code on another post Smile it makes it much easier to read thanks.

I do have some niggly questions like why does the LockMode U appear in the graph for the delete and vice versa? I assume its just the way the graph is rendered but it bugs me :s

The other question is what importance does the Input Buffer have, I think I was following this as a red herring. The proc mentioned in the Input Buffer actually ends up being proc_MSS_ProcessCommitted how does this relate to the deadlock? This proc is also mentioned in the <frame procname for both processes and am confused as to its relevance in the graphs.

I will also look at the execution plans for the two procs in questions, I no doubt will have more questions ..grr...

Many thanks Smile
DataDog
DataDog
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 426
wayne: that is very handy bit of sql

it is impossible / unreasonable and unnecessary to stop all deadlocks
as long as there is correct retry logic
they are irrelevant

if you have an application with no deadlock handling
then you can add the deadlock retry in to the SPs with try catch
Kwisatz78
Kwisatz78
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 1821
No I understand that but I was taking this opportunity to learn how to trouble shoot and understand the layout of the XML thats all.
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8433 Visits: 10473
doobya (7/21/2010)
wayne: that is very handy bit of sql


Blush Thanks. It's been very handy for me also.

it is impossible / unreasonable and unnecessary to stop all deadlocks

It depends on what the cause of the deadlock is. It's not unreasonable to investigate each and every deadlock, and see what you can do about it.

FWIW, when I started my job a bit over a year ago, we were getting regular deadlocks. After some work to set up traces to trap all deadlocks, and to investigate the causes and do some re-coding / indexing, deadlocks on our systems are now a very rare event.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8433 Visits: 10473
Kwisatz78 (7/21/2010)
Oh wow I was just looking at some of your code on another post Smile it makes it much easier to read thanks.

I do have some niggly questions like why does the LockMode U appear in the graph for the delete and vice versa? I assume its just the way the graph is rendered but it bugs me :s

The other question is what importance does the Input Buffer have, I think I was following this as a red herring. The proc mentioned in the Input Buffer actually ends up being proc_MSS_ProcessCommitted how does this relate to the deadlock? This proc is also mentioned in the <frame procname for both processes and am confused as to its relevance in the graphs.

I will also look at the execution plans for the two procs in questions, I no doubt will have more questions ..grr...

Many thanks Smile

The input buffer is the code that was called - it looks like you've identified it. It appears to contain both the update and delete statements.

I was looking into your graph a little bit more, and noticed that my code isn't returning what object is being locked. Looking into your graph, I see that they are locking on indexes. So, I've modified the code to return the indexes being locked.
select 
[PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
[DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
[KeylockObject] = Keylock.Process.value('@objectname', 'varchar(200)'),
[Index] = Keylock.Process.value('@indexname', 'varchar(200)'),
[IndexLockMode] = Keylock.Process.value('@mode', 'varchar(5)'),
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
LEFT JOIN @deadlock.nodes('/deadlock-list/deadlock/resource-list/keylock') as Keylock(Process)
ON Keylock.Process.value('owner-list[1]/owner[1]/@id', 'varchar(50)') =
Deadlock.Process.value('@id', 'varchar(50)')



This shows that the delete has an exclusive lock on the PK, and wants an update lock on the IX. The update has an exclusive lock on the IX, and wants an exclusive lock on the PK. The queries are pretty simple, so I'd check to see if there is an index on HostID, and one on DocID/BatchID.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

DataDog
DataDog
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 426
FWIW, when I started my job a bit over a year ago, we were getting regular deadlocks. After some work to set up traces to trap all deadlocks, and to investigate the causes and do some re-coding / indexing, deadlocks on our systems are now a very rare event.


my first big sql database was plagued with deadlocks during pre-production testing
I managed to redesign the system and achieve zero deadlocks
(it was a busy but simple / almost classic transaction processing application)

I thought I could repeat that on any database ... but once the complexity increases
the possible permutations of access becomes astronomical and what you have is a humungous race condition

So now I design for zero deadlocks, fail then redesign for the high frequency deadlocks and let retry logic mop up the low frequency deadlocks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search