Return to Question

add some tag

Link

edit approved Aug 21, 2022 at 14:07

rozwojoprogramowania.pl

sql-server deadlock

Source Link

asked Aug 19, 2022 at 10:46

Mark Gibson

SQL Server 2017 - Read-Committed Snapshot isolation update deadlocks

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

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?

deadlock