SQL Server 2017 - Read-Committed Snapshot isolation update deadlocks

Question

I have a stored procedure that is running concurrently on schedule and is causing 100's of deadlocks a day. The transaction is managed by the application and the datebase is using read-committed snapshot isolation.

The SQL that is deadlocking is

UPDATE dbo.Job
SET IsCalculated = 0
WHERE RfJobStatusID = 2
AND LineID IN (SELECT LineID FROM dbo.Line WHERE PlantID = @nJobPlantID)
AND IsCalculated = 1;
giving plan

The deadlock graph is

Deadlock XML is

xml_report <deadlock> <victim-list> <victimProcess id="process1a740339468"/> </victim-list> <process-list> <process id="process1a740339468" taskpriority="5" logused="8180" waitresource="KEY: 6:72057594300858368 (a565e7579a1e)" waittime="3034" ownerId="68453613031" transactionname="user_transaction" lasttranstarted="2022-08-19T12:17:54.910" XDES="0x1a2932bc428" lockMode="X" schedulerid="2" kpid="7176" status="suspended" spid="53" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2022-08-19T12:17:54.917" lastbatchcompleted="2022-08-19T12:17:54.913" lastattention="1900-01-01T00:00:00.913" clientapp="Framework Microsoft SqlClient Data Provider" hostname="HSCHT2409" hostpid="1672" loginname="sfol_qasp10" isolationlevel="read committed (2)" xactid="68453613031" currentdb="6" currentdbname="SFOL" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="SFOL.dbo.JobResequence" line="485" stmtstart="56924" stmtend="57904" sqlhandle="0x0300060085ab773fdf91ce00b5ae000001000000000000000000000000000000000000000000000000000000"> unknown </frame> <frame procname="SFOL.dbo.JobReschedule" line="186" stmtstart="27626" stmtend="28404" sqlhandle="0x03000600db843045e4bb37016eae000001000000000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> Proc [Database Id = 6 Object Id = 1160807643] </inputbuf> </process> <process id="process1a2e8a38108" taskpriority="5" logused="1147920" waitresource="KEY: 6:72057594300858368 (4d2035f3e54e)" waittime="3041" ownerId="68453402253" transactionname="user_transaction" lasttranstarted="2022-08-19T12:17:48.217" XDES="0x1a10627c428" lockMode="U" schedulerid="3" kpid="4732" status="suspended" spid="58" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2022-08-19T12:17:48.220" lastbatchcompleted="2022-08-19T12:17:48.220" lastattention="1900-01-01T00:00:00.220" clientapp="Framework Microsoft SqlClient Data Provider" hostname="HSCHT2409" hostpid="1672" loginname="sfol_qasp10" isolationlevel="read committed (2)" xactid="68453402253" currentdb="6" currentdbname="SFOL" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="SFOL.dbo.JobRecalcAllEstimatedDates" line="95" stmtstart="13416" stmtend="13760" sqlhandle="0x03000600a2603c44b5c8c500f6ae000001000000000000000000000000000000000000000000000000000000"> UPDATE dbo.Job SET IsCalculated = 0 WHERE RfJobStatusID = 2 AND LineID IN (SELECT LineID FROM dbo.Line WHERE PlantID = @nJobPlantID) AND IsCalculated = </frame> <frame procname="SFOL.dbo.JobReschedule" line="226" stmtstart="32170" stmtend="32538" sqlhandle="0x03000600db843045e4bb37016eae000001000000000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> Proc [Database Id = 6 Object Id = 1160807643] </inputbuf> </process> </process-list> <resource-list> <keylock hobtid="72057594300858368" dbid="6" objectname="SFOL.dbo.Job" indexname="IM_RfJobStatusID_IsCalculated" id="lock1a210631580" mode="U" associatedObjectId="72057594300858368"> <owner-list> <owner id="process1a2e8a38108" mode="U"/> </owner-list> <waiter-list> <waiter id="process1a740339468" mode="X" requestType="wait"/> </waiter-list> </keylock> <keylock hobtid="72057594300858368" dbid="6" objectname="SFOL.dbo.Job" indexname="IM_RfJobStatusID_IsCalculated" id="lock1a23d8c0d80" mode="X" associatedObjectId="72057594300858368"> <owner-list> <owner id="process1a740339468" mode="X"/> </owner-list> <waiter-list> <waiter id="process1a2e8a38108" mode="U" requestType="wait"/> </waiter-list> </keylock> </resource-list> </deadlock>
Index is

CREATE NONCLUSTERED INDEX IM_RfJobStatusID_IsCalculated ON dbo.Job(RfJobStatusID ASC,IsCalculated ASC) INCLUDE(WOID,EstimatedEndDT,LineID,EstimatedStartDT)
And table structure is

