Transactions and ACID

Imagine you use a banking app to send £100 from your current account to a friend. Behind the scenes the database must do two separate jobs:

subtract £100 from your balance, and
add £100 to your friend's balance.

These are two separate UPDATE statements. But think about what happens if the server loses power in between them: the £100 has left your account but never arrived in your friend's. The money has simply vanished. The bank's books no longer balance, and no amount of apologising will make the missing money reappear.

The fix is to treat the two updates as one indivisible unit — a transaction. Either both updates happen, or neither does. There is no in-between. This page is about how databases make that guarantee, and the four letters — ACID — that describe the promises a reliable database keeps.

What is a transaction?

A transaction is a group of one or more database operations that are treated as a single, indivisible unit of work. The database bundles them together and promises to apply them all or apply none — never just some of them.

In SQL you mark out a transaction explicitly. You begin it, run your statements, and then either COMMIT — make every change permanent — or ROLLBACK — undo everything back to how it was before you started:

BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- take from you UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- give to friend COMMIT; -- both changes made permanent

Everything between BEGIN TRANSACTION and COMMIT succeeds together. If anything goes wrong before the COMMIT — a crash, an error, a rule violation — the database performs a ROLLBACK and the accounts look exactly as they did before you started. It is as if the half-finished transfer never happened.

It is borrowed straight from banking and shop-keeping, where a transaction has always meant a complete exchange: money for goods, in one agreed swap. A database transaction keeps that same all-or-nothing spirit — the operations only “count” once the whole deal is settled. The four ACID properties were named in a 1983 paper by Theo Härder and Andreas Reuter, tidying up ideas Jim Gray had been developing through the 1970s.

Commit, or roll back

Here is the money transfer as a transaction. Two accounts start with valid balances. Step through the commit path, where both updates succeed and the money moves cleanly. Then hit Refresh or read on for the rollback path below, where a crash strikes halfway and the database undoes the damage.

On the commit path, notice that the total across both accounts never changes: £100 simply moves from one column to the other. The books always balance. That is exactly the state of affairs a transaction protects.

When it goes wrong: the rollback

Now watch the dangerous case. The first update runs — £100 has left account 1 — and then the machine crashes before the second update completes. Without a transaction the database would be left in the broken state on the left. With a transaction, the database rolls back and restores the original balances, shown on the right:

The rollback is only possible because the first update was never made permanent — an uncommitted change can always be undone. Nothing counts until the COMMIT. This is the beating heart of Atomicity, the first of the ACID properties.

Without transactions, a crash midway through a multi-step change corrupts your data. Picture the transfer running as two ordinary, independent updates. The first one takes £100 out of your account and is saved. Then the power fails before the second update adds it to your friend. When the machine restarts, your account is £100 lighter and your friend's is unchanged — the money is gone, and the database has no idea anything is wrong.

Atomicity prevents this. Because the two updates are wrapped in a single transaction, the incomplete change was never committed, so on recovery the database automatically rolls it back to the last consistent state. It is genuinely all-or-nothing: the transfer either fully happens or fully un-happens. Never assume a sequence of separate writes is safe just because each one works on its own — it is the gap between them that a transaction closes.

ACID — the four guarantees

A database transaction is only trustworthy if it keeps four promises. Their initials spell ACID:

Atomicity — a transaction is all-or-nothing. Either every operation in it takes effect, or, on any failure, none of them do (the transaction is rolled back).
Consistency — a transaction moves the database from one valid state to another, never leaving it breaking a rule. All constraints (primary keys, data types, business rules like “a balance cannot go below its overdraft limit”) still hold afterwards.
Isolation — concurrent transactions do not interfere with one another. The end result is as if they had run one at a time, even though the database ran them at the same time.
Durability — once a transaction has been committed, its changes are permanent. They survive a crash, a power cut, or the machine being switched off and on again.

A closer look at each letter

Each property is easiest to grasp through the bank transfer, and through the mistake it rules out.

A — Atomicity (all-or-nothing)

The transfer's two updates are indivisible. If the second update fails, the first is rolled back, so you never lose money into thin air. “Atomic” borrows the old idea of the atom as something that cannot be split.

C — Consistency (only valid states)

Before and after the transfer, the total money in the two accounts is unchanged and every rule still holds. If a rule would be broken — say the transfer would push your balance below your overdraft limit — the whole transaction is rejected and rolled back, keeping the database valid.

I — Isolation (as if one at a time)

Suppose two transfers touch your account at the same moment. Isolation guarantees they don't tread on each other: one effectively finishes before the other “sees” the account, so the results are the same as if they had queued up and run in turn. Without it, two transactions reading and writing the same balance could produce a wrong total.

D — Durability (survives a crash)

The instant the app tells you “transfer complete,” the change has been committed and written to permanent storage (typically a disk, plus a write-ahead log). Pull the plug a millisecond later and the money has still moved — a committed transaction is not forgotten.

They are close cousins but not the same. Atomicity is about the steps of one transaction — all of them, or none. Consistency is about the rules of the whole database — no transaction may leave it in a state that breaks a constraint. A transaction could be perfectly atomic (it fully ran) yet still be refused for consistency (it tried to set a negative balance where that is forbidden). Atomicity is how a transaction behaves; consistency is the result it must respect.

Seeing atomicity in code

Our sandbox runs TypeScript, not SQL, but the idea transfers exactly. This models a transfer that checks for a problem partway through; if anything is wrong it rolls the balances back to a saved snapshot instead of leaving them half-changed. Run it, then try changing amount to more than account 1 holds and watch the rollback fire.

type Account = { id: number; balance: number }; let accounts: Account[] = [ { id: 1, balance: 250 }, { id: 2, balance: 80 }, ]; const total = () => accounts.reduce((sum, a) => sum + a.balance, 0); const find = (id: number) => accounts.find((a) => a.id === id)!; function transfer(fromId: number, toId: number, amount: number): void { // Snapshot balances so we can ROLLBACK if the transaction fails. const snapshot = accounts.map((a) => ({ ...a })); console.log("Total before: " + total()); try { find(fromId).balance -= amount; // step 1: take if (find(fromId).balance < 0) // consistency rule: no negative balance throw new Error("insufficient funds"); find(toId).balance += amount; // step 2: give console.log("COMMIT: transfer of " + amount + " succeeded"); } catch (e) { accounts = snapshot; // ROLLBACK: restore the saved state console.log("ROLLBACK: " + (e as Error).message + " — nothing changed"); } console.log("Account 1: " + find(1).balance + ", Account 2: " + find(2).balance); console.log("Total after: " + total()); } transfer(1, 2, 100); // succeeds transfer(1, 2, 999); // fails the rule → rolls back

Notice the total is identical before and after every transfer — whether it commits or rolls back. A real database does this snapshot-and-restore for you, far more efficiently, using its transaction log.