Transactions and Isolation: ANSI Levels, MVCC, and SERIALIZABLE

Two Buyers, One Item Left

A flash sale on 50 units ends with 63 orders. Nobody wrote a bug you can point to: the code checked the stock, then decremented it. Two requests just did that at the same moment, and both saw one unit left.

Transactions are the database’s promise that a group of changes happens all together or not at all, and that concurrent work does not trample itself. How strong that second promise is depends on the isolation level, and most teams never look past the default. Below is what each level really guarantees, and the patterns that keep concurrent writes correct.

ACID and Isolation Levels

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

Writing Correct Concurrent Updates

Step 1: Use a real transaction in C#

// EF Core 10 — 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.

Mistakes That Corrupt Data

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.

What I Would Do

• 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.