Transactions
Grouping several reads and writes so they take effect all together or not at all, isolated from concurrent work to a defined degree.
Storage & state
Learn it
Many operations are really several writes: debit one account and credit another; mark an order paid and record a fulfilment task. If the process crashes between them, or another request interleaves, the data ends up in a state no operation intended.
A transaction brackets writes with
BEGINandCOMMITso they take effect together or not at all.- Atomicity: changes go to a write-ahead log; on recovery, uncommitted transactions are rolled back, so all the writes are visible or none are.
- Durability:
COMMITreturns only after the log record is flushed to stable storage (and, if configured, replicas). See Durability. - Isolation: concurrent transactions are kept from seeing each other's partial work, to a configurable degree.
Think first
Under Postgres's default isolation (read committed), two transactions each run SELECT balance (100), check it's at least 80, then UPDATE balance = balance - 80. Both commit. What's the balance?A transaction covers one database. It can't include an HTTP call to a payment provider, a message broker, or an email. Coordinating across systems needs other tools: the Transactional outbox, Idempotency, and Reconciliation.
Check
Code calls a payment API inside BEGIN … COMMIT. The API charges the card, then the COMMIT fails. What state are you in?
Quick reference
The same ideas, condensed for revision.
How it goes wrong
- External call inside a transaction
- Holds locks during a slow call; and if the call succeeds but the commit fails, the outside world changed while your data did not.
- Check-then-act under read committed
- Two transactions read the same value and both act on it.
- Unretried serialization failures
- Stricter isolation aborts transactions; code that does not retry surfaces errors to users.
- Long transactions
- Block vacuum and other writers, and inflate replication lag.
Instead, consider
- Single conditional statement
- The whole operation fits in one UPDATE or INSERT … ON CONFLICT, which is atomic on its own.
- Sagas (compensating steps)
- The operation spans services or systems that cannot share a transaction.
- Append-only events with idempotent consumers
- You can express the change as a fact and derive state from it.
In practice
- Postgres, MySQL/InnoDB
- MVCC with configurable isolation levels.
- SQLite
- Serializable by design via a single writer.
- Distributed SQL (Spanner, CockroachDB)
- Transactions across partitions at the cost of coordination latency.
It assumes
- All the state that must change together lives in the same database.
- Transactions are short. Holding one open across slow network calls holds locks and connections.
- Code that runs at stricter isolation levels is prepared to retry on serialization failures.
Explain it in your own words
Where you practise it
- A reliable video processing pipeline
How do jobs reach workers? · Write the claim and the completion
- A payment workflow that never double-charges
- Live queries: screens that update in real time
Twelve tasks, eleven cards · Seven tasks in a column of five · Write the commit check
- Sharding Postgres while it is running
Further reading
Engineers describing it in systems they run.
- How Convex Works
Convex · Sujay Jayakar · Post, Apr 2024
A walk through a reactive database from the inside: the transaction log, read sets, optimistic concurrency, and how a write finds the subscriptions it affects.
- Implementing Stripe-like Idempotency Keys in Postgres
Stripe · Brandur Leach · Post, Oct 2017
The long version, with code: how to make a multi-step request safe to retry when some of its steps call other services.
Related concepts
- Concurrency control
Making read-decide-write sequences safe when other actors may change the same data in between: locks, conditional writes and constraints.
- Transactional outbox
Recording outgoing messages in the same database transaction as the state change, then delivering them separately, to avoid the dual-write problem.
- Durability
What has to have happened before a system may say "saved": which failures the data must survive, and where that guarantee is actually made.
- Idempotency
Designing an operation so that performing it twice has the same effect as performing it once, which is what makes retries safe.
- Delivery guarantees
At-most-once, at-least-once, and why 'exactly-once' is achieved by making duplicates harmless rather than by preventing them.
- State machines for business state
Modelling an entity's lifecycle as explicit states and allowed transitions, enforced with conditional updates so concurrent or stale actors cannot corrupt it.