Transactions and ACID

A transaction groups several statements into one all-or-nothing unit: BEGIN, then the changes, then COMMIT to keep them or ROLLBACK to undo them. ACID names the four guarantees it gives: Atomic, Consistent, Isolated, Durable.

Why it matters

Moving money means taking £100 from one account and adding it to another. If the system crashes between the two, £100 vanishes. Transactions make sure either both changes happen or neither does — and that two people working at once don't corrupt each other's changes.

The idea

BEGIN starts a transaction. Changes made inside are visible to you but not yet permanent. COMMIT makes them all permanent at once; ROLLBACK throws them all away.

ACID: Atomic — all or nothing.
Consistent — the data moves from one valid state to another, never breaking a constraint.
Isolated — transactions running at the same time don't see each other's half-finished work (how strictly depends on the isolation level).
Durable — once committed, it survives a crash.

A bank transfer slip that only counts when it is stamped. You can fill in both lines — take from here, add to there — but nothing happens until the stamp (COMMIT). Tear up the slip (ROLLBACK) and it is as if you never started.

A torn-up slip leaves no trace. Inside a database, a long open transaction holds locks that can make other people wait. Keep transactions short: never wait for user input inside one.

Syntax

BEGIN;
UPDATE ... ;
UPDATE ... ;
COMMIT;      -- or ROLLBACK;

How it works

  1. BEGIN opens the transaction.
  2. Two UPDATEs move Gus into HR and give him a raise. Only this transaction can see the changes so far.
  3. COMMIT makes both changes permanent together. A crash before COMMIT would undo both.
  4. In the second example, DELETE removes every item — then ROLLBACK undoes it, and all 14 rows are back.
  5. If any statement fails, you roll back, and the data is exactly as it was before BEGIN.

Key points

Common mistakes

Not true: Default isolation level: it stops two transactions from overwriting each other's work.
In fact: Under the usual default, two transactions can both read the same balance and both write it back, so one change is lost. Lock the row first, or use a stricter level.

In the interview

A transaction groups statements into an atomic unit — BEGIN, changes, then COMMIT or ROLLBACK. ACID means atomicity, all or nothing; consistency, constraints hold before and after; isolation, concurrent transactions don't see each other's uncommitted work; and durability, committed changes survive crashes. Isolation is tunable: read committed, repeatable read, serializable. Each level trades anomalies, like non-repeatable or phantom reads, against concurrency. For something like a balance update, I lock the row with SELECT FOR UPDATE or use serializable.

Part of Unit 6: Building tables and changing data in the free SQL course.