Transaction isolation

Transaction isolation controls what one transaction can observe while other transactions are running. SQL names four levels: read uncommitted, read committed, repeatable read, and serializable. Moving up generally hides more concurrency anomalies, from dirty reads through nonrepeatable reads and phantoms, but exact behavior varies by database. PostgreSQL, for example, treats read uncommitted as read committed.

The defaults differ too, which catches people porting code between engines: PostgreSQL defaults to read committed while MySQL's InnoDB defaults to repeatable read, so the same application can behave differently on each without a line changing. Kyle Kingsbury's Jepsen reports have spent a decade showing that the label on the level and the behavior under load and network partition are separate questions.

Reach for a stronger level when correctness depends on several reads describing one stable world: reserving the last seat, enforcing a total across rows, or making a decision that another transaction could invalidate. Serializable aims to make committed results equivalent to some one-at-a-time order, while read committed is often enough for independent statements backed by atomic updates and constraints.

Gotcha: stronger isolation does not mean transactions politely wait until they all succeed. PostgreSQL can abort a serializable transaction with a serialization failure, and the application must retry the whole transaction. Keep it short, make retries safe, and test the actual database engine rather than relying only on the SQL level's name.