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 database 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
[![enter image description here][1]][1]
[![enter image description here][2]][2]
The deadlock graph is
[![enter image description here][3]][3]
[1]: https://i.sstatic.net/AJlhi.jpg
[2]: https://i.sstatic.net/uOTZt.png
[3]: https://i.sstatic.net/us5sw.png
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?