November 24, 2009 at 3:03 am
Hi Everyone
How to solve this deadlock problem
There are two SP's one updating queuedata table & second inserting rows
<EVENT_INSTANCE>
<EventType>DEADLOCK_GRAPH</EventType>
<PostTime>2009-11-23T16:48:41.373</PostTime>
<SPID>15</SPID>
<TextData>
<deadlock-list>
<deadlock victim="processffbeb8">
<process-list>
<process id="processffbeb8" taskpriority="0" logused="0" waitresource="KEY: 9:72057594039238656 (3902c0e6b9f2)" waittime="4336" ownerId="26548842" transactionname="UPDATE" lasttranstarted="2009-11-23T16:48:37.023" XDES="0x111ad1720" lockMode="RangeS-U" schedulerid="3" kpid="452" status="suspended" spid="120" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-11-23T16:48:37.020" lastbatchcompleted="2009-11-23T16:48:37.020" clientapp=".Net SqlClient Data Provider" hostname="APP" hostpid="1796" loginname="fopluser" isolationlevel="serializable (4)" xactid="26548842" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="QDB_Central.dbo.uspUpdateOrgUnitAcknowledgements" line="35" stmtstart="1686" stmtend="2032" sqlhandle="0x03000900949d7f118720d800889c00000100000000000000">
UPDATE QueueData with(rowlock)
SET markAsDeleted = 0
WHERE destination = @destination
-- AND itemType = @itemType
-- Error messege for duplicate records </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 9 Object Id = 293576084] </inputbuf>
</process>
<process id="process3c009b8" taskpriority="0" logused="597628" waitresource="KEY: 9:72057594039238656 (550137a60582)" waittime="4305" ownerId="26548630" transactionname="INSERT" lasttranstarted="2009-11-23T16:48:36.860" XDES="0x2c4b9e900" lockMode="RangeI-N" schedulerid="4" kpid="3616" status="suspended" spid="152" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-11-23T16:48:36.860" lastbatchcompleted="2009-11-23T16:48:36.860" clientapp=".Net SqlClient Data Provider" hostname="APP" hostpid="1796" loginname="fopluser" isolationlevel="serializable (4)" xactid="26548630" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="QDB_Central.dbo.EnqueueMessage" line="45" stmtstart="4710" stmtend="5286" sqlhandle="0x030009009357ea084ff2e800de9b00000100000000000000">
INSERT into QueueData (itemValue, associatedApplicationName, itemGuid, itemType, destination, additionalInfo,itemPriority)
SELECT @queueItem, @application, @itemGuid, @itemType, seperatedcode, @additionalInfo,@priority from dbo.GetDelimiterSeperatedString(@destination,',')
--end </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 9 Object Id = 149575571] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594039238656" dbid="9" objectname="QDB_Central.dbo.QueueData" indexname="IX_QueueData" id="lock2dff85b00" mode="RangeS-U" associatedObjectId="72057594039238656">
<owner-list>
<owner id="processffbeb8" mode="RangeS-U" />
</owner-list>
<waiter-list>
<waiter id="process3c009b8" mode="RangeI-N" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594039238656" dbid="9" objectname="QDB_Central.dbo.QueueData" indexname="IX_QueueData" id="lock3c64b5380" mode="X" associatedObjectId="72057594039238656">
<owner-list>
<owner id="process3c009b8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processffbeb8" mode="RangeS-U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
</TextData>
<TransactionID />
<LoginName>sa</LoginName>
<StartTime>2009-11-23T16:48:41.370</StartTime>
<ServerName>CLUSSQL01</ServerName>
<LoginSid>AQ==</LoginSid>
<EventSequence>89658613</EventSequence>
<IsSystem>1</IsSystem>
<SessionLoginName />
</EVENT_INSTANCE>
Could anybody please suggest what to do to solve this deadlock
Thanks
Ghanshyam
November 24, 2009 at 7:51 am
What is the definition of the IX_QueueData index?
It looks like the AND itemType = @itemType in the WHERE clause of the UPDATE statement is commented out, is it?
Is the insert taking place in a cursor or loop?
Is there any other code within the 2 stored procedures?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 24, 2009 at 8:55 am
Can you post the definitions of the two procedures involved (QDB_Central.dbo.uspUpdateOrgUnitAcknowledgements and QDB_Central.dbo.EnqueueMessage), the definitions of the table involved (QueueData) with all of its indexes.
Why are you using the serializable isolation level? Do you need locking at that level?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2009 at 9:09 am
GilaMonster (11/24/2009)
Can you post the definitions of the two procedures involved (QDB_Central.dbo.uspUpdateOrgUnitAcknowledgements and QDB_Central.dbo.EnqueueMessage), the definitions of the table involved (QueueData) with all of its indexes.Why are you using the serializable isolation level? Do you need locking at that level?
Gail,
Now I have a question, wouldn't serializable likely be the cause of the deadlock at this point?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 24, 2009 at 9:49 am
Considering there are range shared locks involved, probably yes. Question is, is serialisable needed. If not, dropping to repeatable read or read committed will probably make this go away. If it is needed, may have to get creative. It should be possible, even if left in serialisable to not have deadlocks, locking yes, deadlocks no. (Emphasis: should)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply