Configuring Transaction Isolation Levels in PostgreSQL

In PostgreSQL, transaction isolation determines which committed changes a transaction can see while other transactions run. A transaction groups database statements so PostgreSQL commits them all or rolls them all back. The isolation level controls whether each statement sees a fresh view of committed data or the transaction keeps a stable view, and whether PostgreSQL rejects concurrent work that could produce an inconsistent result.

PostgreSQL uses READ COMMITTED by default. This level works well for many transactions. Other levels suit work that needs a stable snapshot, a fixed view of committed data, or stronger protection against concurrency anomalies. You can set a level for one transaction or change the default for a connection. Choose the level based on the data rule you need to protect, and handle serialization failures when they occur.

How PostgreSQL isolation levels differ

At READ COMMITTED, each statement sees rows that were committed before it began. If another transaction commits between two queries in the same transaction, the second query can see those changes. At REPEATABLE READ, PostgreSQL gives the transaction a stable snapshot, a fixed view of committed data. Later statements keep seeing the data in that snapshot.

A few terms help describe the difference:

• A dirty read occurs when a transaction sees another transaction’s uncommitted change.
• A nonrepeatable read happens when a transaction reads a row twice and sees a committed change between the reads.
• A phantom read happens when repeating a search returns a different set of matching rows because another transaction changed the data.
• A serialization anomaly occurs when transactions commit results that could not have happened if PostgreSQL had run them one at a time in some order.

PostgreSQL accepts four standard isolation-level names, but READ UNCOMMITTED behaves the same as READ COMMITTED. PostgreSQL also prevents phantom reads at REPEATABLE READ, although the SQL standard allows them at that level.

These behaviors are described in PostgreSQL’s transaction isolation documentation. A stable snapshot at REPEATABLE READ does not guarantee that concurrent transactions have the same combined effect as running them one at a time.

Set the level when the transaction starts

PostgreSQL normally uses autocommit, which runs each standalone statement in its own transaction. Use an explicit transaction block when several statements need the same isolation level.

Set the level in BEGIN:

BEGIN ISOLATION LEVEL REPEATABLE READ;

-- Run queries and changes here

COMMIT;

You can also set it immediately after beginning the transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Run queries and changes here

COMMIT;

Run SET TRANSACTION before the transaction’s first query or data change. After the first query or data change, PostgreSQL will not let you change that transaction’s isolation level. If you issue SET TRANSACTION before starting a transaction, PostgreSQL warns you and does nothing. See PostgreSQL’s SET TRANSACTION rules and BEGIN behavior.

Setting the level when the transaction starts avoids a common mistake: a helper, ORM, or database hook runs a query before your code sets the level. Check where the transaction begins and make sure no query runs before the level is set.

Set and check defaults

To change the default for new transactions on the current connection, set a session characteristic or change default_transaction_isolation:

SET SESSION CHARACTERISTICS AS TRANSACTION
    ISOLATION LEVEL REPEATABLE READ;

SET default_transaction_isolation = 'repeatable read';

The PostgreSQL default is READ COMMITTED. The setting affects new transactions only; transactions already in progress keep their current isolation level. You can check the current transaction’s level with:

SHOW transaction_isolation;

You can also set defaults in the server configuration or with ALTER DATABASE. PostgreSQL’s client connection defaults documentation explains where each setting applies. If a connection pool reuses database connections, check how it sets and resets connection-level settings. The pool’s configuration determines its behavior.

Choose the level based on the data rule

Use READ COMMITTED when each statement can make its decision from the data visible when that statement starts. PostgreSQL describes this level as adequate for many applications. It suits straightforward changes to predetermined rows when multiple queries in the transaction do not need to share the same view of the data.

Use REPEATABLE READ when later statements must see the same snapshot, or fixed view of committed data, as earlier statements. This helps when a transaction reads several values and needs them to stay stable throughout its work. This level does not protect every rule that depends on multiple rows. Two transactions can read some of the same data, make decisions from their snapshots, and update different rows in ways that break a shared rule.

Use SERIALIZABLE when a business rule requires concurrent reads and writes to have the same result as running the transactions one at a time. For example, PostgreSQL documents a case where two transactions each read a sum from one group and insert that sum into another. Under REPEATABLE READ, both transactions can commit even though their combined results match neither order of running them one at a time. Under SERIALIZABLE, PostgreSQL aborts one transaction when needed to prevent that result.

PostgreSQL monitors read/write conflicts to find these unsafe patterns. Serializable isolation adds monitoring work and can produce serialization failures. According to PostgreSQL’s documentation, it does not add blocking beyond REPEATABLE READ, but your code must handle transaction retries. Use the intended isolation level for all reads and writes that protect the same data rule. Weaker transactions in that work can undermine the protection. PostgreSQL explains these checks in its guidance on application-level data consistency.

Retry serialization failures as whole transactions

PostgreSQL reports a serialization failure with SQLSTATE 40001. When this happens, abort and retry the entire transaction, including the reads and decisions that determined what to write. If you retry only the failed statement, your code can reuse a decision based on an outdated view of the data. PostgreSQL does not replay the transaction automatically because it cannot know how to repeat your code’s decisions correctly.

Set a retry limit in your code. When the transaction reaches that limit, stop retrying. Return other errors through the normal error path:

repeat up to the application's retry limit:
    begin a transaction at the chosen isolation level
    run all reads, decisions, and writes
    if commit succeeds:
        return success
    if SQLSTATE is 40001:
        roll back and retry the whole transaction
    otherwise:
        return or raise the error

A deadlock has SQLSTATE 40P01; retrying that transaction may be appropriate. Unique-constraint errors (23505) and exclusion-constraint errors (23P01) sometimes result from concurrency patterns. They can also point to persistent data or logic problems. Check the cause before automatically retrying either error. PostgreSQL’s serialization failure guidance explains these cases.

When a retry runs your code again, keep external side effects, such as sending a message or charging a payment method, from happening twice. Use an idempotent operation, which has the same effect when repeated, or wait until the database transaction succeeds before performing the side effect.

Use deferrable serializable transactions for long reads

For a long-running, read-only transaction, use SERIALIZABLE READ ONLY DEFERRABLE:

BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

-- Run reporting or backup queries

COMMIT;

PostgreSQL may wait while it gets the initial snapshot. After that, the transaction runs without the usual serializability overhead and cannot be canceled because of a serialization failure. DEFERRABLE has no effect unless the transaction is both serializable and read-only. Use it when the initial wait is acceptable.

A transaction can use an explicit lock, such as SELECT ... FOR UPDATE, to protect particular rows while it coordinates changes. Other transactions that need those locks must wait. A lock does not replace choosing the right isolation level. Under REPEATABLE READ, acquiring a lock does not refresh a snapshot the transaction already established. Lock the specific rows or tables involved in the rule, and make sure every relevant transaction uses the same protection strategy.

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