Transactions and ACID

The problem: half-done operations

Imagine a bank transfer: subtract €100 from account A and add it to account B. That's two writes. What happens if the system fails between the two? The money disappears: it was subtracted from A but never reached B.

A transaction solves this: it groups several operations into an indivisible unit that runs all or nothing.

BEGIN, COMMIT, ROLLBACK

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;   -- confirms: both changes are made permanent at once

• BEGIN (or BEGIN TRANSACTION) opens the transaction.
• COMMIT confirms it: all changes are applied at once.
• ROLLBACK undoes it: discards all changes made since BEGIN, as if nothing had happened.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  -- something goes wrong (insufficient balance, validation error...)
ROLLBACK;  -- nothing is saved; A's balance stays intact

ACID: the four guarantees

The transactions of a relational database comply with ACID:

• A — Atomicity. All or nothing. If one operation of the transaction fails, all are undone. It never stays half-done.
• C — Consistency. The transaction takes the database from a valid state to another valid state: the rules are respected (foreign keys, CHECK constraints, UNIQUE...). A data invariant is never violated.
• I — Isolation. Concurrent transactions don't step on each other: each one runs as if it were alone. The intermediate results of one are not visible to the others until COMMIT.
• D — Durability. Once COMMIT is done, the changes persist even if the power goes out right after. They've been written to disk (usually via a write-ahead log).

Isolation levels

Perfect isolation is expensive (it forces everything to serialize). That's why SQL defines levels that balance correctness and performance, from lowest to highest guarantee:

The higher the level, the more correct but less concurrent (more locks).

Race conditions

Without adequate isolation, race conditions appear: two concurrent transactions read the same data, modify it and one overwrites the other (lost update).

Classic example — booking the last ticket to a concert:

1. Transaction T1 reads tickets = 1.
2. Transaction T2 reads tickets = 1 (nothing has been committed yet!).
3. T1 subtracts 1 → writes 0.
4. T2 subtracts 1 → writes 0.

Result: two tickets have been sold but there was only one. Transactions with the right isolation level (or an explicit lock like SELECT ... FOR UPDATE) prevent this error.

Mental rule: a transaction must be correct even if another runs at the same time.

Examples

Transaction committed with COMMIT (transfer)

BEGIN;
  UPDATE orders SET status = 'paid' WHERE id = 2;
  UPDATE orders SET status = 'paid' WHERE id = 6;
COMMIT;

SELECT id, status FROM orders WHERE id IN (2, 6) ORDER BY id;

ROLLBACK undoes the transaction's changes

BEGIN;
  UPDATE orders SET total = 0 WHERE id = 1;
ROLLBACK;

-- The total of id=1 is still the original (120.0)
SELECT id, total FROM orders WHERE id = 1;