Transaction concepts

Two halves. The theory doesn't change when you switch databases: what a transaction promises, what the isolation levels trade away, and the full vocabulary of things that go wrong. What each engine actually does about that vocabulary changes a great deal, and the comparison below puts PostgreSQL's and MySQL's answers in adjacent cells. Every claim on either side links to a transcript from a real run that proves it.

Theory

• What is a transaction? (ACID) — the unit of work, and what each of the four letters actually promises.
• Isolation levels — the SQL standard's four-level ladder, what each level permits, and where the standard stops telling the whole story.

The anomalies

The anomaly catalog names every isolation anomaly, from the SQL standard's classic three to Adya's full formal list. The five that matter most in practice get their own pages:

• Dirty read — seeing data that was never committed
• Non-repeatable read — the same query, two answers
• Phantom read — new rows appearing between your queries
• Lost update — the silent bug your app most likely has
• Write skew — both transactions commit, the invariant dies

The comparison

• Anomalies by engine — one row per anomaly, one column per engine, each cell naming the weakest isolation level at which that engine prevents it. The lost-update row is two levels apart.

Patterns

• Dual writes & the transactional outbox — why you cannot atomically write to two systems, and the pattern that shrinks the problem.

Then pick an engine

Theory is where the databases agree. The lessons are in where they don't:

• The PostgreSQL track — snapshots everywhere, conflicts surface as retryable errors (40001), dirty reads impossible at every level.
• The MySQL track — InnoDB's locks and current reads, real dirty reads at READ UNCOMMITTED, conflicts surface as deadlocks (1213).