Deadlock Analysis – VMEnt\SQL25 – 2026-11-13
Executive summary
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.
- 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.
- Reduce the payment order lookup locks. Review an index on
SalesDemo.dbo.Orderskeyed by(AccountId, Status)and includingAmount. Run the Index Tuning goal before implementing any physical design change; existing indexes are not supplied. - Enable optimized locking after ADR.
SalesDemohas 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. - 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. - 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
| Evidence | Finding |
|---|---|
| Deadlock graphs | 10 graphs, each with two processes and two resources. |
| Victims | One victim per graph; the victim log used values supplied are 284, 404 or 264. |
| Isolation | All shown processes use READ COMMITTED. |
| SalesDemo settings | RCSI OFF; snapshot isolation OFF; ADR OFF; optimized locking OFF. |
| Code source | usp_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 ownership | SalesDemo owns the locked resources in deadlocks #1–#10, so its settings govern all analyzed locks. |
Detailed prioritized recommendations
-
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_AccountsandSalesDemo.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.
-
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.Orderspage 8782 and an S waiter from the correlated aggregate at CODE-3 (moduleSalesDemo.dbo.usp_PayOrder, lines 11–13). The predicate isAccountId = @AccountIdandStatus IN ('Open','Refunded'), and the value returned isAmount.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 columnAmount. 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.
-
Priority 3 — Enable ADR and optimized locking for SalesDemo
Evidence:
SalesDemohas 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 insideSalesDemoinstead 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.
-
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 (moduleSalesDemo.dbo.usp_PayOrder, lines 11–13) requests an S lock while reading Orders under READ COMMITTED. With RCSI enabled, ordinary READ COMMITTED reads ofSalesDemodata 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 toSalesDemo. -
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 LOWcan 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
| Graphs | Pattern | Locks and statements | Primary fix |
|---|---|---|---|
| #1–#4, #7, #10 | Opposite-order writer cycle | Refund 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, #9 | Account writer versus Orders page read | Payment 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.