Transactions and ACID

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  B["BEGIN"] --> S1["change 1"] --> S2["change 2"]
  S2 --> C["COMMIT<br/>both kept, permanently"]
  S2 --> R["ROLLBACK or a crash<br/>both undone"]

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.

The shape of it

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

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

BEGIN;
UPDATE employees SET dept_id = 4 WHERE name = 'Gus';
UPDATE employees SET salary = salary + 2000 WHERE name = 'Gus';
COMMIT;
SELECT name, dept_id, salary FROM employees WHERE name = 'Gus';

BEGIN;
DELETE FROM order_items;
ROLLBACK;
SELECT COUNT(*) AS items_after_rollback FROM order_items;

Must-know points

• BEGIN … COMMIT makes several statements one all-or-nothing unit; ROLLBACK cancels it.
• Atomic: all or nothing. Consistent: constraints always hold. Isolated: concurrent transactions don't interfere. Durable: committed means saved.
• Isolation levels trade safety for speed: READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
• Anomalies the levels prevent: dirty reads, non-repeatable reads, phantom reads.
• Keep transactions short; long ones hold locks and block others.
• Locks, write skew and transactions that span several services (sagas) are in the System Design tab, Phases 3 and 4.

Common mistakes

• Running related changes without a transaction, so a failure halfway leaves the data inconsistent.
• Leaving a transaction open (forgetting COMMIT), which holds locks.
• Assuming the default isolation level prevents every race. Two transactions can still both read a balance and both write it.

In other databases

• Default isolation: PostgreSQL, SQL Server and Oracle use READ COMMITTED; MySQL InnoDB uses REPEATABLE READ; SQLite is effectively SERIALIZABLE.
• Most databases run each statement in its own transaction automatically (autocommit) unless you BEGIN one.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  ACID transactions explained (video)

  YouTube search, free

• Read

  PostgreSQL docs — Transactions (tutorial)

  official documentation, free

• Read

  PostgreSQL docs — Transaction Isolation

  official documentation, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain each letter of ACID with the bank-transfer example.

   Show answer

   Moving £100 from account A to account B is two UPDATEs: subtract from A, add to B.

   • Atomic: they happen together or not at all. If the server crashes after the first UPDATE, the transaction is rolled back, so £100 can't disappear.
   • Consistent: every rule still holds afterwards. If a CHECK says balances can't go below zero, a transfer that would overdraw A fails, and nothing changes.
   • Isolated: someone reading the balances while the transfer runs never sees the halfway point (A already £100 down, B not yet £100 up).
   • Durable: once COMMIT returns, the transfer is saved on disk and survives a crash or power cut a second later.

2. Writefrom a blank page, then run it

   In one transaction, move Eli to Marketing and give him a £3000 raise, then show his row.

   Show answer

   Both changes sit between BEGIN and COMMIT, so they are saved together. Marketing is department 3. Eli's salary goes from 45,000 to 48,000.

   Notice his manager_id is still 4 (Dara, in Sales). A real move would probably change that too, in the same transaction.

   sqlCopy

   BEGIN;
   UPDATE employees SET dept_id = 3 WHERE name = 'Eli';
   UPDATE employees SET salary = salary + 3000 WHERE name = 'Eli';
   COMMIT;
   SELECT * FROM employees WHERE name = 'Eli';

   Try it

   1 row

   emp_id	name	dept_id	manager_id	salary	hire_date
   5	Eli	3	4	48000	2022-07-01

3. Predictsay it before you run it

   What does the second example return if you remove the ROLLBACK line?

   Show answer

   0. Inside the open transaction, the DELETE has already removed all 14 item lines, and the transaction sees its own changes.

   The bigger problem is that the transaction is never finished. Nothing is permanent yet, and it holds locks that block other writers until someone commits, rolls back, or disconnects (which rolls it back). Forgetting to end a transaction is a classic way to freeze an application.

   sqlCopy

   BEGIN;
   DELETE FROM order_items;
   SELECT COUNT(*) AS items_after_rollback FROM order_items;

   Try it

   1 row

   items_after_rollback
   0

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Two people run "read the stock, subtract 1, write it back" at the same moment on a product with 1 left. What can go wrong, and how do you prevent it?

   Show answer

   Both read stock = 1, both compute 1 − 1 = 0, and both write 0. Both sales go through, so one item has been sold twice: a lost update. The second writer overwrote the first one's change without knowing it existed.

   Prevent it by making "check and subtract" one atomic step:
   • A conditional update: UPDATE products SET stock = stock - 1 WHERE product_id = 7 AND stock >= 1, then check how many rows changed. 0 means sold out.
   • Or lock the row while you read it: SELECT stock … FOR UPDATE, then update. The second buyer waits, then reads 0.
   • Plus a CHECK (stock >= 0) as a final safety net.

   Below, a stock column is added with 1 left, and two buyers run the conditional update. The second one changes 0 rows, so it knows it must refuse the sale.

   sqlCopy

   ALTER TABLE products ADD COLUMN stock INTEGER NOT NULL DEFAULT 1 CHECK (stock >= 0);
   UPDATE products SET stock = stock - 1 WHERE product_id = 7 AND stock >= 1;
   UPDATE products SET stock = stock - 1 WHERE product_id = 7 AND stock >= 1;
   SELECT product_name, stock, changes() AS second_buyer_got
   FROM products
   WHERE product_id = 7;

   Try it

   1 row

   product_name	stock	second_buyer_got
   Phone case	0	0

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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 — trading anomalies like non-repeatable or phantom reads against concurrency, so for something like a balance update I lock the row with SELECT FOR UPDATE or use serializable.

Follow-up questions

Tick each check when you can do it without notes