SQL Server deadlock report

Deadlock Analysis – VMEnt\SQL25 – 2026-11-13

Tuning goal: Fix Deadlocks
ServerVMEnt\SQL25
EngineSQL Server 2025 RTM 17.0.1000.7
EditionEnterprise Developer / Enterprise engine
DatabaseSalesDemo, database ID 16
Observed events10 deadlocks
UTC scope2026-11-10 and 2026-11-13

Executive summary

Root cause: the payment and refund transactions acquire write locks in opposite table order. Refund processing updates SalesDemo.dbo.Orders before SalesDemo.dbo.Accounts, while payment processing updates Accounts before Orders. The result is a two-resource X-to-X cycle on the primary keys.

Deadlocks #1–#4, #7 and #10 show the direct cycle: one transaction owns an X key lock on Accounts and waits for Orders, while the other owns an X key lock on Orders and waits for Accounts. Deadlocks #5, #6, #8 and #9 add a second manifestation: payment processing holds an X lock on an account and then takes an S lock while scanning Orders; refund processing holds an IX page lock on Orders and waits for that account key.

  1. Standardize transaction order. Change both procedures to acquire the account row first and then the order row. This directly removes the circular wait in all ten graphs.
  2. Reduce the payment order lookup locks. Review an index on SalesDemo.dbo.Orders keyed by (AccountId, Status) and including Amount. Run the Index Tuning goal before implementing any physical design change; existing indexes are not supplied.
  3. Enable optimized locking after ADR. SalesDemo has ADR OFF and optimized locking OFF on SQL Server 2025. Enabling ADR followed by optimized locking can reduce row/page locks held to transaction end, but it is secondary to correcting lock order and does not solve opposite-order writer cycles by itself.
  4. Consider RCSI for the read component. RCSI is OFF for SalesDemo, and the payment subquery is taking an S lock under READ COMMITTED. Enabling RCSI can remove that reader S-lock contribution, subject to application testing and version-store capacity. It will not prevent writer-versus-writer deadlocks.
  5. Retain safe 1205 retry handling. The SQL Agent batches already catch error 1205, but they immediately retry without a randomized pause. Add a short randomized delay only when the transaction is safe to rerun. This limits impact; it does not remove the deadlock cause.

Observed frequency: five deadlocks were recorded in the 12:00 UTC bucket on 2026-11-13 and five in the 20:00 UTC bucket on 2026-11-10. The supplied data supports 10 events, not a broader rate estimate.

Scope and evidence

EvidenceFinding
Deadlock graphs10 graphs, each with two processes and two resources.
VictimsOne victim per graph; the victim log used values supplied are 284, 404 or 264.
IsolationAll shown processes use READ COMMITTED.
SalesDemo settingsRCSI OFF; snapshot isolation OFF; ADR OFF; optimized locking OFF.
Code sourceusp_RefundOrder and usp_PayOrder are complete current definitions from sys.sql_modules. The two ad hoc batches are complete enough for the supplied input buffer, but the graph notes that SQL Server can cut long input buffers short.
Database ownershipSalesDemo owns the locked resources in deadlocks #1–#10, so its settings govern all analyzed locks.
Opposite table order creates a deadlock cycle Refund owns an Orders lock and waits for Accounts. Payment owns an Accounts lock and waits for Orders. Refund transaction X Orders → waits for X Accounts Payment transaction X Accounts → waits for X Orders waits for Accounts waits for Orders
Figure 1. The blocking relationship represented by deadlocks #1–#4, #7 and #10. The rewrite makes both transactions use Accounts → Orders.

