PostgreSQL Transaction Isolation Levels: Practical Guide

Start with the PostgreSQL MVCC mental model

PostgreSQL uses multiversion concurrency control (MVCC): statements read from a database snapshot while concurrent writers create new row versions. Ordinary reads do not block writes, and writes do not block ordinary reads. The current PostgreSQL MVCC introduction explains this foundation.

Isolation determines which snapshot a statement receives and how PostgreSQL handles dangerous read/write combinations. It does not automatically validate your business rules. A unique constraint can prevent two identical booking keys; a check constraint can reject a negative value on one row; neither can express every rule involving a changing set of rows.

PostgreSQL transaction isolation levels: a practical decision matrix

PostgreSQL implements three distinct behaviors. A request for Read Uncommitted behaves as Read Committed. PostgreSQL also provides stronger Repeatable Read semantics than the SQL minimum: phantom reads are prevented, although serialization anomalies remain possible. These details are documented in the current transaction isolation guide.

The choice can be per workflow. A product catalog read may stay at Read Committed while account-limit reservations run at Serializable and a long export uses Repeatable Read, Read Only. Avoid changing the database-wide default until every transaction path, library, and retry policy has been audited.

Use Read Committed with atomic SQL, not application hope

Read Committed is PostgreSQL's default. A plain SELECT sees rows committed before that statement began, so two queries inside one transaction can legitimately see different committed states. An UPDATE that encounters a concurrently changed target may wait and then re-evaluate its WHERE clause against the updated row.

That behavior makes one conditional statement safer than a read-modify-write sequence for many counters and inventory reservations:

UPDATE inventory
SET available = available - $1
WHERE sku = $2
  AND available >= $1
RETURNING sku, available;

-- Zero returned rows means the reservation cannot be made.

The statement either decrements a qualifying row or returns no row. By contrast, reading available into application memory, checking it, and issuing an unconditional update creates a wider concurrency window and makes correctness depend on timing.

Let database constraints carry rules they can express

• Use unique constraints for identities such as one active idempotency key per account.
• Use check constraints for row-local rules such as non-negative quantities.
• Use foreign keys for required relationships.
• Use an atomic INSERT ... ON CONFLICT or conditional UPDATE where the desired outcome fits one statement.

Isolation is not a replacement for constraints. Constraints protect every writer, including scripts, jobs, and future service versions. If the workflow spans several statements, keep the transaction short and verify whether a concurrent commit between those statements changes the decision.

Choose Repeatable Read for a stable snapshot

At Repeatable Read, statements see rows committed before the transaction's first query or data-changing statement. Later commits by other transactions are not added to that transaction's view. This is useful when a report, export, reconciliation preview, or multi-step read must describe one database snapshot.

A stable snapshot is not the same as a globally valid business decision. Two transactions can read overlapping predicates, make complementary writes, and both commit even though their combined result cannot be explained by the business rule. PostgreSQL calls this a serialization anomaly. Its application-level consistency guidance warns that Repeatable Read alone is not enough for every integrity check.

• Good fit: several queries must agree about the same historical state.
• Poor fit by itself: “read a total, then insert if the total stays below a limit.”
• Retry requirement: an updating transaction can receive SQLSTATE 40001 when a target row changed after its snapshot began.
• Freshness limit: a long-running snapshot can be internally consistent but increasingly old.

Mark genuinely read-only work as READ ONLY. Keep it bounded; a transaction is not a user session and should not remain open while someone reviews a page or approves a dialog.

Use Serializable when the decision depends on a changing set

Serializable provides the strongest isolation. Successfully committed concurrent transactions have the same effect as some serial, one-at-a-time order. PostgreSQL implements this with Serializable Snapshot Isolation: it monitors read/write dependencies and aborts a transaction when the observed schedule could not be serialized.

Consider a credit-reservation workflow. Each request reads the sum of active reservations for an account and inserts a new reservation if the total remains within the account limit. Two requests can both read the old total and both insert. The invariant depends on a predicate—the set of active rows—not one known row. Serializable can detect the dangerous dependency:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT COALESCE(SUM(amount_cents), 0) AS reserved
FROM credit_reservations
WHERE account_id = $1
  AND status = 'active';

-- Insert only when reserved + requested <= the account limit.
INSERT INTO credit_reservations
  (id, account_id, amount_cents, status)
