The All-or-Nothing Guarantee
Atomicity is the "A" in ACID and provides the fundamental guarantee that a transaction is an indivisible unit of work: either every operation within it completes successfully, or none of them take effect. There is no middle ground — partial results are never visible to other transactions or left behind on disk.
Why Atomicity Matters
Consider a bank transfer that debits Account A and credits Account B. Without atomicity, a crash after the debit but before the credit would silently destroy money. Atomicity ensures the database either completes both operations or rolls back both, preserving the total balance at all times.
The Undo Log
Atomicity is implemented using an undo log (also called the rollback log). Before any modification is applied to a database page, the system records a before-image — the original value of the data that is about to change. These before-images are written to the undo log sequentially. If the transaction needs to be rolled back (either explicitly via ROLLBACK or implicitly due to a crash), the database traverses the undo log in reverse order and restores every modified value to its original state.
Savepoints
SQL supports savepoints, which allow partial rollback within a transaction. A savepoint marks a position in the transaction's undo log. You can issue SAVEPOINT sp1 at any point, continue executing statements, and later ROLLBACK TO sp1 to undo only the changes made after sp1 — without aborting the entire transaction. The operations before the savepoint remain intact. This is especially useful in application code where you want to retry a portion of complex logic without losing earlier work.
Statement-Level Atomicity
Even a single SQL statement is atomic. If an UPDATE modifies 10,000 rows and fails on row 7,500 (e.g., due to a constraint violation), all 7,499 previously modified rows are rolled back. The database treats each statement as an implicit mini-transaction. This is implemented using internal savepoints — the engine sets a savepoint before each statement and rolls back to it if the statement fails.
WAL and Crash Atomicity
The Write-Ahead Log (WAL) is critical for atomicity across crashes. Because undo information is recorded in the WAL before dirty pages are flushed, the recovery system can always find and reverse uncommitted changes. During crash recovery, the ARIES undo phase traverses the log backward for every active (uncommitted) transaction, applying each before-image to restore the original page state. Compensation Log Records (CLRs) prevent double-undo if the system crashes again during recovery.
Savepoints in Practice
A multi-step order processing system uses savepoints to handle partial failures gracefully:
BEGIN;
-- Step 1: Create the order
INSERT INTO orders (id, customer_id, total)
VALUES (1001, 42, 299.99);
SAVEPOINT after_order;
-- Step 2: Reserve inventory (might fail)
UPDATE inventory SET reserved = reserved + 1
WHERE product_id = 777 AND available > 0;
-- If no rows updated, the product is out of stock
-- Roll back to savepoint and try a substitute product
ROLLBACK TO after_order;
UPDATE inventory SET reserved = reserved + 1
WHERE product_id = 888 AND available > 0;
-- Step 3: Charge payment
SAVEPOINT after_inventory;
INSERT INTO payments (order_id, amount, status)
VALUES (1001, 299.99, 'charged');
COMMIT; -- All or nothing
Key observations:
ROLLBACK TO after_orderundoes the failed inventory reservation but keeps the order row- If the entire transaction is rolled back (or the system crashes before COMMIT), everything — order, inventory, payment — is cleanly reversed
- Each individual statement (like the UPDATE) is itself atomic: if it touches 50 rows and fails on row 30, all 50 are untouched
Undo log for this transaction:
- Before-image of
ordersrow (empty → inserted) - Before-image of
inventoryrow for product 777 SAVEPOINT after_ordermarker- Rollback undoes entry 2, returns to marker 3
- Before-image of
inventoryrow for product 888 - Before-image of
paymentsrow