The SQL standard defines four isolation levels. Each one says which problems from the last lesson a transaction can meet:

SERIALIZABLE gives the same result as if the transactions ran one after another.

PostgreSQL gives more than the standard asks for:

• It never allows a dirty read. READ UNCOMMITTED works as READ COMMITTED.
• Its REPEATABLE READ also prevents phantom reads. The transaction sees the data as it was at its first query.

Choose the level

Set the level as the first statement of the transaction, before any query:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;   -- repeatable read

• SET TRANSACTION ISOLATION LEVEL … sets the level of the current transaction only. After it ends, the next transaction uses the default again.
• After a query, it is too late: SET TRANSACTION ISOLATION LEVEL must be called before any query.
• SHOW transaction_isolation shows the level of the current transaction. Outside a transaction, it shows the default.

Defaults differ

The price of more isolation

At REPEATABLE READ and SERIALIZABLE, PostgreSQL does not make transactions wait for each other to stay correct. When two transactions would break the rules, it stops one of them:

ERROR:  could not serialize access due to concurrent update

The application must catch this error and run the whole transaction again. Higher levels prevent more problems, but more transactions fail and must be retried.