LINQ to SQL - Dead Lock on Insert
I am facing a deadlock issue in SQL Server 2014 Standard Edition. Two inserts are deadlocked on the clustered index.
It is an OLTP database and a high insert and high read table, the table already has a clustered index primary key and has over 13 million records
The SQL statements are below
exec sp_executesql N'INSERT INTO [dbo].[PatientBilledItem]([PatientBillUID], [EventOccuredDttm], [ServiceName], [BillableItemUID], [Amount], [CUser], [CWhen], [MUser], [MWhen], [StatusFlag], [OwnerOrganisationUID], [PatientBillableItemUID], [Discount], [NetAmount], [Description], [Comments], [ItemMultiplier], [BSMDDUID], [ItemName], [DiscountAuthorizedBy], [ConsultantShare], [ServiceTax], [CareProviderUID], [QNUOMUID], [SpecialAmount], [IsRefunded], [ConsultantDiscount], [EducationCess], [HigherEducationCess], [RecordedByUID], [OriginalCreditBillUID], [PatientPackageItemUID], [BatchID], [VATPercentage], [InternalCost], [CalculateTaxOnMRP], [StoreUID], [BillPackageUID], [SplitDiscount], [RSLVLUID], [ParentUID], [PackageItemAmount], [RoundOff], [AuthNo], [ExpiryDttm], [BILGRDUID], [ApprovalCode], [ApprovedBy], [InternalBatchID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48)
SELECT [t0].[UID], [t0].[TIMESTAMP]
FROM [dbo].[PatientBilledItem] AS [t0]
WHERE [t0].[UID] = (SCOPE_IDENTITY())',N'@p0 bigint,@p1 datetime,@p2 nvarchar(4000),@p3 int,@p4 float,@p5 int,@p6 datetime,@p7 int,@p8 datetime,@p9 nvarchar(4000),@p10 int,@p11 bigint,@p12 float,@p13 float,@p14 nvarchar(4000),@p15 nvarchar(4000),@p16 float,@p17 int,@p18 nvarchar(4000),@p19 int,@p20 float,@p21 float,@p22 int,@p23 int,@p24 float,@p25 nvarchar(4000),@p26 float,@p27 float,@p28 float,@p29 int,@p30 bigint,@p31 bigint,@p32 nvarchar(4000),@p33 float,@p34 float,@p35 nvarchar(4000),@p36 int,@p37 int,@p38 float,@p39 int,@p40 bigint,@p41 float,@p42 float,@p43 nvarchar(4000),@p44 datetime,@p45 int,@p46 nvarchar(4000),@p47 int,@p48 nvarchar(4000)',@p0=3796014,@p1='2017-06-20 12:12:49.300',@p2=N'Consultancy',@p3=38328,@p4=500,@p5=4985,@p6='2017-06-20 12:13:12.603',@p7=4985,@p8='2017-06-20 12:13:12.603',@p9=N'A',@p10=4,@p11=10692556,@p12=NULL,@p13=500,@p14=NULL,@p15=NULL,@p16=1,@p17=1159,@p18=N'Dr. B K ',@p19=NULL,@p20=500,@p21=NULL,@p22=1395,@p23=NULL,@p24=NULL,@p25=NULL,@p26=NULL,@p27=NULL,@p28=NULL,@p29=NULL,@p30=NULL,@p31=NULL,@p32=NULL,@p33=NULL,@p34=NULL,@p35=NULL,@p36=NULL,@p37=NULL,@p38=NULL,@p39=NULL,@p40=NULL,@p41=NULL,@p42=NULL,@p43=NULL,@p44=NULL,@p45=NULL,@p46=NULL,@p47=NULL,@p48=NULL
exec sp_executesql N'INSERT INTO [dbo].[PatientBilledItem]([PatientBillUID], [EventOccuredDttm], [ServiceName], [BillableItemUID], [Amount], [CUser], [CWhen], [MUser], [MWhen], [StatusFlag], [OwnerOrganisationUID], [PatientBillableItemUID], [Discount], [NetAmount], [Description], [Comments], [ItemMultiplier], [BSMDDUID], [ItemName], [DiscountAuthorizedBy], [ConsultantShare], [ServiceTax], [CareProviderUID], [QNUOMUID], [SpecialAmount], [IsRefunded], [ConsultantDiscount], [EducationCess], [HigherEducationCess], [RecordedByUID], [OriginalCreditBillUID], [PatientPackageItemUID], [BatchID], [VATPercentage], [InternalCost], [CalculateTaxOnMRP], [StoreUID], [BillPackageUID], [SplitDiscount], [RSLVLUID], [ParentUID], [PackageItemAmount], [RoundOff], [AuthNo], [ExpiryDttm], [BILGRDUID], [ApprovalCode], [ApprovedBy], [InternalBatchID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48)
SELECT [t0].[UID], [t0].[TIMESTAMP]
FROM [dbo].[PatientBilledItem] AS [t0]
WHERE [t0].[UID] = (SCOPE_IDENTITY())',N'@p0 bigint,@p1 datetime,@p2 nvarchar(4000),@p3 int,@p4 float,@p5 int,@p6 datetime,@p7 int,@p8 datetime,@p9 nvarchar(4000),@p10 int,@p11 bigint,@p12 float,@p13 float,@p14 nvarchar(4000),@p15 nvarchar(4000),@p16 float,@p17 int,@p18 nvarchar(4000),@p19 int,@p20 float,@p21 float,@p22 int,@p23 int,@p24 float,@p25 nvarchar(4000),@p26 float,@p27 float,@p28 float,@p29 int,@p30 bigint,@p31 bigint,@p32 nvarchar(4000),@p33 float,@p34 float,@p35 nvarchar(4000),@p36 int,@p37 int,@p38 float,@p39 int,@p40 bigint,@p41 float,@p42 float,@p43 nvarchar(4000),@p44 datetime,@p45 int,@p46 nvarchar(4000),@p47 int,@p48 nvarchar(4000)',@p0=3796013,@p1='2017-06-20 12:13:10.030',@p2=N'Consultancy',@p3=83986,@p4=600,@p5=763,@p6='2017-06-20 12:13:12.023',@p7=763,@p8='2017-06-20 12:13:12.023',@p9=N'A',@p10=8,@p11=10692557,@p12=NULL,@p13=600,@p14=NULL,@p15=NULL,@p16=1,@p17=1159,@p18=N'Dr.L K S',@p19=NULL,@p20=600,@p21=NULL,@p22=1506,@p23=NULL,@p24=NULL,@p25=NULL,@p26=NULL,@p27=NULL,@p28=NULL,@p29=NULL,@p30=NULL,@p31=NULL,@p32=NULL,@p33=NULL,@p34=NULL,@p35=NULL,@p36=NULL,@p37=NULL,@p38=NULL,@p39=NULL,@p40=NULL,@p41=NULL,@p42=NULL,@p43=NULL,@p44=NULL,@p45=NULL,@p46=NULL,@p47=NULL,@p48=NULL
Yes, the table also has a trigger
Event XML file attached
<deadlock>
<victim-list>
<victimProcess id="processa19016ca8" />
</victim-list>
<process-list>
<process id="processa19016ca8" taskpriority="0" logused="3888" waitresource="KEY: 9:72057699966713856 (ffffffffffff)" waittime="1034" ownerId="1298235110" transactionname="user_transaction" lasttranstarted="2017-06-20T11:52:42.003" XDES="0x714fe0d90" lockMode="RangeI-N" schedulerid="1" kpid="7856" status="suspended" spid="66" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-20T11:52:42.830" lastbatchcompleted="2017-06-20T11:52:42.820" lastattention="1900-01-01T00:00:00.820" clientapp=".Net SqlClient Data Provider" hostname="WIN-2HO2RRV99BU" hostpid="2980" loginname="sa" isolationlevel="serializable (4)" xactid="1298235110" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="1236" stmtend="3454" sqlhandle="0x0200000064f4502b431082b3ac55b13757583f58c2c98c7c0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 bigint,@p1 datetime,@p2 nvarchar(4000),@p3 int,@p4 float,@p5 int,@p6 datetime,@p7 int,@p8 datetime,@p9 nvarchar(4000),@p10 int,@p11 bigint,@p12 float,@p13 float,@p14 nvarchar(4000),@p15 nvarchar(4000),@p16 float,@p17 int,@p18 nvarchar(4000),@p19 int,@p20 float,@p21 float,@p22 int,@p23 int,@p24 float,@p25 nvarchar(4000),@p26 float,@p27 float,@p28 float,@p29 int,@p30 bigint,@p31 bigint,@p32 nvarchar(4000),@p33 float,@p34 float,@p35 nvarchar(4000),@p36 int,@p37 int,@p38 float,@p39 int,@p40 bigint,@p41 float,@p42 float,@p43 nvarchar(4000),@p44 datetime,@p45 int,@p46 nvarchar(4000),@p47 int,@p48 nvarchar(4000))INSERT INTO [dbo].[PatientBilledItem]([PatientBillUID], [EventOccuredDttm], [ServiceName], [BillableItemUID], [Amount], [CUser], [CWhen], [MUser], [MWhen], [StatusFlag], [OwnerOrganisationUID], [PatientBillableItemUID], [Discount], [NetAmount], [Description], [Comments], [ItemMultiplier], [BSMDDUID], [ItemName], [DiscountAuthorizedBy], [ConsultantShare], [ServiceTax], [CareProviderUID], [QNUOMUID], [Sp </inputbuf>
</process>
<process id="process18572db848" taskpriority="0" logused="3896" waitresource="KEY: 9:72057699966713856 (ffffffffffff)" waittime="1012" ownerId="1298235129" transactionname="user_transaction" lasttranstarted="2017-06-20T11:52:42.027" XDES="0x1ada4fed90" lockMode="RangeI-N" schedulerid="1" kpid="14928" status="suspended" spid="118" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-20T11:52:42.853" lastbatchcompleted="2017-06-20T11:52:42.847" lastattention="1900-01-01T00:00:00.847" clientapp=".Net SqlClient Data Provider" hostname="WIN-PGECRKPS51J" hostpid="3092" loginname="sa" isolationlevel="serializable (4)" xactid="1298235129" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="1236" stmtend="3454" sqlhandle="0x0200000064f4502b431082b3ac55b13757583f58c2c98c7c0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 bigint,@p1 datetime,@p2 nvarchar(4000),@p3 int,@p4 float,@p5 int,@p6 datetime,@p7 int,@p8 datetime,@p9 nvarchar(4000),@p10 int,@p11 bigint,@p12 float,@p13 float,@p14 nvarchar(4000),@p15 nvarchar(4000),@p16 float,@p17 int,@p18 nvarchar(4000),@p19 int,@p20 float,@p21 float,@p22 int,@p23 int,@p24 float,@p25 nvarchar(4000),@p26 float,@p27 float,@p28 float,@p29 int,@p30 bigint,@p31 bigint,@p32 nvarchar(4000),@p33 float,@p34 float,@p35 nvarchar(4000),@p36 int,@p37 int,@p38 float,@p39 int,@p40 bigint,@p41 float,@p42 float,@p43 nvarchar(4000),@p44 datetime,@p45 int,@p46 nvarchar(4000),@p47 int,@p48 nvarchar(4000))INSERT INTO [dbo].[PatientBilledItem]([PatientBillUID], [EventOccuredDttm], [ServiceName], [BillableItemUID], [Amount], [CUser], [CWhen], [MUser], [MWhen], [StatusFlag], [OwnerOrganisationUID], [PatientBillableItemUID], [Discount], [NetAmount], [Description], [Comments], [ItemMultiplier], [BSMDDUID], [ItemName], [DiscountAuthorizedBy], [ConsultantShare], [ServiceTax], [CareProviderUID], [QNUOMUID], [Sp </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057699966713856" dbid="9" objectname="HEALTHOBJECT.dbo.PatientBilledItem" indexname="IX_PatientBillableItem" id="lock28bc25a00" mode="RangeS-S" associatedObjectId="72057699966713856">
<owner-list>
<owner id="process18572db848" mode="RangeS-S" />
<owner id="process18572db848" mode="RangeI-N" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="processa19016ca8" mode="RangeI-N" requestType="convert" />
</waiter-list>
</keylock>
<keylock hobtid="72057699966713856" dbid="9" objectname="HEALTHOBJECT.dbo.PatientBilledItem" indexname="IX_PatientBillableItem" id="lock28bc25a00" mode="RangeS-S" associatedObjectId="72057699966713856">
<owner-list>
<owner id="processa19016ca8" mode="RangeS-S" />
<owner id="processa19016ca8" mode="RangeI-N" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process18572db848" mode="RangeI-N" requestType="convert" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
+3
source to share
2 answers
INSERTS alone will not block each other. If you call SELECT WHERE UID=SCOPE_IDENTITY()
in the same transaction, the server will need to acquire a SHARED lock on any indexes that contain UIDs. This is because you were trying to read a value SCOPE_IDENTITY()
that will change when another row is added, resulting in inconsistent data. If you use the REPEATABLE READ isolation level, you may be stumped.
The solution is very simple - don't use a separate SELECT. You can use the OUTPUT INSERT clause to get all the new values like id, defaults, etc:
INSERT INTO [dbo].[PatientBilledItem]
([PatientBillUID], [EventOccuredDttm], [ServiceName], [BillableItemUID], [Amount], [CUser], [CWhen], [MUser], [MWhen], [StatusFlag], [OwnerOrganisationUID], [PatientBillableItemUID], [Discount], [NetAmount], [Description], [Comments], [ItemMultiplier], [BSMDDUID], [ItemName], [DiscountAuthorizedBy], [ConsultantShare], [ServiceTax], [CareProviderUID], [QNUOMUID], [SpecialAmount], [IsRefunded], [ConsultantDiscount], [EducationCess], [HigherEducationCess], [RecordedByUID], [OriginalCreditBillUID], [PatientPackageItemUID], [BatchID], [VATPercentage], [InternalCost], [CalculateTaxOnMRP], [StoreUID], [BillPackageUID], [SplitDiscount], [RSLVLUID], [ParentUID], [PackageItemAmount], [RoundOff], [AuthNo], [ExpiryDttm], [BILGRDUID], [ApprovalCode], [ApprovedBy], [InternalBatchID])
OUTPUT inserted.UID,inserted.Timestamp
VALUES ......
You can combine this with the SNAPSHOT isolation level to reduce transaction blocking.
0
source to share
Based on the information provided, the first assumption is that these deadlocks are caused by one of the following missing indexes
CREATE /*UNIQUE*/ NONCLUSTERED INDEX IUN_PatientBilledItem_UID_#_TIMESTAMP
ON dbo.PatientBilledItem (UID)
INCLUDE(TIMESTAMP)
or
CREATE /*UNIQUE*/ NONCLUSTERED INDEX IUN_PatientBilledItem_UID_TIMESTAMP
ON dbo.PatientBilledItem (UID, TIMESTAMP)
required by the following operators SELECT
:
SELECT [t0].[UID], [t0].[TIMESTAMP]
FROM [dbo].[PatientBilledItem] AS [t0]
WHERE [t0].[UID] = (SCOPE_IDENTITY())
which generate locks RangeS-S
(also because the isolation level is TX SERIALIZABLE
).
Note. Uncomment the keyword /*UNIQUE*/
if the current index has unique values or a unique pair of values (composite index).
0
source to share