Database transactions - System Design Guide

Overview

A transaction groups several operations into one unit that either fully succeeds or has no effect. ACID describes the guarantees: Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions do not see each other's partial work), and Durability (committed data survives crashes).

Isolation levels trade correctness for concurrency. Read Committed (the default in PostgreSQL) prevents dirty reads but allows lost updates and write skew unless you lock or check; Repeatable Read gives a consistent snapshot; Serializable makes concurrent transactions behave as if they ran one at a time, at the cost of retries. Knowing which anomalies each level allows is essential for correct money and inventory logic.

A bank teller's transfer slip

Moving money from savings to checking is one slip: the teller never debits one account without crediting the other. If the power fails halfway, the slip is voided and both balances are untouched.

When to use it

Money transfers, orders, bookings, inventory updates.
Any multi-row or multi-table change that must stay consistent.
Preventing race conditions between concurrent requests.
Implementing job queues and counters safely.

Where it shows up in interviews

Preventing double booking

Recognize it when: two users try to book the last seat.

Design a ticket booking system
Design a hotel reservation system

Money movement

Recognize it when: balances must never go negative or lose money.

Design a payment wallet
Design a ledger service

Where it is used in real software

Double-entry ledgers

Payment companies record every movement as balanced debit and credit entries inside one transaction.

SELECT FOR UPDATE

Booking systems lock the seat row during checkout so two transactions cannot both claim it.

Serializable in CockroachDB

CockroachDB defaults to serializable isolation and asks clients to retry on conflicts.

Key terms

Atomicity: All operations commit or none do (rollback).
Isolation level: How much concurrent transactions can see of each other.
Dirty read / non-repeatable read / phantom: Reading uncommitted data / a row changing between reads / new rows appearing.
Lost update: Two transactions read, modify, and write the same row; one overwrites the other.
Optimistic vs pessimistic locking: Check a version at write time vs lock rows up front.

How it works, step by step

1
BEGIN
Start the transaction.
2
Read what you need, locking if necessary
SELECT ... FOR UPDATE for rows you will change based on their value.
3
Validate business rules
Balance sufficient, seat still available.
4
Write changes
Updates and inserts across tables.
5
COMMIT or ROLLBACK
On serialization failures, retry the whole transaction.

The lost update anomaly

Balance = 100; two withdrawals of 30 at the same time, read-modify-write without locking

Step	Transaction A	Transaction B	Balance in DB
1	read balance: 100	-	100
2	-	read balance: 100	100
3	write 100 - 30 = 70	-	70
4	-	write 100 - 30 = 70	70 (should be 40)

NOWStep: 1 | Transaction A: read balance: 100 | Transaction B: - | Balance in DB: 100

Fixes: atomic update (SET balance = balance - 30 WHERE balance >= 30), SELECT FOR UPDATE, optimistic locking with a version column, or Serializable isolation with retries.

Implementation

1-- Book the last seat safely (pessimistic lock)2BEGIN;3SELECT id FROM seats WHERE event_id = 7 AND seat_no = 'A12' AND status = 'available'4FOR UPDATE;                                  -- second transaction waits here5UPDATE seats SET status = 'held', held_by = 42, held_until = now() + interval '10 minutes'6WHERE event_id = 7 AND seat_no = 'A12';7COMMIT;8 9-- Atomic conditional update: no read-then-write race10UPDATE accounts SET balance = balance - 30 WHERE id = 1 AND balance >= 30;11-- 0 rows updated means insufficient funds12 13-- Optimistic locking with a version column14UPDATE documents SET body = $1, version = version + 115WHERE id = $2 AND version = $3;             -- 0 rows: someone else updated first, reload and retry

Complexity and performance

Commit durability1 fsync of WAL

Group commit batches many transactions.

Lock waitContention dependent

Hot rows serialize access.

Trade-offs

Isolation vs throughput

Serializable prevents all anomalies but causes more aborts and retries; lower levels need explicit locks or checks.

Pessimistic vs optimistic

Locks suit high contention; optimistic version checks suit low contention and avoid blocking.

Variants and related techniques

Distributed transactions

Two-phase commit across databases, or sagas with compensation.

Savepoints

Partial rollback within a transaction.

Common mistakes

Calling external APIs inside a transaction.
Fix: Holds locks for long periods; call external services before or after, and use an outbox for side effects.
Read-then-write without locks.
Fix: Use atomic updates, FOR UPDATE, or version checks.
Not retrying serialization failures.
Fix: Wrap transactions in retry logic for error codes like 40001.

Interview questions

How do you prevent two users from booking the same seat?

Use a transaction with SELECT ... FOR UPDATE on the seat row, or a conditional update that changes status only if it is still available, plus a unique constraint on (event_id, seat_no) for bookings.

What is write skew?

Two transactions read the same data, each makes a decision that is valid alone, and they write different rows, violating a rule together (for example, both doctors go off call). Serializable isolation or explicit locks prevent it.

Practice problems

Problem	Difficulty	What it trains
Implement a safe money transfer	Easy	Atomicity.
Design seat booking with holds and expiry	Medium	Locks and timeouts.
Design a double-entry ledger	Hard	Invariants and idempotency.