0

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

enter image description here

The deadlock graph is

enter image description here

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?

Improve this question
4
  • 1
    Please share all of the statements that make up the transaction
    – Andrew Sayer
    Commented Aug 19, 2022 at 16:15
  • There are 7 nested stored procedures with numerous cursors in the transaction. I have discovered that although we are using rsci the top procedure is setting the isolation to repeatable read. This I think is the reason for the deadlock due to shared locks being held until the end end of the transaction. I think the fix maybe to use an updlock hint when reading the data initially. I will update whether this fixes the problem.
    – Mark Gibson
    Commented Aug 21, 2022 at 19:24
  • I don't think such a complex problem with so much missing information is going to be answerable. There are too many moving parts to be able to properly diagnose this without having access to the actual machine. All I can say is: 7 nested procedures is already a complex beast, and cursors are a complete nightmare for performance. This is going to be compounded if you are using them in transactions. Even if we solve your current deadlock problem (likely caused by that big hash join), it will just pop up somewhere else.
    – Charlieface
    Commented Aug 21, 2022 at 20:12
  • I agree. This requires a major refactor of the code to shorten the transactions as currently spanning many reads (inc cursors) and not just the data changes. I guess I was thrown by the fact that the stored procedures that are deadlocking are running in repeatable read isolation and not read committed snapshot isolation like the rest of the system. I will speak to the developers to find out why this was done and if it is needed. At least that may reduce the deadlocks but the refactor is still going to be required.
    – Mark Gibson
    Commented Aug 22, 2022 at 9:24

1 Answer 1

Reset to default
0

"Repeatable Read" is giving you the 'U' locks instead of shared ('S') locks. I don't suggest changing a non-default isolation level without deep understanding of why it was set the way it was. (there is likely a story behind it.) RCSI may mask the problem and "fix it", but I'd recommend that being your last resort, as you can subtly break things.

Looking at the plan, it starts with reading the Line table, then reads ALL the rows from IM_RfJobStatusID_IsCalculated where "RfJobStatusID = 2" and "IsCalculated = 1" and hash joins then together.

Update ("U") locks are taken when the rows are read from the index. They are converted to "X" locks when/if the row is updated.

Note: In your case, "U" locks are taken w/o regard to the join on the Line table. If the join was a Loop Join instead of a Hash join, then less rows would be locked and chances of deadlocks would decrease.

In overly simplistic terms, you're getting this:

  • session 1: UPDATE.. where PlantID = 1
    • 'U' locks on all rows
    • 'X' locks on rows where PlantID = 1
  • session 2: UPDATE.. where PlantID = 2
    • 'U' locks on all rows -- conflicts!
    • 'X' locks on rows where PlantID = 2

when you want this:

  • session 1: UPDATE.. where PlantID = 1
    • 'X' locks on rows where PlantID = 1
    • 'U' locks on all where PlantID = 1
  • session 2: UPDATE.. where PlantID = 2
    • 'X' locks on rows where PlantID = 2
    • 'U' locks on rows where PlantID = 2 -- no conflict!

First I'd try a filtered index. CREATE INDEX IX_XXX on Job (LineId) WHERE RfJobStatusID = 2 AND IsCalculated = 1; This should give you a Loop Join along with a much smaller number of 'U' locks. If this doesn't work, then a query hint may be required.

It's still possible for deadlocks if two sessions concurrently execute the UPDATE for the same @nJobPlantID, nothing you can really do in that case.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.