Deadlock Analysis – RockyPC / WideWorldImporters, tpch10 – 2026-05-31

1) Enforce one write order for WideWorldImporters transactions touching Countries and StateProvinces

Recommendation: Make every code path acquire locks and perform updates in the same object order. Use one canonical sequence, for example: Application.Countries first, then Application.StateProvinces, or the reverse—just keep it identical everywhere.

• The deadlock graph shows a textbook cycle:
  • SPID 61: updates StateProvinces, then waits for Countries
  • SPID 63: updates Countries, then waits for StateProvinces
• This is the highest-confidence fix because the deadlock is caused directly by inconsistent object ordering.
• If the business action must touch both entities, encapsulate both writes in one procedure so the order cannot drift between callers.
• If multiple modules perform similar work, review them all for the same ordering rule; fixing only one caller may leave the deadlock pattern intact.

Preferred implementation pattern: a single stored procedure or application service method that always performs the two updates in one deterministic order.

Confidence: 99%

2) Eliminate long lock retention inside the WideWorldImporters transaction

Recommendation: Remove the in-transaction WAITFOR DELAY '00:00:10' and any equivalent non-essential pause, user interaction, network call, or application processing performed after the first write and before commit.

• The deadlock window is being deliberately widened by a 10-second hold on an exclusive key lock.
• Even with consistent ordering, long transactions increase blocking and deadlock exposure.
• If a pause is needed for testing only, keep it out of production code entirely.
• If external work must occur, move it before BEGIN TRAN or after COMMIT.

Confidence: 99%

3) Force the tpch10 orders update to run serially

Recommendation: Add statement-level OPTION (MAXDOP 1) to the UPDATE o ... FROM dbo.orders AS o JOIN ... statement inside dbo.usp_UpdateOrdersAndLineitem, or otherwise force that specific update to use a single worker.

• The deadlock graphs repeatedly show exchangeEvent resources with e_waitPortOpen, which is a parallel plan signature.
• They also show multiple ECIDs for the same SPID, confirming multiple workers participating in the same request.
• Single-threading the update removes the exchange-port cycle and usually removes this class of parallel key-lock deadlock entirely.
• Because this is SQL Server 2022 Enterprise/Developer, you have the platform capability to control this precisely at statement or Query Store hint level; no edition limitation blocks this fix.

Preferred scope: statement-level or query-level, not server-wide.

Confidence: 95%

4) Process orders in small, ordered batches by primary key

Recommendation: Rewrite dbo.usp_UpdateOrdersAndLineitem so it updates a predictable range or chunk of o_orderkey values per batch, in ascending key order, committing between batches.

• The captured deadlocks show U locks on different orders_pk keys, which means concurrent workers/executions are acquiring keys in non-harmonized sequences.
• Stable ordering reduces the chance that session A holds K1 and waits for K2 while session B holds K2 and waits for K1.
• Smaller batches reduce lock footprint, transaction duration, log pressure, and victim cost.
• This is especially important if multiple SQLCMD sessions invoke overlapping date ranges concurrently, as seen in the captured executions.

Implementation direction: load candidate order keys into a temp structure, index it on o_orderkey, then update TOP (N) rows per loop ordered by key.

Confidence: 92%

5) Replace the table variable driving the orders update with a temp table and supporting index

Recommendation: If @BatchOrders is a table variable, replace it with #BatchOrders and add an index or primary key on o_orderkey.

• The statement text shows JOIN @BatchOrders AS bo ON bo.o_orderkey = o.o_orderkey.
• Table variables often produce weaker cardinality estimates than temp tables, especially for larger sets, which can promote poor join choices and parallel plans.
• Better row estimates improve plan stability and make it easier to get a serial, ordered key-update plan.
• On SQL Server 2022, deferred compilation helps table variables more than in older versions, but it does not remove all risk for large update workloads.

Confidence: 84%

6) Prevent overlapping executions of the same tpch10 maintenance procedure when date ranges intersect

Recommendation: Serialize concurrent runs of dbo.usp_UpdateOrdersAndLineitem for overlapping business ranges, either in the job scheduler or inside the procedure using an application lock.

• The deadlocks show concurrent executions from SQLCMD using overlapping date windows, such as 1995-01-01 to 1995-03-31 and 1995-02-15 to 1995-05-15.
• Even after reducing parallelism, overlapping writers against the same key space can still deadlock if the update path remains broad.
• An application lock is a strong deterministic guard when the operation is a batch maintenance task rather than latency-sensitive OLTP.

Confidence: 88%

7) Do not use RCSI as the primary remediation for these captured deadlocks

Recommendation: Keep focus on write-ordering, serial execution of the problematic update, and batch design. Treat READ_COMMITTED_SNAPSHOT only as a secondary concurrency improvement for read/write blocking elsewhere.

• Deadlock #1 is update-versus-update on two keys.
• Deadlocks #2–#5 are update key locks plus parallel exchange events.
• RCSI reduces reader/writer blocking, but it does not prevent writer/writer deadlocks on the same rows or parallel exchange deadlocks.

Confidence: 96%