Locking and Blocking Analysis – WideWorldImporters on RockyPC – 2026-05-31

Tuning goal: Locking and Blocking Analysis

Server: RockyPC  |  Version: SQL Server 2022 (16.0.1180.1) Developer Edition (Enterprise engine)  |  Database: WideWorldImporters

Executive Summary

Active blocking is confirmed and severe. A chain of open BEGIN TRAN statements left uncommitted in SSML query windows has created a long-lived blocking pyramid that has persisted for over 3 hours (longest wait ≈ 11,164,894 ms / ~186 minutes). The root is not a missing index or query-shape problem in the traditional sense — it is an open transaction with no activity on session 62 combined with a classic circular update ordering deadlock pattern (Countries → StateProvinces vs StateProvinces → Countries) seen both live and in deadlock history.

Top priorities:

1. Immediately clear the orphaned transactions. Session 62 holds an exclusive KEY lock on StateProvinces with an open transaction (WAITFOR DELAY '00:00:10' long since elapsed) and a blank command — it is idle inside an open transaction. Killing/committing it releases the entire pyramid. High impact
2. Standardize lock acquisition order across the Countries/StateProvinces update procedures to eliminate the recurring deadlock pattern observed in DeadlockHistory and live in sessions 61/63. Medium impact
3. Enable Read Committed Snapshot Isolation (RCSI) on WideWorldImporters so read-only SELECTs (session 66) stop being blocked by writers, removing the reader leg of the blocking tree. Medium impact
4. Add the recommended covering index on Application.Cities(StateProvinceID) INCLUDE (CityName) (Impact 118.9 / 93.0 from plan) to reduce scan footprint of the join SELECT. Low/secondary

Blocking Tree

The diagram shows the live blocking pyramid. Session 62 is the true lead blocker (idle/open transaction holding an X KEY lock on StateProvinces). Session 66 waits on it for an S lock, and itself blocks two writers (63, 61) that need U/X locks on the same StateProvinces key.

Blocking tree: SPID 62 (idle open transaction) holds the exclusive KEY lock at the apex; SPID 66 (a SELECT in its own open transaction) waits for a shared lock and in turn blocks two writers (63, 61) all queued on the same StateProvinces primary key. Note the cross-dependency: SPID 63 already holds an X lock on Countries while waiting on StateProvinces — the inverse-order pattern that produces deadlocks.

Root Cause Classification

Correlated Evidence

• Lead blocker is idle, not working: ActiveTransactions SPID 62 has blank Status/Command and 0ms CPU/Elapsed in BlockingChains, yet LockInventory shows it GRANTed an X KEY lock on PK_Application_StateProvinces. This is the signature of an abandoned BEGIN TRAN ... UPDATE ... WAITFOR batch left uncommitted in SSMS.
• Reader is blocked by the writer: SPID 66 SELECT waits LCK_M_S on the StateProvinces KEY (LockInventory Mode=S, Status=WAIT) because RCSI is OFF (DatabaseSettings: RCSI OFF). Under RCSI this read would not block.
• Circular update ordering: SPID 63 batch updates Countries first (holds X on PK_Application_Countries) then StateProvinces; SPID 61 updates StateProvinces first then Countries. This inverse ordering matches the recurring lockMode="U" KEY-wait deadlocks in DeadlockHistory (e.g., 2026-05-31 15:37:34 victim SPID 61).
• Scan footprint of the join: BlockerPlans SPID 66 StatementEstRows 715.85, embedded MissingIndex Impact 93.0; IndexUsage Cities PK Scans=2 confirms the join reads Cities by scan rather than seek on StateProvinceID.
• TempDB is a red herring: TempDBContention shows only background PAGELATCH/LATCH waits with no spills and VersionStore 0.0MB — not the cause of blocking.

Impact Metrics

Detailed Prioritized Recommendations

