Transactions and Concurrency

Using `REPEATABLE READ` and `SERIALIZABLE`

In the previous chapters, you learned that PostgreSQL uses READ COMMITTED by default.

Under READ COMMITTED, statements inside the same transaction can see different committed database states.

Sometimes that's exactly what we want.

But sometimes several queries need to work from a more stable view of the database.

PostgreSQL provides stronger isolation levels for these situations:

REPEATABLE READ
SERIALIZABLE

When `READ COMMITTED` isn't enough

Imagine a request starts a transaction and reads an order:

While the transaction remains open, another transaction changes the order to shipped and commits.

Under READ COMMITTED, a later SELECT can see:

For many operations, that's fine.

But suppose several queries all need to work from the same view of the database.

For that, PostgreSQL provides REPEATABLE READ.

Using `REPEATABLE READ`

Type the following statement into the SQL editor and run it:

Now check the current isolation level:

You should see:

End the transaction:

Under REPEATABLE READ, once the transaction establishes its snapshot, later queries continue working from that snapshot rather than seeing newly committed changes from other transactions.

Seeing the difference

The following examples are conceptual.

With READ COMMITTED:

With REPEATABLE READ:

The key difference is:

whereas:

Why would an application need this?

Imagine a backend request generates a report using several queries.

One query counts orders.

Another calculates their total value.

Another retrieves additional information about those orders.

If other transactions keep changing the database while these queries run, READ COMMITTED can allow each query to observe a somewhat different database state.

If all the calculations need to represent one consistent view of the data, REPEATABLE READ can be useful.

What about `SERIALIZABLE`?

PostgreSQL provides an even stronger isolation level: `SERIALIZABLE`.

Type the following statement into the SQL editor and run it:

Check it:

You should see:

End the transaction:

SERIALIZABLE aims to make the outcome of concurrent transactions equivalent to some order in which those transactions had run one after another.

The transactions can still physically run concurrently.

But PostgreSQL can reject a transaction when allowing all the concurrent transactions to succeed would violate that guarantee.

Serializable transactions can fail

Conceptually:

PostgreSQL may determine that both transactions can't safely succeed.

One can fail with an error similar to:

The source specifically notes that a serializable transaction may succeed when retried.

Your application must be prepared to retry

This matters to backend code.

Using SERIALIZABLE doesn't mean:

Instead:

If PostgreSQL rejects the transaction because of a serialization conflict, the application needs to be prepared to run the transaction again.

Choosing between the isolation levels

For many application transactions, PostgreSQL's default READ COMMITTED is a good starting point.

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

Use SERIALIZABLE when you need the stronger guarantee that concurrent transactions produce an outcome equivalent to some serial execution.

A stronger isolation level isn't automatically better.

The important question is:

What guarantee does this operation actually need?

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

When READ COMMITTED isn't enough

Using REPEATABLE READ