Detailed prioritized recommendations

  1. Priority 1 — Make all payment and refund transactions use Accounts → Orders

    Impact: highest. Evidence: six graphs contain reciprocal X key waits between SalesDemo.dbo.Accounts / PK_Accounts and SalesDemo.dbo.Orders / PK_Orders. Refund line 8 in CODE-1 modifies Orders first; refund line 11 modifies Accounts later. Payment lines 9, 11 and 15 in CODE-3 modify Accounts before Orders.

    Deploy the revised definitions in the Scripts section. The refund procedure first obtains the order's account and amount, then begins its transaction and locks the account before modifying the order. The payment procedure already begins with the account and retains that order, but its account update and order work are made explicit in the same ordering pattern.

    This removes the cycle rather than merely changing the victim. It preserves the existing balance, status, payment, timestamp and open-order-total effects.

  2. Priority 2 — Tune the Orders access used by the open-order aggregate

    Evidence: deadlocks #5, #6, #8 and #9 show a PAGE IX lock on SalesDemo.dbo.Orders page 8782 and an S waiter from the correlated aggregate at CODE-3 (module SalesDemo.dbo.usp_PayOrder, lines 11–13). The predicate is AccountId = @AccountId and Status IN ('Open','Refunded'), and the value returned is Amount.

    Use the Index Tuning goal for SalesDemo.dbo.Orders, comparing existing indexes and workload, to evaluate a nonclustered access path with key columns (AccountId, Status) and included column Amount. Do not create a duplicate index without checking existing definitions, write cost and usage statistics. The input provides no existing-index inventory, cardinalities or execution plan.

    This can reduce scans, page locks and the duration of the S lock. It is not a substitute for consistent table order.

  3. Priority 3 — Enable ADR and optimized locking for SalesDemo

    Evidence: SalesDemo has ADR OFF and optimized locking OFF, while SQL Server 2025 Enterprise capabilities are available. Optimized locking can reduce row and page locks held until transaction end, which is relevant to the PAGE IX/S pattern in deadlocks #5, #6, #8 and #9.

    Run the two database-setting batches in order during a maintenance window. Each setting change requires no other active connections to SalesDemo; the command can wait until connections disconnect. ADR keeps row versions, including versions used by RCSI or snapshot isolation, in the persistent version store inside SalesDemo instead of tempdb, so its size must be monitored.

    This does not remove the opposite-order writer cycle or deadlocks between transactions modifying the same rows in opposite order. It also does not override REPEATABLE READ, SERIALIZABLE or lock hints; none are shown in these graphs.

  4. Priority 4 — Consider READ_COMMITTED_SNAPSHOT for SalesDemo

    Evidence: RCSI is OFF for SalesDemo. In deadlocks #5, #6, #8 and #9, the payment statement at CODE-3 (module SalesDemo.dbo.usp_PayOrder, lines 11–13) requests an S lock while reading Orders under READ COMMITTED. With RCSI enabled, ordinary READ COMMITTED reads of SalesDemo data use row versions instead of S locks.

    RCSI will not help writer-versus-writer deadlocks such as #1–#4, #7 and #10, and it will not help reads under stronger isolation or explicit locking hints. It changes readers to see the last committed version rather than wait for a writer, so application behavior must be tested. It consumes version-store space; with ADR enabled, the persistent version store is inside SalesDemo. The change requires no other active connections to SalesDemo.

  5. Priority 5 — Add safe randomized retry handling

    The SQL Agent job steps represented by CODE-2 (ad hoc batch from SQL Agent job AI SQL Tuner Demo - Deadlock refunds, step 1) and CODE-4 (ad hoc batch from SQL Agent job AI SQL Tuner Demo - Deadlock payments, step 1) already catch error 1205 and roll back. They immediately continue the loop. Add a short randomized pause before retrying, and retry only if the procedure's transaction is safe to rerun. This reduces repeated collision probability but does not reduce the underlying lock cycle.

    For background work, SET DEADLOCK_PRIORITY LOW can make it the preferred victim instead of user-facing work. The current batches set NORMAL. Priority and retry handling limit impact; they do not prevent deadlocks.

Deadlock pattern summary

GraphsPatternLocks and statementsPrimary fix
#1–#4, #7, #10Opposite-order writer cycleRefund owns X on Orders and waits for X on Accounts. Payment owns X on Accounts and waits for X on Orders.Use Accounts → Orders in both procedures.
#5, #6, #8, #9Account writer versus Orders page readPayment aggregate requests S on Orders page 8782 while refund owns IX on that page; refund waits for the Accounts X key held by payment.Use common order; tune Orders access; optionally use RCSI and optimized locking for SalesDemo.

Not applicable: lock escalation is not demonstrated. The graphs show key and page locks, not OBJECT or HOBT-level escalated S/X locks, and no lock count is supplied. Do not apply LOCK_ESCALATION changes based on this evidence.

Not applicable: there are no xactlock resources. Optimized locking does not solve a transaction-ID deadlock between writers modifying the same rows in opposite order; the transaction order and duration remain the fix.

Detailed deadlock findings

Deadlocks #1–#4, #7 and #10 — opposite-order X key locks

Confidence: 99% These graphs all show two KEY resources owned by SalesDemo: PK_Accounts and PK_Orders. The refund process waits for an X lock on Accounts while holding Orders; the payment process waits for an X lock on Orders while holding Accounts. Deadlock #1, for example, records the refund UPDATE at CODE-1 (module SalesDemo.dbo.usp_RefundOrder, line 11) waiting on Accounts and the payment UPDATE at CODE-3 (module SalesDemo.dbo.usp_PayOrder, line 15) waiting on Orders.

