How to fix this deadlock?
-
H All,
We are seeing a deadlock happening repeatedly in our environment.
Using SQL 2014.
Was able to get the xml deadlock graph and found below.
Tried below options but didn't work out.
1. The retry logic is there for 3 times, still it fails.
2. Tried creating a non-clustered idx on a primary key column to avoid the deadlock. Still we can see deadlocks happening.How can I fix this deadlock issue.Pl suggest.
Attaching the xml deadlock graph as well.Thanks,
Sam
-
Would help to know what query or queries that are deadlocking.
-
I can't open your file - just as likely to be due to restrictions this end as to anything wrong with the file itself. Combing through a deadlock graph isn't easy, though, which is why you might struggle to get someone to do it for you for free. Try going through this, and don't skip any steps. It may be a little tedious, but if it enables you to fix your deadlock, it'll be worth it.
John
- John Mitchell-245523 - Tuesday, May 22, 2018 8:13 AM
Same here. Can you try recreating your archive file, perhaps using 7zip instead? Just an idea.
- Lynn Pettis - Tuesday, May 22, 2018 8:20 AM
I got it open with 7zip - had to unblock it first.
Sue
-
pasting the contents of deadlock.xml
<deadlock>
<victim-list>
<victimProcess id="processbb030df848" />
</victim-list>
<process-list>
<process id="processbb030df848" taskpriority="0" logused="19068" waitresource="OBJECT: 9:1573580644:1 " waittime="1952" ownerId="24694507" transactionname="user_transaction" lasttranstarted="2018-05-18T07:10:59.770" XDES="0xe260858458" lockMode="IX" schedulerid="2" kpid="32972" status="suspended" spid="151" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-18T07:10:59.767" lastbatchcompleted="2018-05-18T07:10:59.763" lastattention="2018-05-18T07:10:50.380" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF90A2B4" hostpid="10300" loginname="BDO-APT-LMM-SVC-LDT2" isolationlevel="read committed (2)" xactid="24694507" currentdb="9" currentdbname="BDO-DB-APT-LIB-LDT2-IND" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x6ce3c436b69d2aca" queryplanhash="0x8fa5d20e41e04f31" line="2" stmtstart="1202" stmtend="1600" sqlhandle="0x02000000d87099192c204db8c012d991efdaaf84075a45230000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p27 uniqueidentifier,@p28 uniqueidentifier,@p29 uniqueidentifier,@p30 uniqueidentifier,@p31 uniqueidentifier,@p32 uniqueidentifier,@p33 uniqueidentifier,@p34 uniqueidentifier,@p35 uniqueidentifier,@p36 uniqueidentifier,@p37 uniqueidentifier,@p38 uniqueidentifier,@p39 uniqueidentifier,@p40 uniqueidentifier,@p41 uniqueidentifier,@p42 uniqueidentifier,@p43 uniqueidentifier,@p44 uniqueidentifier,@p45 nvarchar(10),@p46 bit,@p47 bigint,@p48 uniqueidentifier,@p49 datetime2(7),@p50 uniqueidentifier,@p51 nvarchar(4000),@p52 uniqueidentifier,@p53 uniqueidentifier,@p54 uniqueidentifier)SET NOCOUNT ON;
INSERT INTO [AMT].[ItemTags] ([Id], [ItemVersionId], [TagVersionId])
VALUES (@p27, @p28, @p29),
(@p30, @p31, @p32),
(@p33, @p34, @p35),
(@p36, @p37, @p38),
(@p39, @p40, @p41),
(@p42, @p43, @p44);
INSERT INTO [AMT].[ItemVersionMasterTranslations] ([FlagColor], [IsTailored], [ItemAttributeId], [ItemVersionId], [ValueChangedAt])
VALUES (@p45, @p46, @p47, @p48, @p49);
SELECT [Id]
FROM [AMT].[ItemVersionMasterTran </inputbuf>
</process>
<process id="processbcfb923848" taskpriority="0" logused="57516616" waitresource="KEY: 9:72057594080854016 (43a704ad655c)" waittime="1866" ownerId="24670444" transactionname="user_transaction" lasttranstarted="2018-05-18T07:10:46.583" XDES="0xbbfcd04458" lockMode="S" schedulerid="1" kpid="28676" status="suspended" spid="158" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-18T07:10:59.733" lastbatchcompleted="2018-05-18T07:10:59.730" lastattention="2018-05-18T07:10:59.733" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF90A2B4" hostpid="6276" loginname="BDO-APT-LMM-SVC-LDT2" isolationlevel="read committed (2)" xactid="24670444" currentdb="9" currentdbname="BDO-DB-APT-LIB-LDT2-IND" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x71e46993f43dba47" queryplanhash="0x07325a7346e1218f" line="1" stmtend="1090" sqlhandle="0x02000000b33dd11597bf048957dd703539b11e274248f7990000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
INSERT INTO [Questionnaire].[FormattingActions] ([ChangesetId], [Id], [ActionId], [CreatedAt], [CreatedBy], [DeletedAt], [DeletedBy], [FormattingOptionId], [IsDeleted], [IsSynced], [ModifiedAt], [ModifiedBy], [TargetFieldId], [TargetHeaderColumnId], [TargetRowFieldId]) SELECT [ChangesetId], [Id], [ActionId], [CreatedAt], [CreatedBy], [DeletedAt], [DeletedBy], [FormattingOptionId], [IsDeleted], [IsSynced], [ModifiedAt], [ModifiedBy], [TargetFieldId], [TargetHeaderColumnId], [TargetRowFieldId] FROM [Questionnaire].[#FormattingActions_049d2c]; DROP TABLE [Questionnaire].[#FormattingActions_049d2c]; </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="1" objid="1573580644" subresource="FULL" dbid="9" objectname="d146518a-f1b1-4f0b-9ee3-a59003836299.AMT.ItemTags" id="lockbc048abd80" mode="X" associatedObjectId="1573580644">
<owner-list>
<owner id="processbcfb923848" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processbb030df848" mode="IX" requestType="wait" />
</waiter-list>
</objectlock>
<keylock hobtid="72057594080854016" dbid="9" objectname="d146518a-f1b1-4f0b-9ee3-a59003836299.Common.Changesets" indexname="PK_Changesets" id="lockbc01829480" mode="X" associatedObjectId="72057594080854016">
<owner-list>
<owner id="processbb030df848" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processbcfb923848" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock> -
Like I said, this is something you need to do yourself. It's too tiresome to expect someone else to do it for you, not to mention the fact that we don't know what table your objid="1573580644" refers to.
John
-
Did you checked the execution plan of both the queries.
If the deadlock is between Insert and Select queries try to run the select query with nolock and check if it clears the problem.
Thank You.
Regards,
Raghavender Chavva - Sue_H - Tuesday, May 22, 2018 8:52 AM
How? I was able to download it but can't open it.
- John Mitchell-245523 - Tuesday, May 22, 2018 9:41 AM
Hi John, I have done my analysis. If u check the diagram, that is my analysis based on deadlock.xml.
objid="1573580644" subresource="FULL" dbid="9" objectname="d146518a-f1b1-4f0b-9ee3-a59003836299.AMT.ItemTags"
Looking for fixing this deadlock.
- Lynn Pettis - Tuesday, May 22, 2018 9:52 AM
don't know. Re-uploaded in the form of .txt file.
- vsamantha35 - Tuesday, May 22, 2018 9:55 AM
No, you haven't done your analysis in the detail described in the link I sent you.
John
- John Mitchell-245523 - Tuesday, May 22, 2018 10:00 AM
except below part, i covered almost. And I know the object name is AMT.ItemTags.
SELECT OBJECT_NAME(i.object_id), i.name
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.partition_id = 1573580644 -
You have all the information available to solve this problem, we don't. All you have provided to us is the deadlock graph. This is not enough to even try and help you. Without the queries, the table and index definitions all we can do is take shots in the dark while wearing a blind fold and hope we hit a target, any target.
Another way to put it, we can't see what you see.
- Lynn Pettis - Tuesday, May 22, 2018 10:27 AM
I understand sir. Even I am facing the same problem. This is a deadlock which is generate by .NET Entity framework code first model which converts the .net code to sql and runs at the backend. The front end team wasnt co-operative. Even I have requested them if they can reproduce the issue using plain tsql in ssms but the dont respond... Thought collecting the deadlock graph can help.
How about using SNAPSHOT isolation level? If nothing works, not sure thinking to put a dirty fix.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply