PostgreSQL/Visibility

Some exemplary Problems
It's obvious that every transaction 'sees' all data changes, it has been carried out during its lifetime, without problems. But there are situations where more than one process wants to read or write the same data during an overlapping time interval of their transactions or even at the same point in time, which is possible on servers with multiple CPUs or a disk array. In such cases, different types of conflicts and suspicious effects may occur.

Applications may or may not accept the effects resulting from such competing situations. They can choose different levels of isolation against the activities of other transactions depending on their needs. The level defines which effects they are willing to accept and which not. Higher levels mean that fewer effects can occur but the database system must work harder and that the overall throughput decreases.

Here are some examples with two transactions TA and TB. Both don't perform a  if not explicitly noted.


 * TA reads the row with . TB reads the same row. TA increases column X by 1. TB increases the same column by 1. What will be the result? There is the danger of a 'Lost update'.


 * TA changes a value of the row with . What shall TB see if it reads the same row? TA may perform a  . (Uncommitted read)


 * TA reads the row with . TB reads the same row, changes a value and performs a  . TA reads the row again. In comparison to its first read, it will see a different value. (Non-repeatable read)


 * TA reads all rows with . TB inserts an additional row with   and performs a  . TA reads all rows with   again and receives a different number of rows. (Phantom read)


 * TA reads and changes the row with . TB reads and changes the row with  . TB wants to read and change the row with  . Because TA has not yet committed its changes, TB must wait for TA. TA wants to read and change the row with  . Because TB has not yet committed its changes, TA must wait for TB. (Deadlock)

PostgreSQL's Solutions
The SQL standard describes the 3 effects (or problematic situations) 'Uncommitted read', 'Non-repeatable read', and 'Phantom read' and defines 4 levels of isolation between transactions: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Every level is stricter than its predecessor and prevents more effects, which means e.g. that a 'Non-repeatable read' is possible in level READ COMMITTED but not in REPEATABLE READ or SERIALIZABLE.

PostgreSQL implements those levels. But, as a consequence of its MVCC model, it implements some aspects a little stricter than they are demanded by the standard. If a transaction requests the level READ UNCOMMITTED, PostgreSQL handles it always as a READ COMMITTED, which leads to the overall behavior that all uncommitted changes are invisible to all other transactions at any level - only committed changes can be seen by other transactions.

Examples
The following examples act on a table t1 with the two columns id and col and a single row.

Uncommitted read
The example shows that PostgreSQL solely shows committed rows to other transactions.

Lost update
The example shows that PostgreSQL prevents 'lost update' in the lowest level of isolation - as well as in all other levels. (The table t1 contains its original values.)

Both  statements are executed, nothing gets lost.

Please note that transaction B is an example for a 'non-repeatable read' (see below) because the isolation level is '(UN)COMMITTED READ'. First, it reads the value '100' with its  command. Next, it reads '101' with its  command - after COMMIT of transaction A - and increases it to '102'. If the isolation level would be 'REPEATABLE READ', transaction B would receive the error message 'could not serialize access due to concurrent update' as PostgreSQL's reaction to the  request.

Non-repeatable read
The example shows a non-repeatable read. (The table t1 contains its original values.)

Phantom read
The example shows a phantom read. (The table t1 contains its original values.)

Dead lock
The example shows a dead lock. (The table t1 contains two rows.)