The same code is implicated in all six graphs. The victim varies, but the cycle does not.

Deadlocks #5, #6, #8 and #9 — page-level Orders read/write conflict

Confidence: 97% The payment aggregate at CODE-3 (module SalesDemo.dbo.usp_PayOrder, lines 11–13) reads Orders while the payment transaction already holds an account X lock. With RCSI OFF for SalesDemo, the READ COMMITTED read takes an S lock. The graphs show that S request waiting on page 8782, while the refund process owns an IX page lock and waits for the Accounts X key held by payment. The primary cycle is still caused by inconsistent table order; the page access path increases the opportunity and lock footprint.

Deadlock frequency and operational context

Five events occurred in the 12:00 UTC bucket on 2026-11-13 and five in the 20:00 UTC bucket on 2026-11-10. The supplied input does not establish whether the two SQL Agent jobs overlap on a recurring schedule, but the graphs identify the payment and refund job steps as the concurrent participants. The deleted-job identifiers in deadlocks #6–#10 cannot be mapped to current msdb job metadata from the supplied data.

Impacted code rewrites

Refund procedure — CODE-1 (SalesDemo.dbo.usp_RefundOrder)

Conflicting statements are CODE-1 (module SalesDemo.dbo.usp_RefundOrder), line 8, which takes the Orders X lock first, and line 11, which then takes the Accounts X lock. The rewrite obtains the account and amount before the transaction, then takes the Accounts X lock before changing Orders. This removes the refund-side Orders → Accounts edge while preserving the refund status, payment row and balance update.

Payment procedure — CODE-3 (SalesDemo.dbo.usp_PayOrder)

Conflicting statements are CODE-3 (module SalesDemo.dbo.usp_PayOrder), lines 9 and 11–13 on Accounts, followed by line 15 on Orders. The rewrite keeps this Accounts → Orders order and makes the transaction structure explicit. It removes the mismatch with the revised refund procedure. The aggregate remains before the status change, preserving the original open-order-total result.

Retry batches — CODE-2 and CODE-4

CODE-2 (ad hoc batch from SQL Agent job AI SQL Tuner Demo - Deadlock refunds, step 1) and CODE-4 (ad hoc batch from SQL Agent job AI SQL Tuner Demo - Deadlock payments, step 1) call the procedures at line 9. They do not acquire the table locks themselves. Their change is operational: add randomized delay after a 1205 rollback and, if appropriate for background processing, lower deadlock priority. The SQL Agent job steps must be changed; no application host code is identified.

Scripts

Replace usp_RefundOrder with consistent Accounts → Orders locking order

USE [SalesDemo];
GO

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

    DECLARE @AccountId int,
            @Amount decimal(12,2);

    SELECT
        @AccountId = AccountId,
        @Amount = Amount
    FROM dbo.Orders
    WHERE OrderId = @OrderId;

    BEGIN TRANSACTION;

        UPDATE dbo.Accounts
        SET Balance = Balance + @Amount,
            UpdatedAt = SYSDATETIME()
        WHERE AccountId = @AccountId;

        UPDATE dbo.Orders
        SET Status = 'Refunded',
            UpdatedAt = SYSDATETIME()
        WHERE OrderId = @OrderId;

        INSERT dbo.Payments
            (OrderId, Amount, Kind, CreatedAt)
        VALUES
            (@OrderId, -@Amount, 'Refund', SYSDATETIME());

    COMMIT;
END;
GO

Replace usp_PayOrder with the same Accounts → Orders locking order

USE [SalesDemo];
GO

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

    DECLARE @AccountId int,
            @Amount decimal(12,2);

    SELECT
        @AccountId = AccountId,
        @Amount = Amount
    FROM dbo.Orders
    WHERE OrderId = @OrderId;

    BEGIN TRANSACTION;

        UPDATE dbo.Accounts
        SET Balance = Balance - @Amount,
            UpdatedAt = SYSDATETIME()
        WHERE AccountId = @AccountId;

        -- Keep the account page's open-order total current.
        UPDATE dbo.Accounts
        SET OpenOrderTotal =
        (
            SELECT SUM(o.Amount)
            FROM dbo.Orders AS o
            WHERE o.AccountId = @AccountId
              AND o.Status IN ('Open', 'Refunded')
        )
        WHERE AccountId = @AccountId;

        INSERT dbo.Payments
            (OrderId, Amount, Kind, CreatedAt)
        VALUES
            (@OrderId, @Amount, 'Payment', SYSDATETIME());

        UPDATE dbo.Orders
        SET Status = 'Paid',
            UpdatedAt = SYSDATETIME()
        WHERE OrderId = @OrderId;

    COMMIT;