VALUES ($2, $1, $3, 'active');

COMMIT;

The application must treat a serialization failure as a request to rerun the whole decision from a fresh snapshot. A successful SELECT is provisional until COMMIT succeeds. The official PostgreSQL documentation identifies 40001 as serialization_failure and requires complete-transaction retry; see serialization failure handling.

Serializable is not “always lock everything”

Predicate locks used by PostgreSQL for Serializable anomaly detection do not block other transactions. They track dependencies and may cause an abort when the database cannot prove a safe serial order. There is monitoring overhead and retry cost, but explicit blocking locks also have costs. Choose with workload tests, not folklore.

Indexes still matter. They reduce ordinary query work and can affect the granularity of predicate-lock tracking. Measure the real workload, keep transactions focused on one invariant, and avoid adding unrelated reads that enlarge the dependency graph.

Use explicit locks when the contested resource is concrete

SELECT ... FOR UPDATE locks the returned rows against conflicting writers and lockers until the transaction ends. This is a good fit when the application knows the existing row that represents the scarce resource: an inventory item, a job lease, or a mutable account state.

BEGIN;
SET LOCAL lock_timeout = '2s';

SELECT id, available
FROM inventory
WHERE sku = $1
FOR UPDATE;

-- Validate and update immediately; do not wait for user input here.
UPDATE inventory
SET available = available - $2
WHERE sku = $1;

COMMIT;

Row locks do not block ordinary readers, but they can block writers to the same row. PostgreSQL's explicit locking documentation describes the lock modes and their conflicts.

• Lock every required row in a deterministic order, such as ascending primary key.
• Use the weakest lock mode that actually protects the operation.
• Set a bounded lock wait appropriate to the request path.
• Never hold a transaction open across user input, network calls, or slow file work.
• Do not assume a row lock protects rows that do not exist or every future row matching a predicate.

Deadlocks are possible even without an explicit LOCK TABLE. PostgreSQL detects a cycle and aborts one transaction. Consistent lock order prevents many deadlocks; SQLSTATE 40P01 can be retried when the complete workflow is safe to repeat.

Implement complete transaction retries in Node.js

With node-postgres, all statements in a transaction must use the same checked-out client; the official node-postgres transaction guide explicitly warns against using pool.query for transaction statements.

import type { Pool, PoolClient } from "pg";

type Isolation =
  | "READ COMMITTED"
  | "REPEATABLE READ"
  | "SERIALIZABLE";

const retryable = new Set(["40001", "40P01"]);

const delay = (ms: number) =>
  new Promise<void>((resolve) => setTimeout(resolve, ms));

function sqlState(error: unknown): string | undefined {
  if (typeof error !== "object" || error === null || !("code" in error)) {
    return undefined;
  }

  const code = (error as { code?: unknown }).code;
  return typeof code === "string" ? code : undefined;
}

export async function runTransaction<T>(
  pool: Pool,
  isolation: Isolation,
  work: (client: PoolClient) => Promise<T>,
  maxAttempts = 3
): Promise<T> {
  for (let attempt = 1; attempt <= maxAttempts; attempt += 1) {
    const client = await pool.connect();

    try {
      await client.query("BEGIN");
      await client.query(
        "SET TRANSACTION ISOLATION LEVEL " + isolation
      );
      await client.query("SET LOCAL lock_timeout = '2s'");
      await client.query("SET LOCAL statement_timeout = '5s'");

      const value = await work(client);
      await client.query("COMMIT");
      return value;
    } catch (error) {
      await client.query("ROLLBACK").catch(() => undefined);
      const code = sqlState(error);

      if (!code || !retryable.has(code) || attempt === maxAttempts) {
        throw error;
      }
    } finally {
      client.release();
    }

    // Back off only after returning the connection to the pool.
    const backoff = Math.min(25 * 2 ** (attempt - 1), 200);
    await delay(backoff + Math.floor(Math.random() * 25));
  }

  throw new Error("Transaction attempts exhausted");
}

The isolation value is selected from a closed TypeScript union rather than user input; SQL values inside work must still be parameterized. Set isolation before the first application query. Use bounded retries and jitter so a conflict does not create synchronized retry pressure.

