I often forget how different isolation levels affect queries in PostgreSQL, so I wrote a quick overview to remind myself. It won’t include all the details but is hopefully accurate enough in what it does say!
Ideally, transactions behave as if they run sequentially, one after another. They don’t interact with each other while they’re running, and you don’t get different results depending on what other queries are running concurrently. In other words, they are completely isolated.
In reality, when transactions run concurrently, there are several types of interactions (or phenomena) that can happen. We can control which types of interactions are allowed by using “transaction isolation levels”.
PostgreSQL has four (increasing) levels of isolation for transactions:
- Read uncommitted (treated the same as Read committed)
- Read committed
- Repeatable read
- Serializable
The default isolation level for transactions is Read committed. At this level, doing two SELECTs on the same row one after the other inside a transaction can read different values for that row, if another transaction has committed an update in between those two SELECTs. This is known as a nonrepeatable read phenomenon.
Note that Read uncommitted is implemented the same as Read committed in PostgreSQL. In the SQL standard, this allows dirty reads, in which a SELECT can see modifications to a row that have not yet been committed. Dirty reads are not allowed in PostgreSQL.
At Repeatable read level, those two SELECTs will read the same value, even if another transaction has committed an update.
PostgreSQL’s Repeatable read is implemented using Snapshot isolation. At this level, each query in the transaction sees the same snapshot of the database state. In contrast, at Read committed each query sees its own snapshot. In other words, a single SELECT always sees a consistent view; it won’t see updates made since the SELECT started.
At Repeatable read, serialisation anomalies are possible. That is, to quote the documentation:
The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.
The Serializable isolation level prevents this. The documentation contains an example of such an anomaly.
There is more to transaction isolation than this, but this is a good enough overview to remind me what’s going on!