ACID and transactions in plain language

Moving money between two accounts takes two steps: take it from one, add it to the other. If the power fails between the steps, the money has vanished. A transaction prevents that by making the steps one indivisible unit. The guarantees it gives are summarised as ACID.

The four letters

Atomicity: all or nothing. Either every statement in the transaction takes effect or none does. If one fails, the database undoes the rest.

Consistency: the rules hold after every commit. A transaction takes the database from one valid state to another. Constraints (NOT NULL, UNIQUE, CHECK, foreign keys) are the rules, and a change that would break one is refused.

Isolation: transactions do not see each other's half-finished work. Two people transferring money at the same moment do not interfere, as if they had taken turns. How strictly this is enforced is a setting, covered below.

Durability: committed means safe. Once the database says a commit succeeded, the change survives a crash. It is written to disk in a way that can be recovered.

Atomicity, demonstrated

An account may hold between 0 and 1,000. This script moves money, and the second transfer is built to fail after its first step has already run.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript(
    """
    CREATE TABLE accounts (
        id      INTEGER PRIMARY KEY,
        owner   TEXT,
        balance INTEGER CHECK (balance BETWEEN 0 AND 1000)
    );
    INSERT INTO accounts VALUES (1, 'Ana', 100), (2, 'Ben', 990);
    """
)


def transfer(source, target, amount):
    try:
        with conn:  # one transaction: commit on success, roll back on any error
            conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, source))
            print("   first update done, balances now", balances())
            conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, target))
        print(f"moved {amount} from {source} to {target}")
    except sqlite3.IntegrityError as error:
        print(f"refused ({error})")


def balances():
    return conn.execute("SELECT owner, balance FROM accounts ORDER BY id").fetchall()


print("start      ", balances())
transfer(1, 2, 5)
print("after ok   ", balances())
transfer(1, 2, 30)
print("after fail ", balances())

The output:

start       [('Ana', 100), ('Ben', 990)]
   first update done, balances now [('Ana', 95), ('Ben', 990)]
moved 5 from 1 to 2
after ok    [('Ana', 95), ('Ben', 995)]
   first update done, balances now [('Ana', 65), ('Ben', 995)]
refused (CHECK constraint failed: balance BETWEEN 0 AND 1000)
after fail  [('Ana', 95), ('Ben', 995)]

In the failed transfer the first update really did run (Ana briefly had 65), but Ben's balance would have passed 1,000, so the second update was refused. Because both were in one transaction, Ana's balance went back to 95. Without the transaction the money would have been lost. The CHECK constraint is consistency at work: the invalid state was never allowed to stand.

Isolation: the part with choices

Perfect isolation is expensive, so databases offer levels. They differ in which oddities you may see when other transactions run at the same time:

The standard levels, from loosest to strictest, are read uncommitted, read committed, repeatable read and serializable. Most systems default to read committed or repeatable read, and the exact guarantees differ by database, so read yours. The practical advice is simple: use the default, keep transactions short, and reach for a stricter level only when you can name the anomaly that is hurting you.

What a transaction does not do

• It does not make slow things fast. An open transaction holds locks and can block others, so do the thinking before it begins.
• It does not span systems. A transaction in your database cannot undo an e-mail already sent or a payment already taken from a card.
• It does not replace constraints. Code that checks a rule before writing can be raced by a second request. A constraint in the database cannot.
• It cannot save a bad design. If a fact lives in two places, a transaction can keep them in step, but it is better that it live in one.