Transactions & Isolation Levels

Production incident

An endpoint creates an order and updates inventory. Under load you see oversells and negative inventory. A quick patch adds a big transaction around the entire request including HTTP calls to payment. Now you stop oversells but you introduce lock contention and a cascading outage.

Symptom

• Data anomalies: double-spend, oversell, inconsistent reads.
• After a patch: timeouts, blocking chains, deadlocks.
• DB CPU is moderate but wait time is high.

Cause

• Using default isolation without understanding semantics.
• Long-running transactions that include remote calls or large loops.
• Mixing EF Core SaveChanges calls across multiple aggregates without an explicit boundary.

Diagnosis

# App side: identify slow requests with DB spans or logs
grep -R "BeginTransaction" -n /var/log/app/
grep -R "deadlock" -n /var/log/app/

# When you can run DB inspection, look for blocking and deadlocks
# SQL Server deadlock graphs are best pulled from extended events / system_health

Anti-pattern

• Wrapping an entire HTTP request in a transaction that includes outbound IO.
• Relying on implicit transaction behavior and assuming it is enough.
• Escalating isolation to Serializable as a default fix.

Correct pattern

Keep transactions short, cover only DB work, and choose isolation per scenario.

// Short DB-only transaction boundary
await using var tx = await db.Database.BeginTransactionAsync(System.Data.IsolationLevel.ReadCommitted);

try
{
    // Make updates deterministic and indexed
    // Example: conditional update to prevent negative inventory
    var affected = await db.Database.ExecuteSqlInterpolatedAsync($@"
        UPDATE Inventory
        SET Quantity = Quantity - {qty}
        WHERE Sku = {sku} AND Quantity >= {qty}
    ");

    if (affected == 0) throw new InvalidOperationException("Insufficient inventory");

    db.Orders.Add(order);
    await db.SaveChangesAsync();

    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

Isolation level guidance

• ReadCommitted: baseline. Most OLTP. Still can block on writers.
• Snapshot / RCSI: reduces reader-writer blocking. Requires DB config and tempdb capacity planning.
• RepeatableRead / Serializable: use sparingly for narrow critical sections, with proper indexes, and only when you accept lock cost.

Security and performance impact

• Performance: long transactions amplify lock duration and increase deadlock risk.
• Security: inconsistent reads can leak business invariants (for example showing a resource as available when it is not). Also, deadlock storms can become an availability incident.

Operational notes

• Monitoring: track deadlocks, lock waits, transaction duration, and timeouts.
• Rollout: introduce transaction changes behind a feature flag for high-traffic endpoints.
• Rollback: be ready to revert isolation or remove long transactions quickly. Prefer forward-fix with narrower SQL updates.

Checklist

• Transactions wrap only DB work, no remote calls inside.
• Transaction duration is bounded and measurable.
• Isolation level is chosen intentionally per endpoint.
• Updates use indexed predicates to avoid wide locks.
• Deadlocks and lock waits are monitored and alerted.