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. Demonstrated schedules, documented contracts, and marked derivations have distinct evidence limits. Not every reference cell has a direct execution.
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: an overwritten stale read-modify-write
- Write skew: both transactions commit, the invariant dies
The comparison
- Anomalies by engine: one row per anomaly, one column per engine, with operation-specific exclusions and their support. The in-transaction lost-update rows differ between the engines.
Patterns
Choose a protection: start from the business rule, with worked stock, stale-edit, and on-call staffing decisions and paths to request identity and external effects.
Dual writes & the transactional outbox: why you separately committed effects are outside local rollback, and what the database-local pattern does establish.
Then pick an engine
Theory is where the databases agree. The lessons are in where they don't:
- The PostgreSQL track: snapshots and operation-scoped visibility, including
40001target/dependency failures. Plain reads exclude concurrent uncommitted table changes at every level. - The MySQL track: InnoDB's locks and current reads, dirty-read demonstrations at READ UNCOMMITTED, and configured waits/deadlock/timeout scope. Conflicts can also wait and succeed.