Transactions & ACID

Account A holds 500 and account B holds 120. Moving 200 from one to the other is two statements: subtract 200 from A, add 200 to B. Run the first and, for a few microseconds, the money is nowhere — it has left A and has not arrived at B. Almost every time, the second statement runs and nobody notices. Then once, the process is killed, or the connection drops, or a constraint on B’s row rejects the write, and the money is simply gone. Nobody stole it. The system stopped halfway through a sentence.

The problem is not that a write can fail; writes fail all the time. It is that a transfer is one fact and the database was told about it as two. What is missing is a way to say “these statements are a unit” — the way a bank uses a single transfer form with both sides of the move written on it, handed over as one instruction. The clerk files the whole form or rejects the whole form. No filing cabinet anywhere contains half of one. A database transaction is that form, and ACID is the list of promises it makes: atomicity, consistency, isolation, durability.

All or nothing

Atomicity is the first promise and the one that solves the opening problem. The work is bracketed — BEGIN, then the statements, then COMMIT — and everything inside the bracket is provisional. COMMIT makes every write real together. ROLLBACK throws every write away together. There is no partial outcome to handle, which means there is no cleanup code to write.

A crash counts as a rollback. If the connection dies or the server loses power before the commit, the database discards the uncommitted work during recovery without being asked. That is the part people underestimate: atomicity is not only about errors you catch, it is about the failures you never get to observe.

The scene below runs six transfers between those same two accounts. The second one fails at the credit.

Two things are worth watching. The failed transfer leaves nothing behind: the credit to B never lands, and the rollback undoes A’s debit too, so A ends that step where it began it. And the totals hold — A and B open at 500 and 120, and after every transfer, the failed one included, they still add up to 620. Five lines stand and one is struck out — the struck line is what that transfer would have left behind, and it is the only line whose A+B is not 620.

The first transfer in that sequence is this function, called as transfer(db, 'A', 'B', 200):

interface Tx {
  // Each is one UPDATE: balance = balance ± amount, computed by the database.
  debit(accountId: string, amount: number): Promise<void>;
  credit(accountId: string, amount: number): Promise<void>;
}

interface Db {
  transaction<T>(body: (tx: Tx) => Promise<T>): Promise<T>;
}

async function transfer(
  db: Db,
  from: string,
  to: string,
  amount: number,
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx.debit(from, amount);
    // Anything that throws in here — a constraint violation, a frozen
    // account, a dropped connection — takes the debit down with it: neither
    // row changes, and the caller sees an error rather than half a transfer.
    await tx.credit(to, amount);
  });
}

The callback shape is doing real work: the library issues BEGIN before calling it, COMMIT if it returns, and ROLLBACK if it throws, so the rollback path is not a branch anyone can forget. APIs that instead hand you bare begin() and commit() calls are the ones that leak connections when an exception leaves by an unusual route.

interface Tx {
  debit(accountId: string, amount: number): Promise<void>;
  credit(accountId: string, amount: number): Promise<void>;
  balance(accountId: string): Promise<number>;
}

async function transfer(
  db: Db,
  from: string,
  to: string,
  amount: number,
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx.debit(from, amount);
    // Check after the debit, not before: the debit's UPDATE locks the row
    // until the commit, so no other transfer can move this balance meanwhile.
    if ((await tx.balance(from)) < 0) {
      throw new Error(`${from} cannot cover ${amount}`);
    }
    await tx.credit(to, amount);
  });
}

const balances = new Map([['A', 500], ['B', 120]]);

const db: Db = {
  async transaction(body) {
    const working = new Map(balances);
    const result = await body({
      debit: async (id, amount) => {
        working.set(id, (working.get(id) ?? 0) - amount);
      },
      credit: async (id, amount) => {
        working.set(id, (working.get(id) ?? 0) + amount);
      },
      balance: async (id) => working.get(id) ?? 0,
    });
    for (const [id, value] of working) balances.set(id, value);
    return result;
  },
};

await transfer(db, 'A', 'B', 200);
await transfer(db, 'A', 'B', 400).catch((error: unknown) =>
  console.log(String(error)),
);
// Error: A cannot cover 400
console.log(balances.get('A'), balances.get('B')); // 300 320

Consistency and durability

Consistency says a transaction moves the database from one valid state to another valid state, where valid means whatever you declared: foreign keys, unique indexes, check constraints, not-null. If a statement inside the transaction would break one of those rules, the transaction fails and takes the rest of its writes with it.

It is worth being blunt about how little this letter does alone. The database enforces the rules you wrote down and has no opinion about the rest. “Money is conserved” is a database rule only if you expressed it as one — a check constraint, a trigger, a balances table the database maintains. Otherwise it is a property of your code, and the transaction guarantees that your code’s result is kept or discarded whole, not that it was right.

Durability is the most literal letter: once COMMIT returns, the change survives a crash. Pull the power the instant after the transfer commits and A is still 300 when the database comes back. The usual mechanism is write-ahead logging. Before the commit is acknowledged, a compact record of the change is appended to a log and flushed to durable storage; the data pages themselves can be rewritten later, because recovery replays the log. Two caveats travel with it. Durability is only as honest as the hardware underneath — a disk that acknowledges a flush it never performed breaks the promise silently — and under replication, a commit is durable on the machine that answered it and nowhere else, unless you configured commits to wait for replicas.

Isolation, and the dial on it

Atomicity and durability are close to absolute, and consistency is exactly as strong as the rules you wrote. Isolation is different: it is a dial, and the default position is not the one the acronym implies.

Perfect isolation is called serializable: running transactions concurrently produces the same result as running them one after another in some order — any order, but a real one. That is the ideal. It is rarely the default, because enforcing it costs either more locking or more aborted transactions.

The SQL standard names four levels, defined by which anomalies they permit. At read uncommitted, one transaction can see another’s uncommitted writes — a dirty read, and the only level at which the provisional balance in the scene would be visible to anyone else. At read committed you see committed data only, but two reads of the same row within one transaction can return different values, because somebody committed in between. The same gap works across rows, and on the scene’s two accounts it conjures money: a report that reads A and then B, in two statements, can read A as 500 just before the first transfer commits and B as 320 just after it, and total 820 in accounts that have only ever held 620. Repeatable read holds the rows you have already read steady, while a query re-run can still turn up new rows that match its condition — a phantom. Serializable forbids all of it.

Defaults are weaker than serializable essentially everywhere: PostgreSQL, Oracle, and SQL Server default to read committed, and MySQL’s InnoDB defaults to repeatable read. Those are deliberate performance choices and usually the right ones. They also mean the isolation you actually have is whatever your connection is configured for, not what the letter I suggests.

The consequence shows up in a pattern every codebase contains: read a value, decide something in application code, write it back. Two transfers out of A can both read 500, both conclude that 300 is a fine result, and both write 300 — one update silently lost, with no error anywhere, because each transaction was individually valid. The fixes are to let the database do the arithmetic in one statement (balance = balance - 200, as the debit above does), to lock the row while you think (SELECT ... FOR UPDATE), or to run at serializable and retry the transactions the database chooses to abort. That last option is only safe if the code was written to be retried.

Where the guarantee stops

A transaction covers one database. Two services with their own datastores cannot share one, and the distributed version — two-phase commit — is usually avoided because it couples two independent systems into one that stalls when either stalls. The common answer is a saga: a sequence of local transactions, each with a compensating action that undoes it.

Transactions are also cheap only while they are short. An open one holds locks and forces the database to keep older row versions readable, so a transaction waiting on a slow HTTP call blocks other writers for the length of somebody else’s outage. Do the network work first, then open the transaction.