1. Clear the orphaned / idle open transactions immediately

   Fix: Identify and commit or kill the idle lead blocker (SPID 62) and the stalled SELECT-holding transaction (SPID 66). All scripts are in the Scripts section.

   • Rationale: SPID 62 holds the apex X KEY lock with a blank command and an 11,167s open transaction — releasing it collapses the entire tree (evidence: ActiveTransactions, LockInventory).
   • Risk/Rollback: Killing SPID 62 rolls back its single-row update (no committed data lost); rollback is automatic. Low risk because these are ad-hoc SSMS sessions on the same login.
   • Confidence: High
   • Estimated reduction in blocking: ~100% of the current incident

2. Enforce consistent lock-acquisition order on Countries/StateProvinces updates

   Fix: Wrap multi-table reference updates in a stored procedure that always updates parent then child in a single fixed order (e.g., always Countries then StateProvinces), inside a tightly scoped transaction with XACT_ABORT ON. See rewrite in Query/Code Changes.

   • Rationale: DeadlockHistory and live SPIDs 61/63 show opposite update ordering producing U-lock KEY deadlocks. Single ordering removes the cycle.
   • Risk/Rollback: Low; logic change only. Roll back by reverting the proc.
   • Confidence: High
   • Estimated reduction: ~80–90% of recurring deadlocks

3. Enable Read Committed Snapshot Isolation (RCSI)

   Fix: Turn on READ_COMMITTED_SNAPSHOT for WideWorldImporters (script in Scripts).

   • Rationale: DatabaseSettings RCSI OFF; SPID 66 (a pure SELECT) is blocked by writers on LCK_M_S. RCSI lets readers use row versions, removing the reader leg of the tree.
   • Risk/Rollback: Medium. RCSI adds version-store load to TempDB (currently idle, so headroom exists) and changes read semantics for in-flight readers. Requires a brief exclusive moment to enable. Roll back with SET READ_COMMITTED_SNAPSHOT OFF.
   • Confidence: Medium-High
   • Estimated reduction: ~30–40% of blocking (eliminates reader-vs-writer blocking)

4. Add covering index on Application.Cities

   Fix: Create IX_Cities_StateProvinceID_inc_CityName (script in Scripts). Confirm with a dedicated Index Tuning run for Application.Cities.

   • Rationale: MissingIndexes Impact 118.9 and plan-embedded MissingIndex Impact 93.0; reduces scan-based locking footprint of the join SELECT (IndexUsage Cities PK Scans=2). Smaller lock footprint = shorter lock hold times.
   • Risk/Rollback: Low; online build available (Enterprise engine). Drop index to roll back.
   • Confidence: Medium
   • Estimated reduction: ~5–10% (secondary; reduces contention amplification, not the root open-tran)

5. Add a sensible LOCK_TIMEOUT and avoid manual WAITFOR inside open transactions

   Fix: Application/ad-hoc sessions should set SET LOCK_TIMEOUT (e.g., 5000ms) so stuck waiters fail fast rather than waiting infinitely (all sessions currently show LockTimeout=Infinite). Never leave WAITFOR DELAY inside an uncommitted transaction in production.

   • Rationale: BlockingChains shows infinite waits of 186 minutes; finite timeouts would have surfaced the problem immediately.
   • Risk/Rollback: Low; session-scoped setting.
   • Confidence: Medium
   • Estimated reduction: Prevents future indefinite stalls (operational)

Deadlock History Pattern

The XE deadlock capture is functioning and shows a clear recurring pattern. Two distinct workloads appear:

• WideWorldImporters (db 6): 2026-05-31 15:37:34 — victim SPID 61, waiting on a U lock on a KEY resource (6:72057594041991168), trancount=2. This is the same Countries↔StateProvinces inverse-order pattern visible live.
• tpch10 (db 11): a burst of ~9 deadlocks on 2026-05-04/05 all waiting on U locks against KEY 11:72057594047102976 from SQLCMD clients — a separate parallel-update workload worth its own review, but outside the WideWorldImporters scope.

