Postgres: Read

Understanding Transaction Isolation Levels. Postgres‘s transaction default isolation level is READ COMMITTED, means Transaction can only read changes being committed.

SELECT name, setting
FROM pg_settings
WHERE name = 'default_transaction_isolation';

             name              |    setting
-------------------------------+----------------
 default_transaction_isolation | read committed

Dirty read

A transaction A is modifying a data. The changes not commit to database yet, but another transaction B is using the data. Then XID_B reads the "dirty data". dirty means data have changes not commited.

Non-repeatable read

You cannot read data multiple times in a single transaction. Because other transaction may change the data.

Phantom Read

Transaction A changing data involves all rows while Transaction B also inserted a row. Then A will see this new row like a Phantom!

Serialization Anomaly

4 transactions executed

The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.

Level Dirty Read Non-repeatable Read Phantom Read
Read Uncommitted ✅ ✅ ✅
Read Committed ❌ ✅ ✅
Repeatable Read ❌ ❌ ✅
Serializable ❌ ❌ ❌