Deadlock Analysis – RockyPC – 2026-09-27

Executive summary

1. Highest impact: establish one acquisition order for every code path. Use Accounts then Orders in DeadlockDemo, Countries then StateProvinces in WideWorldImporters, and dl_a then dl_b in tempdb. This directly removes the circular wait in Deadlocks #1–#5.
2. Remove artificial transaction delays: delete the WAITFOR statements from the impacted transactions. The supplied code holds locks for 3 seconds in Deadlocks #1, #2 and #4, and 10 seconds in Deadlocks #3 and #5.
3. Deploy the complete replacements in the Scripts section: the stored procedures are complete current definitions, and the ad hoc batches are complete where retrieved. The application or operator issuing each ad hoc batch must be changed.
4. Do not treat RCSI as the primary fix: RCSI is disabled, but these cycles are writer-versus-writer conflicts on primary-key keys. Snapshot reads would not remove incompatible update or exclusive locks.

Overall confidence: 99%. The lock owners, waiters, resources and statements form explicit cycles in every supplied graph.

Environment and scope

No row counts, index sizes, execution costs, or query plans were supplied. Recommendations therefore rely on the deadlock graphs and source text, not assumed cardinalities or performance measurements.

Deadlock relationship

Detailed prioritized recommendations

1. Priority 1 — Enforce a single table order across all writers.
   • In Deadlocks #1 and #2, usp_PayOrder and the opposing code paths acquire Accounts and Orders in opposite orders.
   • In Deadlocks #3 and #5, CODE-7 acquires StateProvinces then Countries, while CODE-8 acquires Countries then StateProvinces.
   • In Deadlock #4, CODE-9 acquires dl_a then dl_b, while CODE-10 acquires dl_b then dl_a.
   • This is the direct deadlock prevention mechanism and should be deployed before secondary tuning.
2. Priority 2 — Remove WAITFOR and other non-database work from the transaction.
   • CODE-1 line 8, CODE-5 line 8, CODE-3 line 1 and CODE-9/CODE-10 line 1 hold the first write lock during a 3-second delay.
   • CODE-7 line 9 holds a lock during a 10-second delay. CODE-8 has no delay, but it still participates in the order inversion.
   • The supplied waits are intentional demonstration delays, but equivalent application work, network calls or user interaction must not occur between transaction statements.
3. Priority 3 — Keep the transaction boundary aligned with the atomic business operation.
   • Do not split the paired updates into separate committed transactions if the account/order or country/state-province changes must remain atomic.
   • Instead, perform both updates consecutively in the chosen order and commit immediately.
4. Priority 4 — Add retry handling as resilience, not as the fix.
   • Applications should retry error 1205 with bounded backoff because deadlocks can still arise from unprovided code paths, maintenance, triggers or future changes.
   • Retry logic does not remove the demonstrated cycles; it only reduces user-visible failure after prevention changes.
5. Priority 5 — Avoid unnecessary isolation escalation.
   • The graphs report read committed isolation and writer locks. No SERIALIZABLE or REPEATABLE READ issue is shown.
   • RCSI may reduce reader/writer blocking, but enabling it is not required to fix these writer/writer cycles and is not recommended as the primary change based on this evidence.

Deadlocks #1 and #2 — DeadlockDemo

Confidence 99% Both graphs show a two-table order inversion involving exclusive key locks on the primary keys of Orders and Accounts.

Deadlock #1

• CODE-1 line 7 takes an exclusive lock on Accounts; CODE-1 line 9 requests an exclusive lock on Orders.
• CODE-3 line 1 first updates Orders, then requests Accounts after the 3-second WAITFOR.
• The graph shows process 52 owning the Accounts key and waiting for Orders, while process 53 owns Orders and waits for Accounts.

Deadlock #2

• CODE-1 line 7 takes the Accounts lock and line 9 requests Orders.
• CODE-5 line 7 takes the Orders lock and line 9 requests Accounts.
• The procedure name in CODE-5 is intentionally bracketed because its object name is dbo.[usp_Refund.Order].

Impacted code and rewritten code

CODE-1, lines 7–9: rewrite the procedure so its updates remain atomic but use the common Accounts-then-Orders order without the delay. The rewrite removes the Orders-then-Accounts conflicting acquisition path.

CODE-5, lines 7–9: rewrite the refund procedure to use the same Accounts-then-Orders order. The arithmetic and affected rows remain unchanged; only acquisition order and transaction duration change.

CODE-2, line 1, CODE-4, line 1 and CODE-6, line 1: the procedure invocations do not need a semantic change. The application batches must continue calling the revised procedures.

Deadlocks #3 and #5 — WideWorldImporters

Confidence 99% The same complete CODE-7 and CODE-8 batches appear in both graphs. The graphs show update-lock requests on the primary keys of Application.Countries and Application.StateProvinces.

• CODE-7 lines 3–6 updates StateProvinces first; line 9 holds the transaction for 10 seconds; lines 12–15 then update Countries.
• CODE-8 lines 3–6 updates Countries first; lines 9–12 then update StateProvinces.
• The graph reports process A owning StateProvinces and waiting for Countries, while process B owns Countries and waits for StateProvinces.

Impacted code and rewritten code

CODE-7, lines 3–15: change the first update to Countries, then update StateProvinces, and remove the 10-second delay.

