June 11, 2018 at 11:21 pm
Hi All,
Need some help in fixing the deadlock.
Will an Intent exclusive (IX) lock can cause deadlock??
We are seeing deadlocks where one spid is having IX lock and another spid is requesting for a Shared lock.
Needs some advise here on how can I avoid/minimize the deadlock of current scenario.
Below is the deadlock graph. We can save it as .xml .xdl to see the Graph in ssms.
also attaching stored procedure code.
deadlock67.xml
===============
<deadlock>
<victim-list>
<victimProcess id="process72c7134ca8" />
</victim-list>
<process-list>
<process id="process72c7134ca8" taskpriority="0" logused="15242008" waitresource="OBJECT: 7:322100188:0 " waittime="4549" ownerId="16137045" transactionname="user_transaction" lasttranstarted="2018-06-08T07:58:02.500" XDES="0x72c3494458" lockMode="IX" schedulerid="1" kpid="52136" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-06-08T07:58:15.943" lastbatchcompleted="2018-06-08T07:58:15.943" lastattention="2018-06-08T07:58:14.733" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF9073D7" hostpid="4260" loginname="BDO-APT-LMM-SVC-LDT2" isolationlevel="read committed (2)" xactid="16137045" currentdb="7" currentdbname="BDO-DB-APT-LIB-LDT2-IND" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x921c0b78aa56bc6a" queryplanhash="0x7d3ad568ab0d3fd5" line="1" stmtend="1066" sqlhandle="0x02000000488d8e0d31e51a04301cdbf9481c3410ac0375dc0000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
insert bulk [Questionnaire].[FieldValues] ([ChangesetId] UniqueIdentifier, [Id] UniqueIdentifier, [CreatedAt] DateTimeOffset(0), [CreatedBy] Int, [DeletedAt] DateTimeOffset(0), [DeletedBy] Int, [FieldId] UniqueIdentifier, [IsDeleted] Bit, [LocaleTypeId] UniqueIdentifier, [ModifiedAt] DateTimeOffset(0), [ModifiedBy] Int, [PropertyDefinitionId] UniqueIdentifier, [Value] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [FlagColor] NVarChar(10) COLLATE SQL_Latin1_General_CP1_CI_AS, [IsTailored] Bit, [ValueChangedAt] DateTime2(0)) </inputbuf>
</process>
<process id="process72c4437c28" taskpriority="0" logused="63352560" waitresource="PAGE: 7:1:3313776 " waittime="3699" ownerId="16143070" transactionname="user_transaction" lasttranstarted="2018-06-08T07:58:10.190" XDES="0x70fd1c4040" lockMode="S" schedulerid="1" kpid="55288" status="suspended" spid="126" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-06-08T07:58:10.183" lastbatchcompleted="2018-06-08T07:58:10.160" lastattention="1900-01-01T00:00:00.160" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF9073D7" hostpid="5132" loginname="BDO-APT-LMM-SVC-LDT2" isolationlevel="read committed (2)" xactid="16143070" currentdb="7" currentdbname="BDO-DB-APT-LIB-LDT2-IND" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="e0ca04c6-94c4-498a-a3f3-6013a8d22925.Common.uspDeleteBaseContent" queryhash="0x4ac02b61b6324de1" queryplanhash="0xba96b7b3469aa21f" line="85" stmtstart="17482" stmtend="17680" sqlhandle="0x03000700dbcec17e0e54b500f8a8000001000000000000000000000000000000000000000000000000000000">
DELETE AA FROM AMT.ItemVersions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetI </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" stmtstart="84" stmtend="202" sqlhandle="0x010007004d91862090bfee657100000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@EmployeeId int,@BaseId uniqueidentifier)EXECUTE [Common].[uspDeleteBaseContent] @EmployeeId, @BaseId </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="0" objid="322100188" subresource="FULL" dbid="7" objectname="e0ca04c6-94c4-498a-a3f3-6013a8d22925.Questionnaire.FieldValues" id="lock720f6f4b80" mode="X" associatedObjectId="322100188">
<owner-list>
<owner id="process72c4437c28" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process72c7134ca8" mode="IX" requestType="wait" />
</waiter-list>
</objectlock>
<pagelock fileid="1" pageid="3313776" dbid="7" subresource="FULL" objectname="e0ca04c6-94c4-498a-a3f3-6013a8d22925.Questionnaire.Questionnaires" id="lock709542a100" mode="IX" associatedObjectId="72057594060537856">
<owner-list>
<owner id="process72c7134ca8" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process72c4437c28" mode="S" requestType="convert" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
June 12, 2018 at 1:23 am
You are trying to delete from Questionnaire at the same time as you are trying to bulk insert, so yes, you will get a deadlock with an IX lock. The intent of the lock is exclusive, so it doesn't want to share, intent in this case being irrelevant, it can't get the lock, make uspDeleteBaseContent Serializable and it should stop this happening.
June 12, 2018 at 2:47 am
Rick, many thanks for the response. Just for my understanding sake, Looking at the xml deadlock graph data,
Insert is happening on Questionare.FieldValues (spid 79) and delete is happening on AMT.ItemVersions tables (spid 126). But you are saying it is on Questionare.FieldValues?? Can u pl elaborate. or else based on the stored procedure code u meant to say, that the questionaire.fieldvalues is being deleted and at the same time BULK INSERT is happening on the other end. I don't know why the deadlock is showing DELETE on AMT.ItemVersions table instead of DELETE on Questionnaire.FieldValues that is where I was confused!
Also, this is how we can set the isolation level inside a stored procedure right? pl correct me if am wrong. Doing so, idea is to have blocking but no deadlocks? is that correct?
-- test code
CREATE PROCEDURE usp_p1
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION;
UPDATE T1 SET curr_balance = curr_balance * 1.2
COMMIT TRANSACTION;
END
2ndly, Can I disable lock escalation at table level? will it fix the deadlock?
ALTER TABLE AMT.ItemVersions SET (LOCK_ESCALATION = DISABLE);
ALTER TABLE Questionnaire.FieldValues SET (LOCK_ESCALATION = DISABLE);
June 12, 2018 at 3:01 am
OK, Fieldvalues is what is in the buffer at the time of the deadlock happening, but as you have Fieldvalues in your stored procedure, it will be holding a lock on it still. It's actually easier to see where the lock is happening on the graph than from the xml (which can be quite confusing sometimes).
As for setting the transaction level, yes, that is correct.
June 12, 2018 at 4:02 am
Thanks Rick.
Any thoughts on disabling lock escalation or it doesn't really matter/applicable in this scenario??
June 12, 2018 at 4:32 am
Its not applicable in this instance, the lock will always escalate on this kind of behaviour., the only thing you could try is set the transaction to snapshot, but your results will then be inconsistent, so would leave it as serializable.
June 12, 2018 at 8:08 am
vsamantha35 - Tuesday, June 12, 2018 4:02 AMThanks Rick.Any thoughts on disabling lock escalation or it doesn't really matter/applicable in this scenario??
In addition, you don't want to disable lock escalation. SQL Server uses that to minimize memory requirements for locks.
June 13, 2018 at 6:34 am
Hi Rick/Lynn,
Forgot to ask.
Do I need to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE inside both the processes (i.e. both code pieces stored proc and .net sqlbulkcopy code)
or else setting isolation level to serializable inside stored proc is enough?
Also, what would happen if I use this isolation level w.r.t. locks are concerned. How it is work behind the screens which avoids deadlock?
Thanks,
Sam
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply