Transactions & ACID

A transaction is a unit of work — a sequence of operations that the database commits or aborts as a whole. Either all of them happen, or none of them do. The classic example is a bank transfer: debit account A by $100, credit account B by $100, log the transfer. If the system crashes after the debit but before the credit, you've destroyed $100.

The four guarantees a transactional database provides are called ACID:

• Atomicity — all-or-nothing. Either every operation in the transaction commits, or none do. If anything fails, the entire transaction rolls back. ('A' is for Atomic, not 'all-or-nothing' — but the meaning is the same.)
• Consistency — the database moves from one valid state to another. Constraints (foreign keys, uniqueness, checks) are never violated. Note: this 'C' is application-defined — the database enforces the constraints you've declared.
• Isolation — concurrent transactions behave as if they ran sequentially. Their effects don't interleave. (In practice, perfect isolation is expensive; databases offer a spectrum of isolation levels — see the Isolation Levels concept.)
• Durability — once committed, the transaction's effects survive crashes, power loss, and restarts. Typically implemented via the write-ahead log (WAL).

The transfer example is impossible without ACID. Without atomicity, a mid-transfer crash leaves the books unbalanced. Without durability, a crash after commit loses the customer's money. Without isolation, two simultaneous transfers between the same accounts produce wrong balances.

Atomicity is implemented with an undo log: the database records enough information to reverse each operation if the transaction aborts. A crash mid-transaction triggers rollback on restart — every uncommitted transaction is undone before the database accepts new connections.

Durability is implemented with a redo log — the write-ahead log (WAL). Before any change is applied to the data files, the database writes the change to the WAL (an append-only file) and fsyncs it. On crash, the data files may be inconsistent, but the WAL has every committed change; the database replays the WAL on startup to bring the data files up to date.

This is why the WAL is the linchpin of durability — losing the WAL means losing committed transactions even if the data files are intact. Most databases replicate the WAL to a standby for high availability (see the WAL and Replication concepts).

The key insight: atomicity and durability are about surviving crashes. The database promises that even if you pull the power cord mid-transaction or mid-commit, the result is consistent: committed transactions survive, uncommitted ones don't.

Isolation is the hardest guarantee to provide efficiently. True isolation (serializability) means concurrent transactions produce the same result as if they had run one at a time. Achieving this naively requires locking every row a transaction touches for its full duration — which kills concurrency.

Real databases offer a spectrum of isolation levels: Read Uncommitted (almost no isolation), Read Committed (PostgreSQL default), Repeatable Read, and Serializable. Lower levels allow more concurrency but expose more anomalies (dirty reads, non-repeatable reads, phantom reads). The choice is a per-transaction trade-off between correctness and performance.

See the Isolation Levels concept for the full hierarchy. For this lesson, the key takeaway is that 'ACID' doesn't specify which isolation level — a database is still ACID with Read Committed isolation, but that's weaker than what most people assume 'ACID' guarantees. Always ask 'which isolation level?' when someone says 'ACID'.

ACID transactions are straightforward within a single database — the database engine coordinates them internally. The hard problem is transactions across multiple databases or services: deduct from the Billing DB, write to the Inventory DB, send to the Notification service. Each has its own transaction log and crash model.

Two-phase commit (2PC) is the classical solution: a coordinator asks every participant to 'prepare' the transaction (Phase 1), and if all agree, tells them to 'commit' (Phase 2). The guarantees are strong — atomic across databases — but the costs are severe: blocking (if the coordinator dies, participants hold locks indefinitely), slow (multiple network round trips), and fragile (any participant failure aborts the whole transaction). 2PC is used in sharded SQL databases (PostgreSQL-XL, MySQL NDB) but rarely across services in modern architectures.

Sagas are the modern alternative for distributed transactions. A saga is a sequence of local transactions, each with a compensating action that undoes its effect on failure. If step 3 fails, the saga runs compensations for steps 1 and 2. Sagas don't provide isolation across services (intermediate states are visible), but they're resilient, non-blocking, and align with how microservices actually work.

The rule of thumb: stay within one database whenever possible. Cross-database transactions are 10× harder. If you must, prefer sagas over 2PC unless you have a strict need for the latter (and even then, measure the latency cost).

See the Saga Pattern and Two-Phase Commit concepts for deep dives.

Use transactions when:

1. Money or count invariants — payments, balances, inventory. A half-completed transfer is a P0 incident.
2. Multi-row updates that must be atomic — order with line items, user with preferences. Partial writes corrupt application state.
3. Read-modify-write sequences — read balance, check sufficient funds, deduct. Without isolation, two concurrent withdrawals can both see sufficient funds and overdraft the account.
4. Cross-table invariants — deleting a user should atomically delete their sessions, posts, etc. (or use ON DELETE CASCADE within the same transaction).

Don't use transactions for:

1. Single-row inserts/updates — already atomic.
2. Long-running operations — transactions hold locks; long ones block other work. Move long work out of the transaction (background job) and use a small transaction to record the result.
3. Operations that can be eventually consistent — updating a search index, sending a notification, recomputing a counter. These are fine as eventual — and using a transaction unnecessarily slows the primary path.
4. Cross-service coordination — use sagas instead.

The common anti-pattern is the 'big transaction' — wrapping an entire request handler in one transaction so that an external API call to a payment processor happens inside the DB transaction, holding locks for the duration. Move the external call out: open the transaction, write the intent, commit, call the external service, then update the result. Smaller transactions are faster, hold fewer locks, and fail more gracefully.

Mental model. Think of Transactions & ACID as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Transactions & ACID mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”

Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.

Answer this without notes: When would you choose Transactions & ACID, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.

Numerical sanity check

A rough capacity check: required write throughput ≈ peak writes/s × average record size. At 5,000 writes/s and 2 KB average payloads, the raw incoming data stream is about 10 MB/s before indexes, replication and overhead.