How to Set PostgreSQL Transaction Isolation Levels

In PostgreSQL, a transaction groups database statements that commit or roll back together. Its isolation level controls which changes from other transactions running at the same time those statements can see. PostgreSQL 10 uses READ COMMITTED by default. You can set a different level for one transaction; session settings apply to later transactions in that session, and defaults apply to new sessions.

Use REPEATABLE READ when a transaction needs to see the same data across its statements; use SERIALIZABLE when concurrent transactions must have the same effect as running one at a time, and make the code ready to retry transactions PostgreSQL aborts. The examples below follow the PostgreSQL 10.23 documentation. PostgreSQL 10 is unsupported, so check the documentation for the version you run before applying version-specific behavior.

Choose an isolation level

The four standard isolation-level names map to three behaviors in PostgreSQL. READ UNCOMMITTED behaves like READ COMMITTED. It does not permit dirty reads, which means a transaction cannot read changes another transaction has not committed.

A snapshot is the set of data a transaction sees when it reads the database. REPEATABLE READ keeps that view steady, but some patterns of reading and writing in concurrent transactions can still produce a result that no one-at-a-time ordering would produce. Choose SERIALIZABLE when a business rule depends on preventing that result, and be ready to retry transactions PostgreSQL aborts. The PostgreSQL isolation documentation describes these guarantees and their limits.

Set the level for one transaction

Set the isolation level in BEGIN when you want the choice to be visible at the start of the work:

BEGIN ISOLATION LEVEL REPEATABLE READ;

-- Run the transaction's queries and updates.

COMMIT;

Choose SERIALIZABLE instead when the transaction must satisfy serializable behavior:

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Run the reads and writes that make up the transaction.

COMMIT;

A transaction stays open until you run COMMIT or ROLLBACK; in autocommit mode, PostgreSQL runs each statement as its own transaction, so separate statements do not share a snapshot unless you open a transaction block. PostgreSQL describes this behavior in its BEGIN reference.

You can also set the level after BEGIN:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Run the transaction's queries and updates.

COMMIT;

Run SET TRANSACTION before the transaction’s first query or data-modification statement. PostgreSQL 10 does not allow you to change the isolation level after a statement such as SELECT, INSERT, UPDATE, DELETE, FETCH, or COPY has run. If you issue SET TRANSACTION without first starting a transaction, PostgreSQL warns that the command has no effect. See the SET TRANSACTION reference.

Set a default for later transactions

To set a default for subsequent transactions in the current session, run:

SET SESSION CHARACTERISTICS AS TRANSACTION
  ISOLATION LEVEL REPEATABLE READ;

This setting does not affect a transaction already in progress. A later transaction can override it in the BEGIN command or with SET TRANSACTION. The setting stays with the session, so a connection reused from a pool can carry it into later transactions on that connection.

You can set the current session’s default with the default_transaction_isolation parameter as well:

SET default_transaction_isolation TO 'repeatable read';

Set a default for new sessions opened by a particular role with:

ALTER ROLE app_user
  SET default_transaction_isolation = 'repeatable read';

Role settings apply to new sessions; transactions already in progress keep their current settings. Set a role default only if it suits every session opened by that role. For one transaction, set the level in BEGIN to make the choice clear. PostgreSQL 10 lists read committed as the default value of default_transaction_isolation in its client connection settings, and explains role defaults in ALTER ROLE.

Retry transactions PostgreSQL aborts

At SERIALIZABLE, PostgreSQL checks for conflicts between concurrent transactions and aborts a transaction when needed to prevent a result that could not occur if transactions ran one at a time. The error uses SQLSTATE 40001. When this happens, roll back and retry the whole transaction from the beginning. Retrying only the failed statement is not enough because the transaction made earlier reads and decisions using its original view of the data.

At REPEATABLE READ, you also need to retry in some cases. For example, PostgreSQL aborts a transaction that tries to update a row another transaction changed after the repeatable-read transaction began. Restart the transaction so it can read the updated data and make its decision again.

A constraint error does not always mean a serialization failure. A unique-constraint violation can occur during overlapping transactions, even when a transaction checked that a key was absent before inserting it. Handle the error according to your code’s rules rather than automatically retrying it as SQLSTATE 40001.

Check the setting and handle special cases

To see the current transaction’s isolation level, run:

SHOW transaction_isolation;

For a long-running read-only report, PostgreSQL 10 supports a deferrable serializable transaction:

BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

-- Run reporting queries.

COMMIT;

PostgreSQL can wait to get the transaction’s first snapshot, the set of data it will read. After that, the transaction runs without the usual serializable overhead and cannot be canceled because of a serialization failure. This option applies only to transactions that are both SERIALIZABLE and READ ONLY.

Isolation levels do not make sequence changes behave like ordinary table updates. PostgreSQL makes sequence changes immediately visible to other transactions and keeps those changes even if the transaction aborts. A gap in sequence-generated values, such as values for a serial column, does not show whether a transaction committed.

PostgreSQL’s 10.23 release notes said that release was expected to be the last in the 10.x series. Since version 10 is unsupported, use the documentation for your installed PostgreSQL version when setting or relying on isolation behavior.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com