Locking and Blocking Analysis – RockyPC / WideWorldImporters – 2026-05-31 16:53:29 UTC

Executive Summary

• The live lock-blocking chain is 62 → 66 → 63.
• Session 60 is not a real lock blocker; its waits are CXSYNC_PORT, CXPACKET, and CXCONSUMER, which indicate intra-query parallelism waits, not lock blocking.
• Primary root cause category: Open transaction with no activity.
• Secondary contributing categories: Hotspot table or index and Missing index causing lock amplification.

Scope & Context

Key Findings

Blocking Tree

Figure: live lock chain in WideWorldImporters. Session 62 is the root blocker; session 66 is both a waiter and a blocker.

62 (X lock on Application.StateProvinces PK row for 'VA')
  └─ blocks 66 (LCK_M_S, 17,299 ms)
       Statement: SELECT ... FROM Application.Cities
                  JOIN Application.StateProvinces
                  JOIN Application.Countries
                  WHERE sp.StateProvinceCode = N'VA'
       └─ blocks 63 (LCK_M_U, 13,008 ms)
            Statement: UPDATE Application.StateProvinces
                       SET LatestRecordedPopulation = LatestRecordedPopulation + 1,
                           ValidFrom = SYSUTCDATETIME()
                       WHERE StateProvinceCode = N'VA'

Root Cause Classification

Impact Metrics

Detailed Prioritized Recommendations

1. Shorten transaction scope and remove lock-holding idle time around Application.StateProvinces

   • Fix text: Commit immediately after the UPDATE Application.StateProvinces ... WHERE StateProvinceCode = N'VA'; do not leave the transaction open across WAITFOR, client think-time, or unrelated reads/updates.
   • Rationale: Lock Inventory shows session 62 holds X on PK_Application_StateProvinces; Long-Running Transactions shows 19s duration; SQL text includes BEGIN TRAN followed by WAITFOR DELAY '00:00:10'. That is direct causal evidence.
   • Estimated risk: Low to Medium. Risk is mainly application behavior change if current code assumes a broader transaction.
   • Rollback plan: Revert the procedure or application batch to prior transaction boundaries.
   • Confidence: High
   • Estimated reduction in blocking: 70–90%

2. Enable READ_COMMITTED_SNAPSHOT for WideWorldImporters

   • Fix text: Turn on row-versioned read committed so readers do not wait on writers for this pattern.
   • Rationale: Database Settings: RCSI OFF and Snapshot OFF. Active Blocking Chains: session 66 is a reader blocked with LCK_M_S by session 62. This is a textbook case where RCSI breaks reader-writer blocking.
   • Estimated risk: Medium. Changes read semantics to versioned reads and increases tempdb version-store usage.
   • Rollback plan: Set READ_COMMITTED_SNAPSHOT OFF during a maintenance window if application validation fails.
   • Confidence: High
   • Estimated reduction in blocking: 40–60% overall; near 100% for this specific read-vs-write wait

3. Standardize update order for Countries and StateProvinces

   • Fix text: Ensure every code path touching both tables acquires locks in the same order, preferably one consistent sequence such as Countries then StateProvinces, or the reverse, but never both.
   • Rationale: Long-Running Transactions: session 63 batch updates Countries then StateProvinces. Lock Inventory: session 61/62 sample shows another path updating StateProvinces first. Deadlock History: recent deadlock in WideWorldImporters on 2026-05-31 15:37:34 UTC shows a user transaction waiting on a key lock in this database. The current code shape is consistent with conversion/order deadlocks.
   • Estimated risk: Medium. Requires code change across all competing workflows.
   • Rollback plan: Revert procedure/application code to prior release if functional testing fails.
   • Confidence: High
   • Estimated reduction in blocking: 30–50%; deadlock reduction 70–95%

4. Create the missing index on Application.Cities

   • Fix text: Add a nonclustered index on (StateProvinceID) INCLUDE (CityName).
   • Rationale: Missing Index Candidates: impact 118.9 with equality [StateProvinceID] and include [CityName]. Blocker Query Plans session 66: missing index recommendation impact 93.0233 on Application.Cities. Index Usage: Cities.PK has scans on ~37,940 rows. Reducing scan work shortens read duration and lock retention under non-RCSI reads.
   • Estimated risk: Low. Additional storage and write overhead on Cities.
   • Rollback plan: Drop the new index if regression or write overhead appears.
   • Confidence: High
   • Estimated reduction in blocking: 10–25%
   • Additional note: Confirm with the Index Tuning goal for the Application.Cities table for a broader design review before promoting to production.

5. Keep reads outside explicit transactions unless the read must be transactionally coupled to a later write

   • Fix text: Remove BEGIN TRAN around the session 66 read, or move the read after the write if business logic permits.
   • Rationale: Long-Running Transactions: session 66 batch is BEGIN TRAN; SELECT ... ; UPDATE Application.StateProvinces .... The read becomes a blocker once it is waiting and later chains into session 63. Avoiding the transaction around the read reduces chain depth.
   • Estimated risk: Medium. Depends on consistency requirements.
   • Rollback plan: Restore the original transaction wrapper.
   • Confidence: Medium
   • Estimated reduction in blocking: 15–35%

6. Do not treat session 60 as the lead blocking target for this incident

   • Fix text: Separate parallelism tuning for sp01108 in SQLStorm from the lock-blocking remediation in WideWorldImporters.
   • Rationale: Active Blocking Chains: session 60 only shows self-referential CX* waits; no lock wait chain to other sessions. This is not the blocker causing the live lock issue.
   • Estimated risk: None
   • Rollback plan: Not applicable
   • Confidence: High
   • Estimated reduction in blocking: 0% for this lock incident; avoids misdiagnosis

Code / Query Rewrite Guidance

Deadlock History & Long-Term Mitigations

Figure: simplified representation of the deadlock pattern supported by current batch text and recent deadlock history.

Long-term mitigations

• Enforce a single object access order across all procedures and application paths.
• Use RCSI to remove read-vs-write blocking where correctness permits.
• Keep transactions as short as possible and never hold locks during WAITFOR or client-side pauses.
• For known deadlock-prone, low-priority retryable operations, consider application retry logic with bounded retries.
• Query Store and XE deadlock capture are already available here, which is good; continue retaining these for recurrence tracking.

Cross-Referenced Evidence

Scripts

Recommendation 2: Enable READ_COMMITTED_SNAPSHOT for WideWorldImporters

USE master;
GO
ALTER DATABASE [WideWorldImporters]
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO

Recommendation 4: Create the missing nonclustered index on Application.Cities to reduce scan work and lock exposure

USE [WideWorldImporters];
GO
CREATE INDEX IX_Cities_StateProvinceID_CityName
ON [Application].[Cities] ([StateProvinceID])
INCLUDE ([CityName]);
GO