Executive summary (top priorities)

1. Enforce a consistent lock acquisition order for the two procs updating Sales.Orders and Sales.OrderLines (always touch tables/rows in the same sequence).
   • Fixes the classic “A updates Orders then OrderLines, B updates OrderLines then Orders” key-lock deadlock.
   • Confidence: 0.95
2. Enable row-versioning for reads (RCSI) in SQLStorm to eliminate reader/writer blocking that participates in page-lock + parallel-exchange deadlocks.
   • Reduces S/IX contention between sp00050 (SELECT) and usp_UpdateTablesWithDelay (UPDATE).
   • Confidence: 0.85
3. Reduce lock footprint of the long/parallel SELECT (sp00050) and the UPDATE workload to prevent page lock cycles.
   • Primary levers: add/adjust indexes to avoid scans & page S locks, and constrain parallelism for that query/proc.
   • Confidence: 0.75

Detailed prioritized recommendations

1. Make both WideWorldImporters procs acquire locks in the same order (single biggest fix for Deadlock #1)

   • Root cause addressed: Inconsistent object/row access order:
     • UpdLinesandOrders updates Sales.OrderLines then Sales.Orders
     • UpdOrdersandLines updates Sales.Orders then Sales.OrderLines
   • Action: Standardize to one order (recommended: parent then child: Orders then OrderLines), in every code path.
   • Action: If both tables must be updated, explicitly “touch” the first table first (even if no data changes) to establish deterministic lock order.

   -- Pattern: enforce parent-then-child locking order
   -- (example structure; apply to both procs consistently)
   
   BEGIN TRAN;
   
   -- 1) Lock/Update Orders first (acquires key X on PK_Sales_Orders row)
   UPDATE Sales.Orders
   SET Comments = ISNULL(Comments,'') + ' [ProcX]'
   WHERE OrderID = @OrderID;
   
   -- 2) Then update OrderLines (acquires key locks on PK_Sales_OrderLines rows)
   UPDATE Sales.OrderLines
   SET Description = SUBSTRING(ISNULL(Description,'') + ' [ProcX]',1,100)
   WHERE OrderID = @OrderID;
   
   COMMIT;

   • Confidence: 0.95

2. Use an application lock to serialize per-OrderID cross-table updates (high-impact “deadlock breaker” for Deadlock #1)

   • Root cause addressed: Two concurrent sessions updating the same business entity (OrderID) across multiple tables.
   • Action: Take a short-lived sp_getapplock keyed by OrderID at the start of both procs, in the same transaction scope.
   • Why this works: Prevents two sessions from interleaving updates for the same OrderID, eliminating the cyclic wait regardless of physical lock timing.

   -- Add near the start of BOTH procs
   DECLARE @lockResult int;
   
   EXEC @lockResult = sys.sp_getapplock
     @Resource = CONCAT('WideWorldImporters:SalesOrder:', @OrderID),
     @LockMode = 'Exclusive',
     @LockOwner = 'Transaction',
     @LockTimeout = 10000;  -- choose a deterministic timeout policy
   
   -- Proceed only if @lockResult >= 0, then do the updates and COMMIT/ROLLBACK.
   -- The applock is released automatically at transaction end.

   • Confidence: 0.90

3. Keep the WideWorldImporters transactions as short as possible (reduce deadlock window)

   • Root cause addressed: Both sessions show trancount=2; nested/longer transactions increase overlap time and lock hold duration.
   • Action: Remove unnecessary outer transactions around proc calls; avoid user interaction delays inside explicit transactions.
   • Action: Ensure no extra statements run between the two updates that could extend lock duration (e.g., debugging, waits, client think-time).
   • Confidence: 0.75

4. Enable RCSI in SQLStorm to eliminate reader/writer S/IX blocking (primary fix for Deadlock #2)

   • Root cause addressed: sp00050 runs under Read Committed and takes shared (S) page locks; concurrent updates require IX/X and can deadlock with parallel exchange operators.
   • Action: Turn on Read Committed Snapshot Isolation in SQLStorm so readers don’t take shared locks.

   -- Run in an admin window (will require exclusive access momentarily)
   ALTER DATABASE [SQLStorm] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

   • Confidence: 0.85

5. Reduce page-level contention in SQLStorm by ensuring selective, supporting indexes for sp00050 joins/filters

   • Root cause addressed: Page locks indicate scans or low-selectivity access causing many pages to be locked (notably on dbo.Users and dbo.Posts).
   • Action: Add/verify core join indexes to minimize locked pages during the SELECT:
     • Comments(PostId) (join to Posts)
     • Posts(OwnerUserId) (join to Users)
     • Posts(CreationDate) (filter), ideally with includes to reduce lookups for columns selected/ordered/grouped
   • Impact: Fewer touched pages → fewer S page locks → less chance of page-level deadlock and less time locks are held.
   • Confidence: 0.70

6. Constrain or shape parallelism for sp00050 to avoid parallel-exchange deadlock patterns

   • Root cause addressed: Deadlock graph shows multiple exchangeEvent resources (e_waitPipeGetRow / e_waitPortOpen) intertwined with page locks—common in parallel query deadlocks.
   • Action: Apply OPTION (MAXDOP 1) to sp00050 (or a small MAXDOP) to remove exchange operators.
   • Alternative: Use Resource Governor (Enterprise feature) to cap MAXDOP for that workload group if you need broader control.

   -- Example: add at the end of the main SELECT inside sp00050
   OPTION (MAXDOP 1);

   • Confidence: 0.65

7. Make the update workload (usp_UpdateTablesWithDelay / usp_UpdateTablesInLoop) less conflicting with hot pages

   • Root cause addressed: Writer holds IX on Users page while waiting on Posts page; readers hold S on Posts page while waiting on Users—cycle is enabled by page-level hot spots and overlap.
   • Action: Ensure updates target rows via narrow seeks (good supporting indexes on Posts(Id) already implied by PK; verify Users(Id) similarly).
   • Action: If the procs intentionally include delays inside a transaction, move delays outside the transaction so locks are not held while sleeping.
   • Confidence: 0.70

8. Standardize lock order across SQLStorm read/write code paths where both Users and Posts are touched

   • Root cause addressed: Cross-object access with opposite order can still deadlock even under RCSI for some operations (e.g., if readers use locking hints or SERIALIZABLE elsewhere).
   • Action: In any transaction that must touch both tables, always access them in a consistent order (e.g., Users then Posts, or vice versa) across all procedures.
   • Confidence: 0.55

Deadlock #1 (WideWorldImporters) — recommendations tied to this deadlock

• What happened: Two sessions each held an X key lock in one table and waited for a lock in the other:
  • Session A held X on Sales.OrderLines (PK_Sales_OrderLines) and waited for X on Sales.Orders (PK_Sales_Orders)
  • Session B held X on Sales.Orders and waited (U) / then X on Sales.OrderLines
• Primary prevention: consistent lock acquisition order + (optional) per-Order applock serialization.
• Confidence: 0.95

Deadlock #2 (SQLStorm) — recommendations tied to this deadlock

• What happened: Parallel SELECT (sp00050) took shared page locks and participated in an exchange-event deadlock while a concurrent transaction updated Posts and held IX on a Users page.
• Primary prevention: enable RCSI to remove S locks for readers; reduce scans/page-locking via indexing; reduce parallel exchange participation (MAXDOP control).
• Confidence: 0.80