Guide

Database transactions and isolation levels explained

A payment that debits one account but never credits another. An inventory count that sells the last unit twice. These failures happen when concurrent requests read and write the same rows without clear rules. A transaction groups multiple SQL statements into one atomic unit: either every change commits together or none of them do. Isolation levels define how much one transaction can see of another's in-progress work. This guide walks through ACID, the four standard isolation levels, the anomalies each level prevents, how engines implement concurrency with MVCC and row locks, and how to choose settings that match your app's risk — building on SQL fundamentals and connecting to broader distributed consistency trade-offs when data spans multiple services.

What a transaction guarantees

In PostgreSQL, MySQL InnoDB, SQL Server, and most relational engines, you wrap work in an explicit transaction:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the second UPDATE fails — constraint violation, disk full, process crash — the engine rolls back the first change. Users never see a half-finished transfer. The classic acronym ACID names the promises:

Atomicity — all statements in the transaction succeed or all are undone. No partial commits.
Consistency — constraints (foreign keys, CHECK rules, unique indexes) hold before and after. The app defines business invariants; the database enforces schema-level ones.
Isolation — concurrent transactions behave as if they ran in some serial order, within the level you chose. This is the focus of the rest of the guide.
Durability — once COMMIT returns, the change survives a crash (via write-ahead log and fsync policy). Replication lag is a separate concern covered in distributed-systems guides.

ORMs often hide transaction boundaries. Frameworks like Django and Rails default to autocommit per statement unless you open a transaction block. For multi-step operations — order creation plus inventory decrement — you must explicitly group statements or use a service-layer unit of work.

Concurrency anomalies

When two transactions touch the same data at the same time, without isolation you can observe:

Anomaly	What happens	Example
Dirty read	Transaction A reads uncommitted writes from B	You see a balance that B rolls back a moment later
Non-repeatable read	Same row read twice returns different values	Report runs two aggregates; a concurrent update changes the total mid-query
Phantom read	Same range query returns different row sets	`SELECT COUNT(*) WHERE status='open'` returns 5, then 6 after an insert
Write skew	Two transactions read overlapping state and write inconsistently	Two on-call doctors both see the other is off duty and both take vacation
Lost update	Last writer overwrites without seeing prior change	Two editors save; the second clobbers the first's edits

Isolation levels are knobs that block some of these at the cost of throughput and risk of deadlocks. No single level prevents every anomaly in every engine — always check your database's documentation for exact behavior.

The four isolation levels

SQL defines four levels, from weakest to strongest. PostgreSQL defaults to Read Committed; MySQL InnoDB defaults to Repeatable Read. The names are standardized; implementations differ in edge cases.

Read Uncommitted

Allows dirty reads. Rarely used in production — PostgreSQL treats it as Read Committed. Useful only when you explicitly accept stale data and need maximum concurrency on read-heavy analytics (and even then, replicas are usually better).

Read Committed

Each statement sees only committed data at the time it starts. Dirty reads are impossible; non-repeatable reads and phantoms are still possible within one transaction. This is the sweet spot for most web apps: short transactions, one logical operation per request, good throughput. A typical HTTP handler opens a transaction, runs a few queries, commits — anomalies across statements are uncommon if you design idempotent handlers.

Repeatable Read

