Understanding REPEATABLE READ and SERIALIZABLE

Start a transaction using REPEATABLE READ:

Now check the current isolation level:

You should see:

End the transaction:

Running BEGIN doesn't immediately establish the transaction's data snapshot.

Under REPEATABLE READ, PostgreSQL establishes the snapshot when the transaction executes its first query or data-modification statement.

Later statements continue using that snapshot. They don't see changes committed by other transactions after the snapshot was established.

The transaction can still see changes made by its own earlier statements.

This example requires two independent database sessions, so we'll follow the statements rather than run them in the SQL editor.

Suppose an order currently has:

Under READ COMMITTED, Transaction A can see the order change:

Each SELECT in Transaction A receives a new snapshot.

Under REPEATABLE READ, both SELECT statements use the snapshot established by the first SELECT:

Transaction B successfully changes the order to shipped, but Transaction A continues seeing pending because that was the visible status when its snapshot was established.

The key difference is:

Suppose a backend request generates a report using several queries:

1. count the orders;

2. calculate their total value;

3. group them by status;

4. retrieve additional information about them.

Under READ COMMITTED, another transaction could commit changes between those queries. Each query might then observe a slightly different database state.

Under REPEATABLE READ, all the queries work from the same snapshot. The results therefore represent one consistent view of the database.

REPEATABLE READ gives a transaction a stable view, but it doesn't guarantee that the combined outcome of concurrent transactions could have occurred if they had run one at a time.

Suppose an application has this rule:

A customer can't have more than `$1,000` in pending orders.

The customer currently has $900 in pending orders.

Two transactions start at almost the same time. Each transaction wants to create another pending order worth $100.

Both transactions read the current total:

Both see:

Each transaction decides that adding another $100 is allowed.

They then insert different orders:

Because the transactions insert different rows, they don't directly conflict over the same row.

Under REPEATABLE READ, both transactions may commit.

The customer then has:

The business rule has been violated.

This outcome couldn't have occurred if the transactions had run one at a time.

If Transaction A ran first, Transaction B should have seen a pending total of $1,000 and rejected the second order. The same would be true if Transaction B ran first.

This is called a serialization anomaly.

REPEATABLE READ prevents the data visible to one transaction from changing, but serialization anomalies are still possible.

Start a transaction using SERIALIZABLE:

Check the current isolation level:

You should see:

End the transaction:

In PostgreSQL, SERIALIZABLE provides the same transaction-wide snapshot behavior as REPEATABLE READ.

It also monitors how concurrent SERIALIZABLE transactions read and write data.

If their combined outcome couldn't have occurred with the transactions running one at a time, PostgreSQL rejects one of them.

The transactions can still run concurrently. PostgreSQL doesn't simply execute them one after another.

The guarantee is:

Successfully committed `SERIALIZABLE` transactions produce the same result as some order in which those transactions ran one at a time.

Return to the pending-order example.

Two SERIALIZABLE transactions both read a pending total of $900 and attempt to insert another $100 order:

PostgreSQL detects that allowing both transactions to commit would produce an outcome that isn't possible under any one-at-a-time ordering.

One transaction can commit. PostgreSQL rejects the other with an error similar to:

The rejected transaction receives the SQLSTATE error code:

The application must retry that transaction.

Retries aren't limited to SERIALIZABLE.

Suppose Transaction A uses REPEATABLE READ and sees an order with a status of pending.

Transaction B changes that same order to shipped and commits.

If Transaction A then tries to update the order version from its earlier snapshot, PostgreSQL can't safely apply the change. Transaction A can fail with:

This is also a serialization failure with SQLSTATE 40001.

Applications that use REPEATABLE READ for updates must therefore also be prepared to retry transactions.

When PostgreSQL returns a serialization failure, don't retry only the statement that failed.

The transaction's earlier reads and decisions were based on its previous snapshot. Retrying one statement would continue using results that may no longer be valid.

Retry the entire transaction from the beginning:

A serialization failure can occur while a statement is being executed or when the transaction tries to commit.

In an application, use a limited number of retry attempts rather than retrying forever.

A stronger isolation level isn't automatically better. Choose the level based on the guarantee the operation needs.

For many application transactions, READ COMMITTED is a good starting point.

Use REPEATABLE READ when several statements need to work from one consistent snapshot.

Use SERIALIZABLE when a stable snapshot isn't enough and concurrent transactions mustn't produce an outcome that would be impossible if they ran one at a time.

The important question is:

What guarantee does this operation actually need?

The core ideas are:

1. READ COMMITTED creates a new snapshot for each statement.

2. REPEATABLE READ uses one snapshot established by the transaction's first query or data modification.

3. A stable snapshot doesn't prevent every serialization anomaly.

4. SERIALIZABLE rejects transactions when their combined result couldn't match any one-at-a-time execution.

5. Both REPEATABLE READ and SERIALIZABLE can produce serialization failures.

6. After a serialization failure, the application must retry the entire transaction.

In the next chapter, you'll learn about another concurrency problem transactions can encounter: deadlocks.