Managing PostgreSQL Transaction Isolation in Production

PostgreSQL transaction isolation determines what data a transaction sees while other transactions change the database. This matters when a service reads data, makes a decision, and writes a result. If another transaction changes the data in the meantime, the service’s decision can be wrong unless the database or service protects the business rule.

PostgreSQL’s default, Read Committed, gives each statement a new view of data committed before that statement began. Repeatable Read keeps the same view throughout a transaction. Serializable makes successfully committed transactions behave as if they ran one at a time. If concurrent work threatens that guarantee, PostgreSQL aborts a Serializable transaction, and the application must retry the entire transaction.

In production, use the least restrictive mechanism that protects the business rule. Keep transactions short, and track retries and set a limit on them.

What each isolation level guarantees

PostgreSQL uses multiversion concurrency control, or MVCC. When a transaction changes a row, PostgreSQL creates a new version of that row. Readers can often continue reading without waiting for that transaction to finish. A snapshot is the set of committed row versions a transaction can see. The PostgreSQL documentation on isolation explains how each level uses snapshots.

A stable snapshot does not ensure that concurrent transactions preserve every business rule. Suppose two clinicians are on call, and the rule requires at least one to stay on call. Two transactions each see that both clinicians are on call. One transaction turns off the first clinician, and the other turns off the second. Because they update different rows, both can commit under Repeatable Read, leaving no one on call. This problem is called write skew.

Use Serializable when a rule depends on how multiple rows relate and neither a constraint nor an explicit lock can protect it reliably. Use Repeatable Read when the work needs stable reads and you have verified separately that its writes cannot cause this kind of problem.

Match the protection to the rule

Start with the invariant, the condition that must remain true after every transaction commits. Then choose the database feature that enforces that condition directly.

Use a unique constraint to enforce a uniqueness rule. PostgreSQL then enforces the rule even if multiple service instances try to create the same value at once. Under Read Committed, a check-then-insert sequence does not provide the same protection: both transactions can check before either one inserts.

Use row locks when the transaction knows which rows it must protect. For example, SELECT ... FOR UPDATE locks selected rows so other transactions cannot update them at the same time. Have every relevant code path lock the same rows in the same order to reduce deadlocks. PostgreSQL also supports NOWAIT, which makes a transaction fail immediately instead of waiting, and SKIP LOCKED, which lets it skip rows another transaction has locked. See the documentation for SELECT locking clauses.

Choose Serializable when a rule depends on whether any row matches a condition, and locking a known set of rows would be fragile. PostgreSQL tracks reads and writes that could conflict, then aborts a transaction when needed to prevent an unsafe result from committing. This reduces the need to identify and lock every relevant row, but the application must handle serialization failures.

Set the isolation level before the transaction runs its first query or data-modification statement. For a transaction that needs Serializable guarantees:

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Read current state and make the related changes.

COMMIT;

SET TRANSACTION sets characteristics for the current transaction only; it does not affect later transactions. PostgreSQL does not let a transaction change its isolation level after it has started reading or changing data. See SET TRANSACTION.

Retry the whole transaction

PostgreSQL reports serialization failures with the SQLSTATE error code 40001. The application must roll back and rerun the whole transaction, including its reads and the logic that decides what to write. If it retries only the failed statement, it reuses decisions based on an earlier view of the data. PostgreSQL’s serialization failure guidance explains why the server cannot rerun the application’s full decision process automatically.

PostgreSQL reports deadlocks with SQLSTATE error code 40P01. An application may retry these as well, but it should not retry every database error without checking it. For example, a unique-constraint failure can point to an ongoing input or logic problem, rather than a temporary conflict.

Set a retry limit, and stop retrying when the request has used its latency budget. Use a backoff with jitter: add a random delay so concurrent transactions do not all retry at once. Record how many retries the application attempts, how many succeed, how many reach the limit, and how much time retries take. Test these application policies against the service’s workload and latency requirements.

Do not perform external actions inside a transaction that the application might retry. A database rollback cannot undo an email or an external payment request. Perform those actions after the transaction commits, or use a durable pattern such as an outbox, which lets the service match database changes with external work.

Keep transactions short and monitor old snapshots

A transaction stays open while the client waits, even when PostgreSQL is not running a query; idle in transaction describes a session with an open transaction and no query running. It can hold locks and keep old row versions visible, delaying vacuum cleanup and contributing to table bloat. Watch long-running transactions at every isolation level, especially those that hold a stable snapshot.

Do database work between BEGIN and COMMIT. Do not leave a transaction open while you wait for user input, call another service, or perform lengthy calculations.

Use pg_stat_activity to find old transactions and sessions idle inside an open transaction:

SELECT
    pid,
    usename,
    application_name,
    state,
    xact_start,
    now() - xact_start AS transaction_age,
    wait_event_type,
    wait_event,
    left(query, 300) AS query_sample
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

To narrow the results to idle sessions:

SELECT
    pid,
    usename,
    application_name,
    state,
    now() - state_change AS idle_duration,
    xact_start,
    now() - xact_start AS transaction_age
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start;

PostgreSQL’s activity monitoring documentation explains the session states and timing fields. Query text can contain sensitive information, so take care when showing it in dashboards or logs. PostgreSQL’s idle_in_transaction_session_timeout setting terminates a session that stays idle in an open transaction longer than the configured limit. Test the setting with your connection pools and service behavior before enabling it.

Track serialization failures and retries in application telemetry. PostgreSQL’s pg_stat_database_conflicts view counts queries canceled by recovery conflicts on standby servers. It does not count all Serializable failures on a primary server. Server logs help identify errors. Application metrics show whether a transaction eventually succeeded or reached its retry limit. PostgreSQL documents error and statement logging settings for recording relevant details.

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