CODE-8, lines 3–12: retain its Countries-then-StateProvinces order and remove only comments that describe the obsolete opposite-order assumption. This eliminates the conflicting lock order with CODE-7 while preserving both population increments and timestamp assignments.

Deadlock #4 — tempdb

Confidence 99% CODE-9 and CODE-10 are complete input-buffer batches and deliberately update the two tables in reverse order.

• CODE-9 line 1 takes an exclusive lock on dbo.dl_a, waits 3 seconds, then requests an update/exclusive lock on dbo.dl_b.
• CODE-10 line 1 takes an exclusive lock on dbo.dl_b, waits 3 seconds, then requests dbo.dl_a.
• Use dl_a-then-dl_b for both batches and remove the delay. The values assigned by each session remain different.

The application or test harness that issues CODE-9 and CODE-10 must be changed. No index change is indicated: the graph already identifies direct key locks on the two primary-key resources, and the cycle is caused by ordering.

Scripts

Recommendation 1: replace usp_PayOrder with Accounts-then-Orders ordering

USE [DeadlockDemo];
GO

CREATE OR ALTER PROCEDURE [dbo].[usp_PayOrder]
    @id int
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRAN;

    UPDATE dbo.Accounts
    SET Balance = Balance - 1
    WHERE AccountId = @id;

    UPDATE dbo.Orders
    SET Amount = Amount + 1
    WHERE OrderId = @id;

    COMMIT;
END;
GO

Recommendation 1: replace usp_Refund.Order with the same Accounts-then-Orders ordering

USE [DeadlockDemo];
GO

CREATE OR ALTER PROCEDURE [dbo].[usp_Refund.Order]
    @id int
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRAN;

    UPDATE dbo.Accounts
    SET Balance = Balance + 1
    WHERE AccountId = @id;

    UPDATE dbo.Orders
    SET Amount = Amount - 1
    WHERE OrderId = @id;

    COMMIT;
END;
GO

Recommendation 1 and 2: revised CODE-3 application batch using the common order without WAITFOR

USE [DeadlockDemo];
GO

BEGIN TRAN;

UPDATE dbo.Accounts
SET Balance = Balance + 5
WHERE AccountId = 2;

UPDATE dbo.Orders
SET Amount = Amount - 5
WHERE OrderId = 2;

COMMIT;
GO

The application code that issues CODE-3 must be changed. CODE-2, CODE-4 and CODE-6 can continue issuing their procedure calls after the procedure replacements are deployed.

Recommendation 1 and 2: revised CODE-7 batch using Countries-then-StateProvinces ordering

USE [WideWorldImporters];
GO

BEGIN TRAN;

UPDATE Application.Countries
SET LatestRecordedPopulation = LatestRecordedPopulation + 1,
    ValidFrom = SYSUTCDATETIME()
WHERE IsoNumericCode = 840;

UPDATE Application.StateProvinces
SET LatestRecordedPopulation = LatestRecordedPopulation + 1,
    ValidFrom = SYSUTCDATETIME()
WHERE StateProvinceCode = N'VA';

COMMIT;
GO

The application or operator issuing CODE-7 must be changed. The batch preserves both updates and their original values.

Recommendation 1 and 2: normalized CODE-8 batch using the same order

USE [WideWorldImporters];
GO

BEGIN TRAN;

UPDATE Application.Countries
SET LatestRecordedPopulation = LatestRecordedPopulation + 1,
    ValidFrom = SYSUTCDATETIME()
WHERE IsoNumericCode = 840;

UPDATE Application.StateProvinces
SET LatestRecordedPopulation = LatestRecordedPopulation + 1,
    ValidFrom = SYSUTCDATETIME()
WHERE StateProvinceCode = N'VA';

COMMIT;
GO

The application or operator issuing CODE-8 must be changed. Its data effect is unchanged.

Recommendation 1 and 2: revised CODE-9 tempdb batch using dl_a-then-dl_b ordering

USE [tempdb];
GO

BEGIN TRAN;

UPDATE dbo.dl_a
SET v = 1;

UPDATE dbo.dl_b
SET v = 1;

COMMIT;
GO

The application or test harness issuing CODE-9 must be changed.

Recommendation 1 and 2: revised CODE-10 tempdb batch using dl_a-then-dl_b ordering

USE [tempdb];
GO

BEGIN TRAN;

UPDATE dbo.dl_a
SET v = 2;

UPDATE dbo.dl_b
SET v = 2;

COMMIT;
GO

The application or test harness issuing CODE-10 must be changed.

Confidence and limitations

• Root-cause confidence: 99%. Every graph contains a complete two-resource cycle, and the source lines identify reverse acquisition order.
• Recommendation confidence: 99%. A consistent order prevents the specific cycles shown, provided all writers touching these resource pairs follow that order.
• Performance confidence: 95%. Removing the supplied delays will shorten lock duration, but the input does not provide measured execution durations outside the explicit 3-second and 10-second waits.
• Index recommendation confidence: not applicable. No new index is required to remove the demonstrated cycles. The graphs identify key locks on primary-key indexes, and no scan, escalation, missing-index or plan-cost evidence was supplied.
• Coverage limitation: the supplied deadlock history contains five graphs, but only the five shown were analyzed. Statistics and plan-cache data are limited to the stated 37.5-hour post-startup period.