CREATE TABLE dbo.Job(
JobID bigint NOT NULL,
WOID bigint NOT NULL,
RfJobStatusID tinyint NOT NULL,
JobQualityStatusID tinyint NULL,
DeadlineDT datetimeoffset(7) NOT NULL,
ActualStartDT datetimeoffset(7) NOT NULL,
ActualEndDT datetimeoffset(7) NOT NULL,
PlannedStartDT datetimeoffset(7) NOT NULL,
PlannedEndDT datetimeoffset(7) NOT NULL,
PlannedDuration float NOT NULL,
EstimatedStartDT datetimeoffset(7) NOT NULL,
EstimatedEndDT datetimeoffset(7) NOT NULL,
EstimatedDuration float NOT NULL,
ScheduledSequence smallint NOT NULL,
LineID int NOT NULL,
ProductionStartDT datetimeoffset(7) NOT NULL,
ProductionEndDT datetimeoffset(7) NOT NULL,
TargetCycleTime float NULL,
TargetCycleTimeQuantity float NOT NULL,
TargetManPower float NULL,
TargetSetup float NULL,
TargetTearDown float NULL,
TargetFixedTime float NULL,
QuantityOrdered float NOT NULL,
IsCalculated tinyint NOT NULL,
LineGroupID int NOT NULL,
PassName nvarchar(50) NOT NULL,
WorkOrderReleaseStateID int NOT NULL,
ProductionCampaignID bigint NULL,
CampaignSequence smallint NULL,
IsFirstInSequence tinyint NULL,
IsLastInSequence tinyint NULL,
ProductionSequence smallint NULL,
DeadlineLocalDT AS (CONVERT(datetime2,DeadlineDT)) PERSISTED,
ActualStartLocalDT AS (CONVERT(datetime2,ActualStartDT)) PERSISTED,
ActualEndLocalDT AS (CONVERT(datetime2,ActualEndDT)) PERSISTED,
PlannedStartLocalDT AS (CONVERT(datetime2,PlannedStartDT)) PERSISTED,
PlannedEndLocalDT AS (CONVERT(datetime2,PlannedEndDT)) PERSISTED,
EstimatedStartLocalDT AS (CONVERT(datetime2,EstimatedStartDT)) PERSISTED,
EstimatedEndLocalDT AS (CONVERT(datetime2,EstimatedEndDT)) PERSISTED,
ProductionStartLocalDT AS (CONVERT(datetime2,ProductionStartDT)) PERSISTED,
ProductionEndLocalDT AS (CONVERT(datetime2,ProductionEndDT)) PERSISTED,
CONSTRAINT PK_Job PRIMARY KEY CLUSTERED (JobID ASC))
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_RfJobStatusID DEFAULT ((1)) FOR
RfJobStatusID
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_DeadlineDT DEFAULT
(CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR DeadlineDT
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ActualStartDT DEFAULT
(CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR ActualStartDT
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ActualEndDT DEFAULT
(CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR ActualEndDT
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_PlannedStartDT DEFAULT
(CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR PlannedStartDT
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_PlannedEndDT DEFAULT
(CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR PlannedEndDT
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_PlannedDuration DEFAULT ((0)) FOR
PlannedDuration
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_EstimatedStartDT DEFAULT
(CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR EstimatedStartDT
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_EstimatedEndDT DEFAULT
(CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR EstimatedEndDT
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_EstimatedDuration DEFAULT ((0)) FOR
EstimatedDuration
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ScheduledSequence DEFAULT ((0)) FOR
ScheduledSequence
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_LineID DEFAULT ((1)) FOR LineID
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ProductionStartDT DEFAULT
(CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR ProductionStartDT
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ProductionEndDT DEFAULT
(CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR ProductionEndDT
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_TargetCycleTimeQuantity DEFAULT ((1)) FOR
TargetCycleTimeQuantity
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_QuantityOrdered DEFAULT ((0)) FOR
QuantityOrdered
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_IsCalculated DEFAULT ((0)) FOR IsCalculated
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_LineGroupID DEFAULT ((1)) FOR LineGroupID
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_PassName DEFAULT ('1') FOR PassName
GO
ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_WorkOrderReleaseStateID DEFAULT ((1)) FOR
WorkOrderReleaseStateID
GO
ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_Line FOREIGN KEY(LineID)
REFERENCES dbo.Line (LineID)
ON UPDATE CASCADE
GO
ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_Line
GO
ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_LineGroup FOREIGN
KEY(LineGroupID) REFERENCES dbo.LineGroup (LineGroupID) ON UPDATE CASCADE
GO
ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_LineGroup
GO
ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_ProductionCampaign FOREIGN KEY(ProductionCampaignID)
REFERENCES dbo.ProductionCampaign (ProductionCampaignID) `ON UPDATE CASCADE
GO
ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_ProductionCampaign
GO
ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_RfJobStatus FOREIGN KEY(RfJobStatusID) REFERENCES dbo.RfJobStatus (RfJobStatusID)
GO
ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_RfJobStatus
GO
ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_WO FOREIGN KEY(WOID)
REFERENCES dbo.WO (WOID)
GO
ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_WO
GO
ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_WorkOrderReleaseState FOREIGN KEY(WorkOrderReleaseStateID) REFERENCES dbo.WorkOrderReleaseState (WorkOrderReleaseStateID) ON UPDATE CASCADE
GO
ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_WorkOrderReleaseState
GO
Is anyone able to point me in the right direction or give me some hints on how I maybe able to fix or at least reduce the number of deadlocks?

Answer 1

It seems both queries are scanning rows with the same IsCalculated and RfJobStatusID value. Try adding LineID to the existing index (DDL below) to see if the plan improves such that only those rows that need to be updated are touched. Upload the actual execution plan to Paste The Plan and add the link to your question if this doesn't avoid the deadlock.

CREATE NONCLUSTERED INDEX IM_RfJobStatusID_IsCalculated ON dbo.Job(RfJobStatusID ASC,IsCalculated ASC, LineID  ASC) INCLUDE(WOID,EstimatedEndDT,LineID,EstimatedStartDT)  
WITH(DROP_EXISTING=ON)  

You can also specify the ONLINE=ON option to the above if you are using Enterprise Edition.

Answer 2

HI @arsaalandailyUK ,

Please check out this thread, you can change your isolation levels for greater concurrency: SNAPSHOT and READ COMMITTED SNAPSHOT isolation, they are optimistic locking

https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.