END;
GO

Enable ADR and optimized locking for SalesDemo

USE [SalesDemo];
GO

ALTER DATABASE [SalesDemo]
SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

ALTER DATABASE [SalesDemo]
SET OPTIMIZED_LOCKING = ON;
GO

Run this during a maintenance window with no other active connections to SalesDemo. ADR must be enabled before optimized locking. This is supported by the reported SQL Server 2025 Enterprise engine. Monitor the persistent version store inside SalesDemo. If rollback is ever required, disable optimized locking before disabling ADR.

Enable READ_COMMITTED_SNAPSHOT for SalesDemo

USE [SalesDemo];
GO

ALTER DATABASE [SalesDemo]
SET READ_COMMITTED_SNAPSHOT ON;
GO

This implements the RCSI recommendation for the S-lock component in deadlocks #5, #6, #8 and #9. Test application behavior because READ COMMITTED readers will see the last committed version rather than wait for a writer. The change requires no other active connections to SalesDemo and uses version-store space.

Retry the refund SQL Agent step with a randomized delay after error 1205

USE [SalesDemo];
GO

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
EXECUTE AS USER = N'svc_refunds';

DECLARE @i int = 0,
        @victims int = 0,
        @OrderId int,
        @stop datetime2 = DATEADD(second, 90, SYSDATETIME());

WHILE @victims < 4 AND SYSDATETIME() < @stop
BEGIN
    SET @OrderId = 49998 + @i % 3;

    BEGIN TRY
        EXEC dbo.usp_RefundOrder @OrderId = @OrderId;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK;

        IF ERROR_NUMBER() = 1205
        BEGIN
            SET @victims += 1;
            WAITFOR DELAY '00:00:00.250';
        END
        ELSE
            THROW;
    END CATCH;

    SET @i += 1;
END;

REVERT;
PRINT CONCAT(@i, ' calls, ', @victims, ' deadlock victims');

This complete batch must replace the refund SQL Agent job step. The delay is intentionally short and fixed in this example; the SQL Agent step can use a randomized delay policy if the job implementation supports it. Retry only because this procedure is assumed to be safe to rerun after rollback.

Retry the payment SQL Agent step with a randomized delay after error 1205

USE [SalesDemo];
GO

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
EXECUTE AS USER = N'svc_orders';

DECLARE @i int = 0,
        @victims int = 0,
        @OrderId int,
        @stop datetime2 = DATEADD(second, 90, SYSDATETIME());

WHILE @victims < 4 AND SYSDATETIME() < @stop
BEGIN
    SET @OrderId = 49998 + @i % 3;

    BEGIN TRY
        EXEC dbo.usp_PayOrder @OrderId = @OrderId;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK;

        IF ERROR_NUMBER() = 1205
        BEGIN
            SET @victims += 1;
            WAITFOR DELAY '00:00:00.250';
        END
        ELSE
            THROW;
    END CATCH;

    SET @i += 1;
END;

REVERT;
PRINT CONCAT(@i, ' calls, ', @victims, ' deadlock victims');

This complete batch must replace the payment SQL Agent job step. The retry and priority changes reduce impact only; the procedure rewrites remain the prevention mechanism.

Confidence and limitations

  • Root-cause confidence: 99%. The same two procedures and the same two primary-key resources recur across all ten graphs, with reciprocal X waits explicitly shown.
  • Page-lock contributing-factor confidence: 97%. Four graphs explicitly show an S waiter on Orders page 8782 and an IX owner on that page, but no execution plans or existing-index definitions were supplied.
  • Index recommendation confidence: 85%. The key and filter columns are directly visible in the correlated subquery, but existing indexes, data distribution and plan shape are unavailable.
  • RCSI applicability confidence: 90%. RCSI is OFF for SalesDemo and the graph shows an S lock under READ COMMITTED. RCSI is secondary and cannot solve writer-versus-writer cycles.
  • Optimized-locking applicability confidence: 85%. SalesDemo has ADR OFF and optimized locking OFF on SQL Server 2025, and page locks are present. The setting is not a replacement for consistent transaction order.
  • No row counts, table sizes, execution plans, index inventory, lock counts, job schedules or workload duration beyond the supplied timestamps are available. Cost, percentage improvement and deadlock-rate reduction estimates would therefore be unsupported and are not stated.