Transactions and Isolation: ANSI Levels, MVCC, and SERIALIZABLE

Problem Context

Transactions are the database's promise to make a sequence of operations look atomic, consistent, isolated, and durable — the ACID contract. The promise is real, but the price tag varies wildly with the isolation level you pick, and most engineers never look past the default. The default is rarely what you want.

Postgres defaults to READ COMMITTED. SQL Server defaults to READ COMMITTED (with locking). MySQL/InnoDB defaults to REPEATABLE READ. Cosmos DB SQL API and DynamoDB don't even use the same vocabulary. This guide is the mental model that lets you reason about anomalies — lost updates, write skew, phantom reads — and pick the right tool for the workload.

Concept Explanation

ANSI SQL defines four isolation levels by which anomalies they permit:

• READ UNCOMMITTED — sees dirty (uncommitted) writes. Almost never safe.
• READ COMMITTED — only sees committed data, but can see different values across two reads in one transaction.
• REPEATABLE READ — same row reads the same value all transaction long; phantoms (new rows) still possible.
• SERIALIZABLE — equivalent to running transactions one at a time. Catches write skew. Costs the most.

Modern engines implement these with two strategies: 2PL (two-phase locking) as in classic SQL Server, or MVCC (multi-version concurrency control)as in Postgres, Oracle, and SQL Server's snapshot mode. MVCC readers don't block writers; the trade-off is more storage and vacuum overhead.

flowchart LR
    RU["READ UNCOMMITTED<br/>(dirty reads OK)"] --> RC["READ COMMITTED<br/>(default, allows non-repeatable)"]
    RC --> RR["REPEATABLE READ<br/>(snapshot, allows phantoms)"]
    RR --> SER["SERIALIZABLE<br/>(no anomalies, slowest)"]

    style RC fill:#0078D4,color:#fff,stroke:#005a9e
    style SER fill:#16a34a,color:#fff,stroke:#15803d

Implementation

Step 1: Use a real transaction in C#

// EF Core 9 — explicit transaction with isolation level
await using var tx = await db.Database
    .BeginTransactionAsync(System.Data.IsolationLevel.RepeatableRead);

try
{
    var account = await db.Accounts.FirstAsync(a => a.Id == fromId);
    if (account.Balance < amount) throw new InsufficientFundsException();

    account.Balance -= amount;
    var dest = await db.Accounts.FirstAsync(a => a.Id == toId);
    dest.Balance += amount;

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

Step 2: Optimistic concurrency with rowversion

// EF Core: add a concurrency token; saves throw on conflict
public class Account
{
    public long Id { get; set; }
    public decimal Balance { get; set; }
    [Timestamp] public byte[] RowVersion { get; set; } = default!;
}

try { await db.SaveChangesAsync(); }
catch (DbUpdateConcurrencyException)
{
    // Reload, re-apply, retry — winner is whoever commits first
}

Step 3: Pessimistic locking when contention is the norm

-- Lock the row(s) for the rest of the transaction
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;  -- blocks others
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

-- SKIP LOCKED variant for queue workers (don't block, just take the next one)
SELECT * FROM jobs WHERE status='pending'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

Step 4: SERIALIZABLE catches write skew

-- Classic on-call rotation: at least one doctor must be on call.
-- Two transactions read "currently 2 on call", both decide it's safe to
-- take themselves off — and now zero are on call. Write skew.
BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT count(*) FROM oncall WHERE date = '2026-04-27' AND active;
-- both Tx A and Tx B see 2 → both decide to update themselves
UPDATE oncall SET active = false WHERE doctor_id = current_doctor;

COMMIT;
-- Postgres SSI / SQL Server snapshot serializable will abort one with
-- "could not serialize access due to read/write dependencies"

Step 5: Idempotent retry on serialization failure

async Task<T> WithRetry<T>(Func<Task<T>> work, int max = 3)
{
    for (int attempt = 0; ; attempt++)
    {
        try { return await work(); }
        catch (PostgresException ex) when (ex.SqlState is "40001" or "40P01")
        {
            // 40001 = serialization_failure, 40P01 = deadlock_detected
            if (attempt >= max) throw;
            await Task.Delay(TimeSpan.FromMilliseconds(50 * (1 << attempt)));
        }
    }
}

Step 6: Cross-service transactions: the outbox

-- Atomically save business state + the message to publish
BEGIN;
INSERT INTO orders (...) VALUES (...);
INSERT INTO outbox (id, topic, payload)
VALUES (gen_random_uuid(), 'orders.created', '{...}'::jsonb);
COMMIT;

-- A separate poller publishes from outbox to Service Bus / Kafka / Event Grid
-- and marks rows shipped. No 2PC, no XA. Receivers must be idempotent.

Common Pitfalls

1. Read-modify-write without a transaction. Two threads both read 100, both write 101 → you lost an increment. UseUPDATE ... SET balance = balance + ? or a transaction with appropriate isolation.
2. Long-running transactions. Hold locks (2PL) or block vacuum (MVCC). Set statement_timeout andidle_in_transaction_session_timeout; never await network I/O inside a transaction.
3. Deadlocks ignored. Always retry on deadlock (40P01) and serialization failure (40001) with exponential backoff. Order your locks consistently across code paths.
4. Distributed transactions / 2PC. XA across microservices is fragile and slow. Prefer the outbox pattern with idempotent consumers and the saga pattern for compensating actions.
5. Not understanding REPEATABLE READ ≠ SERIALIZABLE.RR prevents non-repeatable reads but allows phantoms and write skew. For invariants like "at least one row must satisfy X", only SERIALIZABLE is correct without explicit locking.
6. Treating autocommit as a feature. Multi-statement business logic without an explicit transaction is a partial-failure waiting to happen. Wrap related writes in BEGIN ... COMMIT.

Practical Takeaways

• Default isolation is READ COMMITTED almost everywhere. Know what anomalies that allows.
• For monetary transfers and inventory, use either SERIALIZABLE or explicit row locks (FOR UPDATE).
• Optimistic concurrency (rowversion / xmin) wins when conflicts are rare.
• Pessimistic locks (FOR UPDATE, FOR UPDATE SKIP LOCKED) win when conflicts are the norm.
• Always retry on deadlock and serialization failure — they're expected, not exceptional.
• Don't do XA / 2PC across services. Use the transactional outbox + idempotent consumers.
• No network calls inside a transaction. Ever.