Deadlock Analysis – RockyPC – 2026-09-29

Executive summary

The five supplied graphs show two repeated application/job patterns rather than random engine contention. Deadlocks #1 and #2 are caused by opposite update order on tempdb.dbo.##dl_a and tempdb.dbo.##dl_b. Deadlocks #3 and #4 are caused by opposite update order on DeadlockDemo.dbo.Accounts and DeadlockDemo.dbo.Orders. Deadlock #5 is caused by opposite update order on WideWorldImporters.Application.Countries and WideWorldImporters.Application.StateProvinces.

Environment and evidence

• SQL Server started at 2026-09-29 10:45:10 server local time; reported uptime was 28 minutes.
• Deadlock frequency was 2 deadlocks in the 2026-09-29 15:00 UTC bucket and 3 deadlocks in the 2026-09-26 09:00 UTC bucket.
• RCSI is disabled. All supplied processes used read committed isolation, but the relevant waits are update or exclusive locks from modifications.
• The SQL Server version is SQL Server 2022 RTM-GDR, version 16.0.1200.5. CREATE OR ALTER PROCEDURE is supported on this engine.
• Index, operational-statistics, missing-index, and plan-cache observations cover only the 28-minute post-startup period. No index statistics, row counts, execution plans, or object definitions beyond the supplied code are available.

Deadlock map

Detailed prioritized recommendations

1. Enforce a single resource order across all code paths

Impact: critical. For the temporary-table pattern, use ##dl_a before ##dl_b. For DeadlockDemo, use Accounts before Orders. For WideWorldImporters, use Countries before StateProvinces. This removes the circular wait rather than merely reducing its probability.

Every code path, scheduled job, test harness, and application transaction that touches the same pair must follow the same order. A retry policy may still be useful for other transient deadlocks, but it is not the primary fix for these deterministic cycles.

2. Remove WAITFOR and other non-database work from open transactions

Impact: high. CODE-1 and CODE-3 hold the first key while waiting 5 and 2 seconds respectively. CODE-5 and CODE-9 hold their first application-table key while waiting 3 seconds. CODE-11 explicitly holds a StateProvinces lock during a 10-second delay. The graphs report wait times of 2674 ms and 3511 ms for Deadlock #1, 1033 ms and 1034 ms for Deadlock #2, 1576 ms and 761 ms for Deadlock #3, 1470 ms and 655 ms for Deadlock #4, and 319 ms and 7896 ms for Deadlock #5.

Removing these test delays preserves the table updates and transaction atomicity. If a real business pause is required, perform it before the transaction or after commit, not while locks are held.

3. Keep transactions narrow and add error-safe transaction handling in production modules

Impact: high. The supplied procedures begin and commit transactions internally. The deadlock graphs show trancount="2", so the callers had an existing transaction level or nested transaction context when the graph was captured. SQL Server nested transactions do not independently release locks at an inner commit. Production implementations should preserve the caller's transaction contract and use error handling appropriate to the surrounding application architecture.

The revised definitions in the Scripts section retain the existing data effects and transaction boundaries shown in the supplied modules while eliminating the opposing order and delay. No additional error-handling behavior is invented because the input does not define the application's transaction ownership contract.

4. Do not use RCSI as the primary remedy for these graphs

RCSI is disabled, but enabling it would primarily reduce reader-versus-writer blocking. These graphs show X-versus-X conflicts in Deadlocks #1–#4 and U-versus-X conflicts in Deadlock #5. The deadlock cycles are caused by writers acquiring resources in different orders; row-versioned reads would not make those writer locks compatible.

5. Do not add indexes based on this evidence alone

The predicates are equality predicates on the shown key columns: id, OrderId, AccountId, IsoNumericCode, and StateProvinceCode. The deadlock resources identify primary-key indexes for the application tables. The input provides no row counts, plans, index definitions for the temporary tables beyond their primary keys, or scan evidence. An index recommendation would therefore be speculative and would not remove the demonstrated cycle.