Our Node.js transactional outbox guide covers reliable post-commit delivery. Connection ownership, pool sizing, and transaction hygiene are detailed in the Node.js database connection pooling guide.

Do not retry every database error

Retry 40001 as a transient concurrency outcome. A deadlock error 40P01 is also often retryable after rollback. Unique violations may be either a concurrency outcome or a persistent domain conflict, so do not retry 23505 indiscriminately. PostgreSQL's SQLSTATE reference provides stable machine-readable error codes; branch on those codes rather than localized message text.

Test the concurrent schedule, not only each request

A sequential integration test cannot expose a concurrency anomaly. Use two independent database clients and a barrier that pauses both transactions after the important read. Release them together, then assert the final invariant and the allowed outcomes.

1. Create isolated test data in a real PostgreSQL database.
2. Start transaction A and transaction B on separate connections.
3. Make both read the same initial state before either writes.
4. Release both writes at the same barrier.
5. Record commit or SQLSTATE outcome for each transaction.
6. Assert the invariant from a fresh third connection after both finish.
7. Repeat enough times to exercise alternate orderings without making timing the assertion.

Keep constraints enabled in tests and use the same isolation statement as production. Mocked repositories are useful for application branches, but they cannot validate MVCC snapshots, row locks, deadlock detection, or serialization failures.

Operate transactions as a bounded production resource

• Measure duration. Track transaction latency by workflow and isolation level, not raw user or tenant identifiers.
• Count outcomes. Monitor commits, rollbacks, 40001, 40P01, lock timeouts, statement timeouts, and exhausted retries.
• Inspect waits. Use pg_stat_activity and pg_locks during investigation; do not guess which session blocks another.
• Control pool pressure. A transaction owns a connection until commit or rollback. Long waits can exhaust the pool before the database reaches its connection limit.
• Find idle transactions. Alert on connections that remain idle in a transaction and apply a carefully tested server-side timeout.
• Keep logs safe. Record workflow name, attempt, SQLSTATE, duration, and a trace ID—not SQL parameters, secrets, or personal data.
• Separate replicas. Business invariants that decide writes must use the authoritative primary state; replica snapshots and lag have different semantics.

Multi-tenant applications must preserve authorization inside every attempt. Set transaction-local tenant context after BEGIN when using database policies, and rerun that setup on retry. Our PostgreSQL row-level security guide explains policy and service-role boundaries.

If indexes, constraints, or tables must change to support the chosen strategy, deploy them with the expand-contract and validation practices in our PostgreSQL zero-downtime migration guide.

Common transaction isolation mistakes

• Using Read Committed for a multi-statement read-then-write invariant without checking concurrent schedules.
• Assuming Repeatable Read prevents every anomaly because its snapshot is stable.
• Enabling Serializable without implementing complete, bounded retries.
• Retrying only the failed SQL statement instead of recomputing the entire decision.
• Performing payments, messages, or other irreversible side effects inside a retryable callback.
• Running transaction statements through different pooled clients.
• Locking rows in inconsistent order or without a lock timeout.
• Holding transactions open while waiting for a person or remote service.
• Using application checks where a unique, check, or foreign-key constraint is clearer.
• Changing the global isolation default before measuring abort rate and auditing all callers.

PostgreSQL transaction isolation FAQ

Should every financial workflow use Serializable?

No. The data invariant determines the mechanism. Atomic updates and database constraints may fully protect a balance or idempotency key. Serializable is valuable when a decision reads a changing set and then writes based on that observation. Document and test the exact invariant.

Does Repeatable Read block concurrent writes?

Ordinary snapshot reads do not block writers. If the transaction later tries to modify a row changed since its snapshot began, PostgreSQL can abort it with a serialization failure. Explicit row-locking clauses have separate blocking behavior.

Can SELECT FOR UPDATE prevent double booking?

It can protect an existing row that represents the bookable resource, provided every writer follows the same protocol. It does not lock a row that does not yet exist, so uniqueness constraints, exclusion constraints, predicate-aware Serializable transactions, or a different data model may be needed.

How many times should an application retry?

Use a small, bounded policy based on request latency, contention, and business behavior. There is no universal count. When attempts are exhausted, return a safe retryable domain response or move work to a durable queue. Alert on sustained increases rather than hiding them with unlimited retries.

Production transaction isolation checklist