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 |