Deadlocks #1–#2: tempdb global temporary tables

Root cause: two transactions update the same two global temporary tables in opposite order. In Deadlock #1, the SQL Agent process owns an X lock on ##dl_a and waits for ##dl_b, while OrderService owns an X lock on ##dl_b and waits for ##dl_a. Deadlock #2 reverses the participating hosts/jobs but shows the same cycle.

Impacted code and conflicting statements

• CODE-1 (ad hoc batch from app "OrderService" on host WEB01): line 1 updates ##dl_b first and later updates ##dl_a. The line-1 statement takes the X lock on ##dl_b.
• CODE-2 (ad hoc batch from SQL Agent job d112d1ee-79ca-43f2-b58f-386eaebc3f09, step 1): line 2 updates ##dl_a; line 4 updates ##dl_b. The line-2 statement takes the X lock on ##dl_a, and line 4 is the conflicting waiter.
• CODE-3 (ad hoc batch from app "OrderService" on host TESTHOST-12): line 2 updates ##dl_b; line 4 updates ##dl_a. These are the same reversed order as CODE-1.
• CODE-4 (ad hoc batch from SQL Agent job "syspolicy_purge_history", step 1 "Verify that automation is enabled."): line 2 updates ##dl_a; line 4 updates ##dl_b.

The rewrite removes the lock conflict by making every transaction acquire ##dl_a before ##dl_b and by removing the deliberate delay between locks.

Deadlocks #3–#4: DeadlockDemo Accounts and Orders

Root cause: dbo.usp_PayOrder acquires Accounts before Orders, while the direct CODE-7 transaction acquires Orders before Accounts. Deadlock #4 shows the same conflict between usp_PayOrder and usp_Refund.Order; the latter uses the opposite order.

Impacted code and conflicting statements

• CODE-5 (module [DeadlockDemo].[dbo].[usp_PayOrder]): line 7 takes the Accounts X lock; line 9 requests the Orders X lock. Line 9 is the deadlock frame cited in Deadlocks #3 and #4.
• CODE-7 (ad hoc batch from app "SQLCMD" on host ROCKYPC): line 1 contains the direct transaction that updates Orders before Accounts. Its first update takes the Orders X lock and its later Accounts update waits.
• CODE-9 (module [DeadlockDemo].[dbo].[usp_Refund.Order]): line 7 takes the Orders X lock; line 9 requests the Accounts X lock. Line 9 is the deadlock frame cited in Deadlock #4.
• CODE-6 (ad hoc batch from app "SQLCMD" on host ROCKYPC) and CODE-8 (ad hoc batch from app "SQLCMD" on host ROCKYPC) invoke usp_PayOrder. Their invocation lines do not acquire the conflicting table locks; no caller rewrite is needed beyond deploying the corrected module.
• CODE-10 (ad hoc batch from app "SQLCMD" on host ROCKYPC) invokes usp_Refund.Order. Its invocation line does not acquire the table locks; the module rewrite is the required change.

The rewrite makes both stored procedures and the direct transaction use Accounts first, then Orders, and removes the 3-second waits. The arithmetic remains unchanged: PayOrder changes the account by -1 and the order by +1; Refund changes the account by +1 and the order by -1; CODE-7 changes the order by -5 and the account by +5.

Deadlock #5: WideWorldImporters Countries and StateProvinces

Root cause: SPID 61, CODE-11, updates Application.StateProvinces first and then requests an update lock on Application.Countries. SPID 63, CODE-12, updates Countries first and then requests an update lock on StateProvinces. Each session owns an X lock on its first resource and waits for a U lock on the second.

Impacted code and conflicting statements

• CODE-11 (ad hoc batch from app "Microsoft SQL Server Management Studio - Query" on host ROCKYPC): line 3 begins the StateProvinces update and line 12 requests the Countries update lock. Line 12 is the deadlock frame.
• CODE-12 (ad hoc batch from app "Microsoft SQL Server Management Studio - Query" on host ROCKYPC): line 3 begins the Countries update and line 9 requests the StateProvinces update lock. Line 9 is the deadlock frame.

