SQL Transactions Visualized: Isolation Levels, Locks & Race Conditions

Why did two users overwrite each other's data even though every query succeeded? Usually the bug is not one invalid statement. It is a valid sequence whose assumptions stop being true when another transaction runs between its steps.

The simulator above is an educational model. PostgreSQL uses MVCC snapshots, row versions, heavyweight and lightweight locks, predicate dependencies, and engine-specific conflict rules that a compact timeline cannot reproduce byte for byte.

A transaction is a correctness boundary

BEGIN starts a unit of work. COMMIT makes its successful changes durable and visible according to the database's rules. ROLLBACK abandons its uncommitted changes.

BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 42;
INSERT INTO ledger(account_id, delta) VALUES (42, -20);
COMMIT;

Without the transaction, a crash between the two statements can change the balance without recording the ledger entry. With it, both changes commit or neither does.

ACID in production language

• Atomicity: the transaction's writes become visible together or are rolled back together.
• Consistency: constraints and correct application rules preserve invariants; the database cannot invent a missing business rule.
• Isolation: concurrent work is constrained to outcomes allowed by the selected level.
• Durability: after a successful commit, the engine's configured persistence guarantees survive the failures it claims to tolerate.

ACID is not a promise that every schedule behaves as though only one request existed. The isolation level defines which interleavings are allowed.

The lost-update trap

This application pattern is vulnerable:

const account = await db.account.findUnique({ where: { id } });
await db.account.update({ where: { id }, data: { balance: account.balance - 20 } });

Two callers can both read 100, calculate different values, and later overwrite each other. A single SQL expression such as SET balance = balance - 20 lets PostgreSQL serialize conflicting row updates. When the new value depends on application logic, use a row lock, an optimistic version check, or a serializable transaction with retry.

UPDATE accounts
SET balance = 80, version = version + 1
WHERE id = 42 AND version = 7;

Zero affected rows means somebody changed version 7 first. Reload, re-evaluate the rule, and retry only when doing so is safe.

Read anomalies

A dirty read sees data another transaction has not committed. PostgreSQL does not allow dirty reads: its READ UNCOMMITTED spelling behaves as READ COMMITTED.

A non-repeatable read means the same row query returns a newly committed value later in the transaction. PostgreSQL READ COMMITTED takes a new snapshot for each statement, so this is possible. PostgreSQL REPEATABLE READ uses one transaction snapshot, preventing non-repeatable reads and ordinary phantom changes visible to that snapshot.

A phantom changes the set of rows matching a predicate. SQL's anomaly names are a portable vocabulary, not a complete implementation specification. PostgreSQL REPEATABLE READ is snapshot isolation and is stronger than the standard's minimum in some respects, yet write-skew-style anomalies can remain. SERIALIZABLE adds Serializable Snapshot Isolation checks and may abort a transaction whose combined dependencies would produce a non-serial execution.

Pessimistic and optimistic concurrency

Pessimistic control reserves the row before making a decision:

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- validate the invariant, then update
COMMIT;

A conflicting updater waits. This is useful when contention is expected and the decision must be based on the locked state, but locks increase waiting and make lock order important.

Optimistic control reads freely and detects a conflicting version at write time. It performs well when conflicts are rare, but the application must surface or retry failures without duplicating side effects.

Deadlocks are normal error paths

If Transaction A locks row 42 then asks for row 77 while Transaction B holds row 77 and asks for row 42, neither can proceed. PostgreSQL detects the wait cycle, aborts one transaction, and lets the other continue. Lock resources in a consistent order to reduce deadlocks, but still handle the error.

for (let attempt = 0; attempt < 3; attempt++) {
  try { return await runSerializableTransaction(); }
  catch (error) {
    if (!isRetryableSerializationOrDeadlock(error)) throw error;
  }
}

Retry the whole transaction, not only the last statement, because every decision came from a snapshot that may no longer be valid. Keep non-database side effects outside or make them idempotent.

Isolation costs

More isolation can create waits, aborts, retained row versions, and retry work. Less isolation can move complexity into explicit locks, conditional writes, constraints, and application reasoning. SERIALIZABLE is not automatically slow, and READ COMMITTED is not automatically safe; measure the workload and state the invariant first.

PostgreSQL, MySQL/InnoDB, SQL Server, SQLite, and distributed SQL databases differ in defaults, snapshot rules, lock ranges, predicate protection, and error behavior. Verify claims against the exact engine and version you operate.

Production checklist

• Put every multi-write invariant inside one transaction.
• Prefer constraints as the final guard: unique keys, checks, and foreign keys race safely.
• Keep transactions short; never wait for user input or a remote API while holding locks.
• Log retryable serialization and deadlock failures separately from permanent errors.
• Test concurrent requests, not only sequential unit tests.
• Combine transactions with server-authoritative rules, not client-provided outcomes.

Key takeaways

• Correct queries can compose into an incorrect concurrent schedule.
• PostgreSQL READ COMMITTED uses statement snapshots; REPEATABLE READ uses a transaction snapshot; SERIALIZABLE can require retry.
• Row locks prevent particular conflicts, while optimistic versions detect them.
• Deadlocks and serialization failures are expected outcomes to handle.
• Indexes make rows fast to find; Database Indexes & B-Trees explains that access path.
• Threads create the same interleaving intuition at another layer; see Processes, Threads & Concurrency.