Learn

A transaction is a unit of work that the database treats atomically — either all succeed, or all fail. The acronym is ACID:

• A — Atomicity: all-or-nothing
• C — Consistency: invariants preserved (constraints, FK)
• I — Isolation: concurrent transactions don't interfere
• D — Durability: committed changes survive crashes

The classic example

Money transfer between two accounts. Without a transaction:

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 💥 server crashes here
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Money evaporates. With a transaction:

BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If anything fails between BEGIN and COMMIT, the DB rolls back — neither UPDATE is applied. Atomicity guaranteed.

Syntax

Most engines:

BEGIN;
  ... statements ...
COMMIT;

-- Or to abort:
BEGIN;
  ... statements ...
ROLLBACK;

SQLite uses BEGIN, Postgres also accepts START TRANSACTION. Some prefer BEGIN TRANSACTION for clarity.

Implicit transactions: every single statement (INSERT/UPDATE/DELETE) runs inside its own transaction by default. If it fails, only that statement rolls back.

Savepoints — partial rollback

BEGIN;
  INSERT INTO logs VALUES (1, 'started');
  SAVEPOINT sp1;
  INSERT INTO orders VALUES (...);
  -- something went wrong with the order
  ROLLBACK TO sp1;
  -- The 'started' log still exists; the order is gone.
  INSERT INTO logs VALUES (2, 'aborted order');
COMMIT;

Savepoints are nested transactions. Useful for retrying part of a workflow.

Isolation levels

This is where it gets messy. Concurrent transactions can see each other's in-progress work. Different DBs have different defaults.

The four standard levels (from least to most isolated):

• READ UNCOMMITTED — see uncommitted writes ("dirty reads"). Almost never used.
• READ COMMITTED — only see committed data. Postgres default. SQL Server default.
• REPEATABLE READ — within a transaction, the same query always returns the same data. MySQL default.
• SERIALIZABLE — transactions appear to execute one at a time. Slowest but safest.

Set the level:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- statements
COMMIT;

Common anomalies

• Dirty read — see uncommitted data
• Non-repeatable read — same row reads differently in same transaction
• Phantom read — a query's row count changes mid-transaction
• Lost update — two transactions overwrite each other

Higher isolation levels prevent more of these but cost performance — which is why databases let you pick a level per transaction rather than forcing one on everyone.

In SQLite

SQLite is single-writer — only one writer at a time. Multiple readers can coexist with a writer (in WAL mode). The isolation is effectively SERIALIZABLE for write transactions because they're serialized.

BEGIN IMMEDIATE;       -- acquire write lock immediately
  ... writes ...
COMMIT;

Best practices

• Keep transactions SHORT. Long transactions hold locks, block others, and bloat the WAL.
• Don't do I/O (network calls, file reads) inside a transaction.
• Test rollback paths — make sure the app handles ROLLBACK gracefully.
• Watch out for LOCK TABLES (MySQL) or SELECT ... FOR UPDATE — explicit locks need explicit unlocks.

Production: serializability vs throughput

Most apps use the default isolation level (READ COMMITTED in Postgres). Banks and payment systems use higher (SERIALIZABLE) — at the cost of throughput. The trade-off is a real engineering decision.

Serializable Snapshot Isolation (Postgres SSI) is one of the great inventions of the 2010s — it gives you serializability without the lock overhead. Look it up if you go deep on Postgres.