The rewrite makes both sessions update Countries first and StateProvinces second. It also removes the 10-second delay in CODE-11, shortening lock duration without changing either update's values or the transaction's atomicity. These batches are test or ad hoc code; the revised batches must be issued by the SSMS user or replaced in the test harness that executed them.

Scripts

Run or deploy the scripts in the order shown. Ad hoc batch replacements must be made in the named SQL Agent job step, application code, SQLCMD test harness, or SSMS test batch identified in each heading. The stored procedure scripts are complete replacement definitions for the complete modules supplied in the input.

1. Standardize tempdb global temporary-table update order for OrderService — implements recommendation 1 and 2

Change the application batch issued by OrderService. This version uses ##dl_a before ##dl_b and removes the delay.

USE [tempdb];
GO

BEGIN TRAN;
UPDATE ##dl_a SET v = v + 1 WHERE id = 1;
UPDATE ##dl_b SET v = v + 1 WHERE id = 1;
COMMIT;
GO

2. Standardize tempdb global temporary-table update order for SQL Agent job d112d1ee-79ca-43f2-b58f-386eaebc3f09, step 1 — implements recommendation 1 and 2

Replace the SQL Agent job step batch. The job was not found in msdb in the supplied evidence and may have been deleted.

USE [tempdb];
GO

BEGIN TRAN;
UPDATE ##dl_a SET v = v + 1 WHERE id = 1;
UPDATE ##dl_b SET v = v + 1 WHERE id = 1;
COMMIT;
GO

3. Standardize tempdb global temporary-table update order for OrderService on TESTHOST-12 — implements recommendation 1 and 2

Change the application batch issued by OrderService on host TESTHOST-12.

USE [tempdb];
GO

BEGIN TRAN;
UPDATE ##dl_a SET v = v + 1 WHERE id = 1;
UPDATE ##dl_b SET v = v + 1 WHERE id = 1;
COMMIT;
GO

4. Standardize tempdb global temporary-table update order for SQL Agent job syspolicy_purge_history, step 1 — implements recommendation 1 and 2

Replace the T-SQL in SQL Agent job syspolicy_purge_history, step 1, “Verify that automation is enabled.”

USE [tempdb];
GO

BEGIN TRAN;
UPDATE ##dl_a SET v = v + 1 WHERE id = 1;
UPDATE ##dl_b SET v = v + 1 WHERE id = 1;
COMMIT;
GO

5. Deploy a consistent Accounts-then-Orders PayOrder procedure — implements recommendation 1 and 2

Replace module [DeadlockDemo].[dbo].[usp_PayOrder]. The original module was complete in CODE-5.

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

6. Deploy a consistent Accounts-then-Orders Refund procedure — implements recommendation 1 and 2

Replace module [DeadlockDemo].[dbo].[usp_Refund.Order]. The original module was complete in CODE-9.

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

7. Standardize the direct DeadlockDemo transaction — implements recommendation 1 and 2

Replace the SQLCMD or test-harness batch represented by CODE-7. This is an ad hoc batch and must be changed in the SQLCMD invocation or test harness.

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

8. Standardize the WideWorldImporters Countries-first transaction — implements recommendation 1 and 2

Replace the SSMS or test-harness batch represented by CODE-11. This batch must be changed in the application, test harness, or SSMS script that issued it.

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

9. Standardize the second WideWorldImporters transaction — implements recommendation 1

Replace the SSMS or test-harness batch represented by CODE-12. This batch already uses the desired order; the complete replacement makes the intended ordering explicit and removes the test-only comments.

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

Confidence and limitations

The report does not infer row counts, data sizes, transaction costs, or post-change percentages because those measurements are absent from the input. Plan-cache and index-statistics conclusions are additionally limited to the reported 28-minute uptime window.