System Design HLD-06.03
Transactions and ACID
Grouping operations so they all succeed or all fail: atomicity, consistency, isolation and durability, and how the write-ahead log survives crashes.
The big idea
Moving $50 from Alice to Bob is two updates: subtract from Alice, add to Bob. If the server crashes between them, $50 has vanished. If two transfers from Alice run at the same moment, both might see enough money and overdraw her. A transactionA group of database operations that the database treats as one unit: either all of them take effect, or none do. wraps both updates into one unit that either completes entirely or does not happen at all, and that does not interfere with other transactions running at the same time.
The guarantees a transaction gives are summarized as ACIDAtomicity, Consistency, Isolation, Durability: the four guarantees of database transactions.. They are why banks, shops and booking systems run on transactional databases, and why "just write to two places" is never as simple as it sounds.
Think of a vending machine. You put money in and either get your snack or get your money back. A machine that sometimes keeps the money and gives nothing is broken, and that partial outcome is exactly what transactions prevent.
How it works
Step 1: The four guarantees
- Atomicity: all or nothing. If anything fails before
COMMIT, every change sinceBEGINis undone (ROLLBACK). - Consistency: the database moves from one valid state to another; constraints such as
balance >= 0and foreign keys hold before and after. - Isolation: concurrent transactions do not see each other's half-finished work. How strictly is configurable, and the subject of Isolation levels and anomalies .
- Durability: once
COMMITreturns, the change survives a crash or power loss.
import sqlite3
db = sqlite3.connect(":memory:", isolation_level=None) # we control BEGIN/COMMIT ourselves
db.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INT CHECK (balance >= 0))")
db.executemany("INSERT INTO accounts VALUES (?, ?)", [("alice", 100), ("bob", 20)])
def balances():
return dict(db.execute("SELECT name, balance FROM accounts"))
def transfer(src, dst, amount, crash_halfway=False):
db.execute("BEGIN")
try:
db.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, src))
if crash_halfway:
raise RuntimeError("server crashed between the two updates")
db.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", (amount, dst))
db.execute("COMMIT")
return "committed"
except (RuntimeError, sqlite3.IntegrityError) as e:
db.execute("ROLLBACK") # undo everything since BEGIN
return f"rolled back ({e})"
print("start: ", balances())
print("transfer 30: ", transfer("alice", "bob", 30), balances())
print("transfer 50, crash: ", transfer("alice", "bob", 50, crash_halfway=True), balances())
print("transfer 500 (too much):", transfer("alice", "bob", 500), balances())
print("total money is always", sum(balances().values()))Output
start: {'alice': 100, 'bob': 20}
transfer 30: committed {'alice': 70, 'bob': 50}
transfer 50, crash: rolled back (server crashed between the two updates) {'alice': 70, 'bob': 50}
transfer 500 (too much): rolled back (CHECK constraint failed: balance >= 0) {'alice': 70, 'bob': 50}
total money is always 120 The first transfer commits. The second fails between its two updates, and the rollback restores Alice's balance as if nothing happened. The third breaks the CHECK constraint and is rolled back too. Whatever happens, the total stays 120: no money is created or destroyed.
Step 2: The write-ahead log
How can a database promise durability and atomicity when data pages are written to disk in random order and a crash can happen at any instant? With a write-ahead logAn append-only log where every change is recorded and flushed to disk before the data pages themselves are modified, so the database can recover after a crash. (WAL):
- Every change is first appended to the log. Appending is a fast, sequential write.
- On
COMMIT, the database writes a commit record and forces the log to disk (fsync). Only then does it tell the client the transaction succeeded. - The actual table and index pages are modified in memory and written back to disk later, in whatever order is efficient.
After a crash, the database reads the log: transactions with a commit record are redone, so durability holds; transactions without one are undone, so atomicity holds. The same log also feeds replication to other machines ( Replication: leader-follower ) and change data capture.
Going deeper
Durability has knobs
Forcing the log to disk on every commit costs a few milliseconds on most hardware. Databases batch commits from many transactions into one flush (group commit), and some let you relax durability, for example acknowledging before the flush and risking the last fraction of a second of commits on power loss. Know which setting your database uses.
Transactions across services
ACID transactions live inside one database. Once an operation spans two databases or two services, such as charging a card and creating an order, you need two-phase commit ( Two-phase commit ) or, more often, sagas ( Sagas: choreography and orchestration ).
Trade-offs
| Option | Wins when | Loses when |
|---|---|---|
| Short transactions | High-concurrency systems: locks and versions are held briefly | Nothing, as long as the work fits; keep network calls out of transactions |
| Strict durability (fsync on commit) | Money, orders, anything that must never be lost | A few milliseconds per commit, softened by group commit |
| Relaxed durability | High-volume data where losing the last moment of writes is acceptable | Recent commits on a power failure |
Common mistakes
- Doing related writes without a transaction, so a crash leaves half of them applied.
- Holding a transaction open while calling another service or waiting for a user, blocking others.
- Assuming a transaction spans two databases. It does not.
- Swallowing errors inside a transaction and committing anyway.
Check yourself
-
A transfer subtracts from Alice, then the server crashes before adding to Bob. With a transaction, what happens?
Show answer
B. The partial change is rolled back, so neither balance changes Atomicity: without a commit, recovery undoes the subtraction.
-
What does durability guarantee?
Show answer
B. Once COMMIT returns, the change survives crashes The commit record is on disk before the database acknowledges.
-
Why write to a log before changing data pages?
Show answer
B. Appending is fast and sequential, and the log lets recovery redo or undo changes after a crash The WAL is the source of truth for recovery, so pages can be written lazily.
-
Which is a bad idea inside a database transaction?
Show answer
B. Calling a slow external payment API Long external calls hold locks and versions open, blocking other transactions.
Recap
- A transaction makes several operations one unit: all succeed or none do.
- ACID: atomic, consistent, isolated, durable.
- The write-ahead log records changes first and flushes the commit record before acknowledging.
- Crash recovery replays the log: redo committed work, undo the rest.
- Keep transactions short, and remember they stop at the edge of one database.