ACID, Isolation Levels, and Redo/Undo — DB Transactions (DB Series 2/4)

ACID: four promises a transaction makes

A transaction is a unit of work that is logically one thing. There’s a reason bank transfers are the eternal example: taking money out of A and adding it to B must both happen or neither happen.

START TRANSACTION;
UPDATE account SET balance = balance - 200000 WHERE id = 'A';
UPDATE account SET balance = balance + 200000 WHERE id = 'B';
COMMIT;  -- until this point, ROLLBACK can undo all of it

A and D come for free from the DBMS. C is different: you have to define what a consistent state is, through constraints and validation. And for I, you have to pick the isolation level. “It’s in a transaction, so it’s safe” stops being true exactly at C and I.

Isolation levels: three anomalies, four settings

Perfect isolation, meaning results identical to running transactions one at a time, kills concurrency. So the SQL standard defines four levels by which anomalies you’re willing to put up with. Here are the anomalies first.

• Dirty read: you read a value another transaction hasn’t committed yet. If it rolls back, you read a value that never existed.
• Non-repeatable read: you read the same row twice in one transaction and get different values. Someone else updated and committed in between.
• Phantom read: you run the same query twice and a row appears that wasn’t there before. Someone else inserted and committed in between.

The quiet trap is that defaults differ between databases. MySQL (InnoDB) defaults to REPEATABLE READ, while PostgreSQL and Oracle default to READ COMMITTED. The same code can behave differently after you switch databases.

The asterisk in the table depends on the database too. InnoDB’s REPEATABLE READ serves plain SELECTs from a snapshot and takes gap (next-key) locks for locking reads like FOR UPDATE, which prevents most phantoms. PostgreSQL’s REPEATABLE READ is snapshot isolation, so phantoms don’t show up there either.

Modern databases don’t solve this with locks alone. The main tool is MVCC (multi-version concurrency control), which shows each transaction a snapshot as of its own point in time. That’s why reads don’t block writes and writes don’t block reads.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM account WHERE id = 'A';  -- ① 1000000

SELECT balance FROM account WHERE id = 'A';  -- ③ 800000, same transaction, different value
COMMIT;

UPDATE account SET balance = balance - 200000 WHERE id = 'A';  -- ② commits immediately (autocommit)

Recovery: commits survive a power cut

A database doesn’t write changes straight to disk. For speed, it modifies pages in memory (the buffer pool) and flushes them to disk later in batches. So what happens if the server dies in between? The log fills that gap.

The rule is WAL (write-ahead logging): write the log to disk before the data. The log carries two kinds of information.

• Redo log: the value after the change. Used to replay changes that were committed but hadn’t reached the data files yet.
• Undo log: the value before the change. Used to roll back changes from transactions that died before committing. In InnoDB and Oracle, the old versions MVCC shows to readers come from here too. PostgreSQL keeps old row versions in the table itself, so it has no separate undo log.

A checkpoint periodically flushes buffered changes to disk and leaves a marker. Thanks to that, a restart only replays the log from the last checkpoint instead of from the beginning. On restart, the database first uses Redo to rebuild the state just before the crash (anything committed must survive: durability). Then it uses Undo to roll back the transactions that never committed (they must leave no trace: atomicity).

So a successful COMMIT doesn’t mean the data is all on disk. It means the redo log is safely on disk. That’s enough, because whenever the server dies, the log can rebuild the data. And since a commit only waits for one log fsync, it’s fast too. This is why WAL is the heart of every serious database, and of systems like Kafka as well.

That guarantee depends on configuration, though. In MySQL, innodb_flush_log_at_trx_commit defaults to 1, which fsyncs the log on every commit. Set it to 0 for speed and even a MySQL crash can lose roughly the last second of commits; set it to 2 and it takes an OS crash or power loss. PostgreSQL’s synchronous_commit = off makes the same trade.

Wrap-up

• In ACID, the DBMS covers A and D with its logs, and C and I are on you.
• Isolation levels are a dial for which anomalies you allow. Start by checking your database’s default (REPEATABLE READ in MySQL, READ COMMITTED in PostgreSQL).
• Where money is involved, protect it with explicit locks, not the isolation level.
• WAL writes the log first and the data later. After a crash, committed work is redone, uncommitted work is undone.

The next part is about design: why normalize and where to stop, and whether business logic belongs in an ORM or in stored procedures.