Skip to content
learn
  1. Learn
  2. High Level System Design
  3. Databases and Storage Fundamentals

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.

Beginner 12 min Last reviewed

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 since BEGIN is undone (ROLLBACK).
  • Consistency: the database moves from one valid state to another; constraints such as balance >= 0 and 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 COMMIT returns, the change survives a crash or power loss.
transaction.pyruns with python3 (standard library sqlite3)
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):

Write-ahead logging, commit and crash recovery A transaction's changes are appended to the write-ahead log, then a commit record is appended and flushed to disk, and only then is the commit acknowledged. Data pages are updated later in memory and written to disk in the background. After a crash, recovery replays the log: committed transactions are redone and uncommitted ones are undone. 1. append 2. flush, then ack 3. background write after a crash: replay Transaction UPDATE, UPDATE WAL (append-only) change . change . COMMIT COMMIT ok Data pages updated later, any order Crash recovery redo committed, undo the rest Figure 1. Changes go to the append-only log first; COMMIT is acknowledged once the commit record is safely on disk (glowing). Data pages catch up later. After a crash, replaying the log redoes committed work and discards the rest.
  1. Every change is first appended to the log. Appending is a fast, sequential write.
  2. 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.
  3. 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

OptionWins whenLoses 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

  1. 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.

  2. What does durability guarantee?

    Show answer

    B. Once COMMIT returns, the change survives crashes The commit record is on disk before the database acknowledges.

  3. 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.

  4. 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.

Get new lessons by email

Get new posts on backend and AI internals. No spam, unsubscribe anytime.

Your email is only used to send new posts. Privacy