[Postgres] Isolation levels

In PostgreSQL, isolation levels determine how transaction integrity is maintained when multiple transactions occur concurrently.

These levels control the visibility of changes made by other transactions and help prevent phenomena like dirty reads, non-repeatable reads, and phantom reads.

Why Isolation Levels Matter

• Data Consistency: Incorrect isolation levels can lead to inconsistent data states, such as dirty reads, lost updates, and phantom reads.
• Performance: Higher isolation levels often require more locking, which can impact performance.
• Concurrency: Lower isolation levels allow for greater concurrency but may introduce risks.

Read Phenomena

Some isolation levels may encounter the following read phenomena.
Understanding each of them and how to prevent them by using the appropriate isolation level is the purpose of this article.

• Dirty Read: Reading uncommitted changes from another transaction. (It's prevented in Postgres due to MVCC model)
• Non-repeatable Read: The result of the same query within a transaction may vary due to changes made by other transactions that have been committed.
• Phantom Read: New rows are apeared or disapeard from a query result between 2 executions due to changes made by another transaction that is committed in between.
• Serialization anomaly: The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.

The following isolation level is sorted from lowest to higest:

1. Read Uncommitted

• In this level, a transaction can read uncommitted changes made by other transactions. However, PostgreSQL internally treats this level as Read Committed, meaning it does not allow dirty reads.
• Phenomena: Dirty reads are prevented in PostgreSQL, but non-repeatable reads and phantom reads may still occur.

2. Read Committed

• This is the default isolation level in PostgreSQL.
• A transaction sees only the committed data at the start of each query within the transaction. It can’t see uncommitted changes from other transactions. However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed
• Phenomena: Non-repeatable reads and phantom reads are possible, but dirty reads are prevented.

Worth noting that we're mentioning the "at the start of each query winthin transaction", not the "at the start of transaction"*.

3. Repeatable Read

• A transaction in this isolation level sees a consistent snapshot of the database taken when the transaction starts. No other transaction's changes are visible during the transaction, even if they commit.
• However, each query does see the effects of previous updates executed within its own transaction, even though they are not yet committed.
• UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows.
  • If such rows are updated by another concurrent transaciton, this transaction will wait for that transaction commit or rollback.
    • If these transaction rolls back, the repeatable transaction can proceeed to update these rows
    • Else, the repeatable transaction will be rolled back with the message
• Phenomena: Prevents dirty, non-repeatable reads, and phantom reads.

4. Serializable

• This is the strictest isolation level. It ensures that transactions execute in such a way that the outcome is as if they were executed one after the other, rather than concurrently. It effectively prevents all three phenomena (dirty reads, non-repeatable reads, and phantom reads).
• Phenomena: Prevents dirty reads, non-repeatable reads, and phantom reads, ensuring full transaction isolation.

Cheat sheet

(Allowed, but not in PG means it's possible in non-MVCC but not possible in Postgres)

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read uncommitted	Allowed, but not in PG	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible	Possible
Repeatable read	Not possible	Not possible	Allowed, but not in PG	Possible
Serializable	Not possible	Not possible	Not possible	Not possible

Worth noting points

1/ Phantom read in Repeatable read

• In non-MVCC model, the phantom read phenomena can happend in Reapeatable read Because:
  • The rows that are read are locked until the end of the transaction to prevent any changes.
  • If other transactions insert more rows that are matched in the read query above, it will lead to phantom read.
• But in MVCC, it is not possible due to the version mechanism.

References

• https://www.postgresql.org/docs/current/transaction-iso.html