The snapshot taken at the first read in the transaction stays stable for subsequent reads of the same rows. Non-repeatable reads on those rows are blocked. Phantom reads may still occur in some engines (MySQL blocks many phantoms via next-key locks; PostgreSQL's snapshot isolation blocks phantoms for most practical cases). Use when a report or batch job must see a consistent view across multiple SELECTs without locking every row upfront.

Serializable

Strongest guarantee: transactions behave as if executed one after another. Prevents write skew and phantoms by aborting transactions that would break serial order (PostgreSQL uses Serializable Snapshot Isolation and may return 40001 serialization_failure). Throughput drops and retry logic becomes mandatory. Reserve for financial invariants — seat booking, ledger postings, coupon redemption — where a lost update costs real money.

Set the level per session or transaction:

-- PostgreSQL
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- or
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

How engines implement isolation: MVCC and locks

Blocking every concurrent reader while a writer works would kill performance. Most OLTP databases use multi-version concurrency control (MVCC): each row version carries a transaction ID range; readers see the snapshot valid for their transaction start without waiting for writers (writers create new row versions). Old versions are vacuumed or purged when no transaction needs them.

MVCC handles most SELECT/UPDATE patterns. When you need exclusive access — decrement inventory, assign a sequential ticket — use explicit row locks:

SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- other transactions block until you COMMIT

FOR UPDATE takes a row-level exclusive lock. FOR SHARE (or LOCK IN SHARE MODE in MySQL) blocks writers but allows other readers. Lock ordering matters: if transaction A locks row 1 then row 2, and B locks row 2 then row 1, both can wait forever — a deadlock. Engines detect this and abort one transaction; your app must retry.

Long transactions holding locks starve others and inflate connection pool checkout times. Keep transactions short: do network I/O and external API calls outside the transaction when possible.

Patterns beyond a single database

ACID applies inside one database. Modern apps often span Postgres, Redis cache, and a payment provider. You cannot wrap those in one BEGIN/COMMIT. Common patterns:

Outbox pattern — write the business row and an outbox event in the same DB transaction; a worker publishes to a message queue asynchronously.
Idempotency keys — store a unique key per client request so retries do not double-charge; pairs with webhook handlers and REST APIs.
Saga / compensation — sequence local transactions with explicit rollback steps (cancel shipment if payment fails). Eventual consistency across services; see distributed consistency for CAP trade-offs.
Optimistic concurrency — add a version column; UPDATE ... WHERE id = ? AND version = ? fails if another writer incremented it first. Cheaper than Serializable for low-contention edits.

On-chain settlement (e.g. verifying a Solana payment before crediting an order) is another boundary: commit the DB row only after chain confirmation, or use a pending state plus a reconciliation job.

Indexing and transaction performance

Locks attach to index entries, not just heap rows. An UPDATE on an unindexed foreign key can escalate to table scans and broad lock ranges. Well-chosen indexes narrow lock scope and speed conflict detection. Conversely, every index is another structure to update on INSERT — hot write paths need fewer indexes, not more. Measure with EXPLAIN (ANALYZE, BUFFERS) under realistic concurrency, not just single-query plans.

Common mistakes

Implicit autocommit on multi-step logic — three separate statements without BEGIN are three separate transactions.
Long transactions around HTTP calls — holding locks while waiting for Stripe or SendGrid ties up pool connections and invites deadlocks.
Assuming ORM save() is atomic across models — related object saves may each autocommit unless wrapped in atomic() / @Transactional.
Serializable without retry — serialization failures are normal; retry with backoff or surface a conflict to the user.
SELECT FOR UPDATE on every read — unnecessary locking crushes read scalability; use optimistic versioning or conditional updates instead.
Ignoring isolation in tests — single-threaded CI misses race bugs; add concurrent integration tests for inventory and balance paths.

Decision checklist

Map each user-facing operation to the rows it must read and write atomically.
Default to Read Committed and short transactions for CRUD APIs.
Escalate to Repeatable Read for multi-statement reports inside one transaction.
Use Serializable or SELECT FOR UPDATE only for contested resources (inventory, balances, unique slots).
Add version columns or idempotency keys before reaching for global Serializable.
Log deadlock and serialization abort rates; tune lock order and transaction length.
Document cross-service flows that cannot be one ACID transaction — outbox or saga.

Key takeaways

Transactions bundle work into atomic, durable units governed by ACID — isolation is the lever for concurrent correctness.
Four isolation levels trade throughput for protection against dirty reads, non-repeatable reads, phantoms, and write skew.
MVCC gives snapshot reads without blocking writers; explicit row locks serialize hot paths like inventory decrements.
Deadlocks are expected — detect, abort one side, retry with jitter.
Multi-database and multi-service flows need outbox, sagas, and idempotency — not a bigger single transaction.