Transactions & Isolation

Status: Reviewed (deep-dive v1)
Stack note: Write procs (usp_Alert_Ack, collector merges) and busy Blazor boards share the warehouse — wrong isolation or fat transactions → blocking, deadlocks, and UI timeouts. Pair with SQL-15 (TRY/CATCH + XACT_STATE).
Pairs with: SQL-12 (write procs), SQL-15 (error/tran cleanup), SQL-19 (MERGE upserts — next on shortlist), SPIKE-07 (Ack/Evaluate).
Goal: RC vs RCSI/snapshot, short transactions, deadlock hygiene for Ack/merge, what Blazor sees when blocked.

Quick chooser

Situation	Lean toward
Default OLTP warehouse	READ COMMITTED; consider RCSI (DB option) for read-heavy boards
Ack / small write	Short explicit tran; `XACT_ABORT ON` (SQL-15)
Board read during merges	RCSI reduces reader/writer blocking; still measure tempdb
Long report inside UI request	Don’t — snapshot to `#temp` or Agent job; short read
“I need a consistent estate snapshot”	One short read tran or `AS OF`/staging — not HOLDLOCK forever

Isolation levels (practical)

Level	Readers see	Main risk for this estate
READ COMMITTED (default)	Committed only; can block on writers	Writers block readers (and vice versa locks)
RCSI (DB: `READ_COMMITTED_SNAPSHOT ON`)	Versioned committed; readers don’t take shared locks the same way	tempdb version store growth
SNAPSHOT (session)	Statement/tran consistent view	Same version store; don’t leave long snapshot trans open
REPEATABLE / SERIALIZABLE	Stronger	Extra blocking — rare for boards; avoid by default
READ UNCOMMITTED / NOLOCK	Dirty	Wrong RAG/alert counts — not a perf strategy (SQL-16)

Team default: keep DB on RC or turn on RCSI after a tempdb size check (SQL-06). Prefer RCSI over sprinkling NOLOCK in usp_*.

-- check
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases WHERE name = DB_NAME();

-- enable RCSI (maintenance window; watch tempdb)
ALTER DATABASE Current SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Write proc pattern (Ack / merge)

SET NOCOUNT ON;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- explicit; don’t surprise

BEGIN TRY
  BEGIN TRAN;
    -- validate + single-row/set update + event insert
  COMMIT TRAN;
END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0 ROLLBACK TRAN;
  THROW;
END CATCH

Rules:

[ ] Touch few rows, few indexes, in a stable lock order (e.g. always Alert then AlertEvent)
[ ] No Blazor round-trip mid-tran; no waiting on HTTP inside SQL
[ ] Don’t open tran before expensive unrelated SELECTs
[ ] Evaluate: prefer set merge in batches over one mega-tran for whole estate if it blocks boards

Deadlocks on Ack / merge

Typical shape: Session A updates Alert then inserts AlertEvent; Session B (or Evaluate) locks in another order / overlapping ranges.

Mitigations:

Consistent table/index access order in all writers
Narrow indexes → fewer lock ranges (SQL-16)
Shorter transactions
Retry once in Blazor/SqlException 1205 (optional, documented)
Capture: system_health / Extended Events deadlock graph — fix the pair, don’t only retry forever

-- victim message includes deadlock XML in XE; also:
DBCC TRACEON (1222, -1);  -- ops judgment; prefer XE in prod

What Blazor sees when blocked

Symptom	Likely cause
Timeout on GetOpenBoard	Blocked on shared/exclusive locks; or slow scan
Timeout on Ack	Blocked behind Evaluate/merge or another Ack
Intermittent 1205	Deadlock victim
Works in SSMS, fails in app	Longer commands, different timeout, connection pooling + leftover tran (bug)

App habits:

Command timeout: boards short; don’t hide blocking with 5-minute timeouts
Ensure every code path commits/rolls back — leaked open tran from connection pool is catastrophic
Show friendly “busy, retry” on 1205/timeout; log proc + user

Readers vs writers (boards)

Without RCSI: usp_Alert_GetOpenBoard can block behind Ack/Evaluate writers (and hold locks that slow writers).

With RCSI: readers use row versions → fewer block chains; writers still conflict with writers.

-- optional: statement snapshot for one heavy read (if SI enabled)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT …;  -- keep short

Don’t hold snapshot tran across user think-time.

Lock hints — use sparingly

Hint	When
`UPDLOCK, ROWLOCK`	Claim row to update without immediate deadlock dance
`HOLDLOCK` / Serial	Almost never on boards
`NOLOCK`	Avoid for alert/RAG truth

Ops checklist

[ ] Know whether warehouse has RCSI on
[ ] tempdb sized for version store if RCSI/SI
[ ] Deadlock XE + Query Store for blocked usp_*
[ ] Evaluate schedule: off peak or batch so Ack UX stays snappy
[ ] No nested BEGIN TRAN puzzles — keep depth 1 in app procs

Tiny lab

Session 1: open tran, update one Alert, don’t commit
Session 2: GetOpenBoard — observe block (RC) vs progress (RCSI)
Two sessions Ack overlapping rows in opposite order — catch deadlock, fix order

Tie-ins

SQL-15: Always rollback doomed trans
SQL-19: MERGE upserts still need short tran + lock order
SPIKE-07: Ack + Evaluate concurrency is the live test

Done when

[ ] Isolation stance documented (RC vs RCSI) for the warehouse
[ ] Write procs: short tran + consistent lock order
[ ] Blazor timeouts/1205 handled without leaving open trans
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-18 Transactions & Isolation v1