Long-term mitigation for the WideWorldImporters pattern is recommendation #2 (consistent lock ordering) plus #3 (RCSI for read paths). Query Store is enabled and capturing top consumers, so no enablement action is required there.

Query / Code Changes

The two writer batches acquire locks in opposite orders. Illustrative (non-executable) shapes:

SPID 63 order: UPDATE Countries ... ; UPDATE StateProvinces ...

SPID 61 order: UPDATE StateProvinces ... ; UPDATE Countries ...

Standardize on a single order and tighten transaction scope. The runnable rewrite (a stored procedure that enforces ordering) is in the Scripts section. The join SELECT (SPID 66) is well-formed and sargable (WHERE sp.StateProvinceCode = N'VA'); its only issue is the missing Cities index and the fact it is being held open in a transaction — it should not run inside BEGIN TRAN when no write follows.

USE [WideWorldImporters];
GO
-- Show waiting/blocking sessions and the lead blocker
SELECT  r.session_id, r.blocking_session_id, r.wait_type,
        r.wait_time AS wait_ms, r.command, s.status,
        DB_NAME(r.database_id) AS db_name,
        t.text AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;
GO
-- Find idle sessions holding open transactions (no active request)
SELECT  s.session_id, s.status, s.last_request_end_time,
        at.transaction_begin_time,
        DATEDIFF(SECOND, at.transaction_begin_time, SYSDATETIME()) AS open_secs
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions at ON at.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON s.session_id = st.session_id
LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
WHERE r.session_id IS NULL          -- no active request = idle in transaction
ORDER BY at.transaction_begin_time;
GO

USE [WideWorldImporters];
GO
-- REVIEW the diagnostic output above FIRST.
-- SPID 62 is the idle lead blocker holding the X KEY lock on StateProvinces.
-- Killing it rolls back its single-row update automatically (no committed data lost).
KILL 62;
GO
-- If SPID 66 (the open SELECT transaction) remains and still blocks writers, release it too:
KILL 66;
GO

USE [WideWorldImporters];
GO
CREATE OR ALTER PROCEDURE Application.usp_BumpPopulation
    @CountryIso       SMALLINT,
    @StateProvinceCode NVARCHAR(5)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;   -- ensure full rollback on error

    BEGIN TRY
        BEGIN TRAN;
            -- FIXED ORDER: parent (Countries) first, then child (StateProvinces)
            UPDATE Application.Countries
                SET LatestRecordedPopulation = LatestRecordedPopulation + 1,
                    ValidFrom = SYSUTCDATETIME()
            WHERE IsoNumericCode = @CountryIso;

            UPDATE Application.StateProvinces
                SET LatestRecordedPopulation = LatestRecordedPopulation + 1,
                    ValidFrom = SYSUTCDATETIME()
            WHERE StateProvinceCode = @StateProvinceCode;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH
END;
GO

USE [master];
GO
-- Requires a brief exclusive lock on the database; run during a quiet window.
ALTER DATABASE [WideWorldImporters]
    SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER 5 SECONDS;
GO
-- Verify
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = N'WideWorldImporters';
GO
-- Rollback if needed:
-- ALTER DATABASE [WideWorldImporters] SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK AFTER 5 SECONDS;

USE [WideWorldImporters];
GO
-- Online build available on this Enterprise engine.
CREATE NONCLUSTERED INDEX IX_Cities_StateProvinceID_inc_CityName
    ON Application.Cities (StateProvinceID)
    INCLUDE (CityName)
    WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO
-- Rollback:
-- DROP INDEX IX_Cities_StateProvinceID_inc_CityName ON Application.Cities;

-- Run at the start of ad-hoc batches so stuck waiters fail fast instead of blocking forever.
SET LOCK_TIMEOUT 5000;  -- milliseconds
GO