ACID and write-ahead logging

ACID properties

Databases make four durability and consistency guarantees, collectively known as ACID:

Atomicity — a transaction is all-or-nothing. If you transfer money between two bank accounts (UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2;), either both updates commit together or neither does. A crash or error mid-transaction leaves the database as if the transaction never started.

Consistency — a committed transaction always moves the database from one valid state to another valid state, as defined by the schema's constraints (foreign keys, check constraints, not-null constraints). The database never commits a row that violates a foreign key reference.

Isolation — concurrent transactions do not see each other's uncommitted work. If transaction A is updating a row and transaction B reads that row simultaneously, B sees the row as it was before A started — not A's partial update. The degree of isolation (and the corresponding performance cost) is configurable via isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE).

Durability — once a transaction is committed, its effects survive any subsequent crash. The database guarantees it can replay the committed data even if the server loses power the millisecond after COMMIT returns.

Write-ahead logging (WAL)

Durability is hard to implement efficiently. Writing data directly to its final location on disk — modifying a B-tree page in place — is dangerous: a crash mid-write leaves a corrupted page. And random writes to scattered disk locations are slow.

The solution is write-ahead logging (WAL). Before any page is modified, a description of the change (a log record) is written to an append-only WAL file on disk. Append operations are sequential and fast. Only after the log record is durably on disk does the engine modify the actual data page (which it may keep in a memory buffer for a while before flushing).

COMMIT transaction T
  → write log record: "T committed; row 42 changed from X to Y"
  → fsync (ensure log is on disk)
  → return success to client
  (data page update happens later, asynchronously)

On crash recovery, the engine replays the WAL from the last checkpoint forward. Any transaction marked committed in the log but not yet reflected in the data files is replayed (redo). Any transaction that was in-progress at crash time is rolled back (undo). The database returns to a consistent state without having lost any committed work.

WAL also powers streaming replication: the primary sends its WAL stream to replica servers, which replay it to stay in sync. The replica's data is guaranteed to be consistent precisely because WAL records are self-contained descriptions of state changes.