What are the ACID guarantees, and how does the transaction log provide them?
Quick Answer
The transaction log records every change before the data pages are written to disk (write-ahead logging). This is what makes atomicity and durability work: on COMMIT, the log record is hardened to disk; on a crash, recovery redoes committed transactions that hadn't been flushed and undoes uncommitted ones from the log.
ACID is atomicity, consistency, isolation, durability. The transaction log is what delivers atomicity and durability. SQL Server uses write-ahead logging: before it changes a data page, it writes the change to the log, and a commit doesn't return until those log records are safely on disk. The actual data pages can be written later. If the server crashes, recovery reads the log and redoes any committed transactions whose pages hadn't been flushed, and undoes anything that was still in progress. Isolation comes from locking or row versioning, and consistency from the constraints — the log handles the other two.
- Write-ahead logging (WAL): a page change is logged before the dirty page is written. A
COMMITonly returns once the relevant log records are on disk — the data pages can be lazily written later by a checkpoint. - Crash recovery has three phases: analysis (what was in flight), redo (re-apply committed changes from the log), undo (roll back uncommitted).
- This is why the log grows if you never back it up in Full recovery, and why long-open transactions prevent log truncation.
- Isolation is provided by locking / row-versioning, not the log; consistency by constraints.