Managing Concurrent Transactions in PostgreSQL

When several PostgreSQL sessions read and change the same data, the database must control which changes each transaction sees and which changes can commit together. PostgreSQL uses multiversion concurrency control (MVCC), which stores multiple versions of a row. Each query reads a snapshot, a consistent view of the data, while other transactions make changes. As a result, ordinary reads and writes generally do not block one another, though conflicting writes can wait. PostgreSQL’s concurrency model

MVCC alone does not guarantee that every combination of successful transactions preserves a business rule. PostgreSQL’s isolation levels control what each transaction sees and which conflicting outcomes the database prevents. Choose an isolation level, a lock, or a database constraint based on the rule you need to protect, and design the application to handle transactions PostgreSQL aborts.

Choose an isolation level for the invariant

A transaction is a group of database statements that commit together or roll back together. Its isolation level determines which concurrent changes PostgreSQL lets those statements see.

PostgreSQL accepts READ UNCOMMITTED, but treats it as READ COMMITTED. It does not allow a transaction to read another transaction’s uncommitted changes. The PostgreSQL isolation-level documentation describes these guarantees and their limits.

These limits matter when a transaction reads data and then makes a decision. Under Read Committed, a later command can see a change that committed after an earlier command. Under Repeatable Read, queries keep the same snapshot, but concurrent transactions can still commit changes whose combined result no one-at-a-time execution could produce. Serializable prevents this kind of anomaly by aborting transactions when PostgreSQL detects a dangerous conflict.

Use one statement for simple conditional changes

For a straightforward update, put the condition and the change in the same SQL statement. This avoids making a decision from a separate read, since another transaction can change the data before the update.

For example, decrement inventory only when stock remains available:

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

If another transaction updates the same row first, PostgreSQL can wait for that transaction and then re-check whether the row still matches the WHERE condition. The statement changes the row only if the condition holds when PostgreSQL applies the update. This pattern suits a targeted, single-row decision; it does not automatically protect a rule that depends on several rows.

Use Repeatable Read for a stable snapshot

Repeatable Read keeps successive queries in a transaction on the same snapshot. That helps when the transaction needs to compare or process data consistently across multiple queries.

A stable snapshot does not make every decision involving several rows safe. For example, two transactions can each read a set of rows, make a decision based on those rows, and insert or update different rows. Both can commit under Repeatable Read even when no one-at-a-time execution could produce the combined result. Use Serializable when the business rule must hold across this kind of concurrent reading and writing.

Repeatable Read can also abort a transaction when it tries to update or lock a row that another transaction changed after its snapshot began. PostgreSQL reports a serialization failure in such cases. The application must restart the entire transaction using fresh reads and decisions.

Use Serializable when the rule spans rows

Serializable is useful when correctness depends on a changing set of rows, such as a rule that requires at least one qualified staff member to remain on duty. The transactions that read and change data relevant to the rule need to participate in Serializable transactions; using a weaker level for relevant work can undermine the check.

PostgreSQL uses predicate locks to record which data a transaction read, including conditions that define a set of rows. These locks do not block writes. PostgreSQL uses the records to find read/write dependencies, where one transaction reads data that another changes. If those dependencies could produce a result that no one-at-a-time execution could produce, PostgreSQL aborts a transaction. The application must then rerun the complete business operation, including its reads and decision logic.

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Read the facts used to decide what to change.
-- Apply all related writes.

COMMIT;

Serializable adds monitoring and can cause retries, so it does not guarantee higher throughput for every workload. Explicit locks can also make transactions wait. Compare the behavior under the actual workload rather than assuming one isolation level is always faster.

Lock specific rows when you know what must be protected

Use SELECT ... FOR UPDATE when a transaction must inspect a known row and prevent another transaction from changing or locking that row before the current transaction finishes. PostgreSQL blocks conflicting writers or lockers, while ordinary reads can continue.

BEGIN;

SELECT balance
FROM accounts
WHERE account_id = $1
FOR UPDATE;

-- Check the balance and apply the related change.

COMMIT;

Keep the transaction short after acquiring the lock. A transaction that holds a row lock while doing unrelated work makes other transactions wait. Row locks can also contribute to deadlocks, where transactions wait on each other in a cycle. PostgreSQL detects a deadlock and aborts one transaction, but does not promise which one it will choose. When a transaction locks multiple rows, acquire them in a consistent order across code paths. For example, update account rows by ascending account ID rather than in whichever order each request happens to encounter them. PostgreSQL’s explicit-locking documentation describes row locks and deadlock handling.

Let constraints enforce rules they can express

Use a database constraint when it directly represents the rule. A unique constraint, for example, prevents duplicate values. A separate “check whether the value exists, then insert it” sequence can race: two transactions can both check before either inserts.

Constraints and transaction isolation solve different parts of the problem. A constraint protects the condition it expresses. Serializable transactions or explicit locks handle decisions that depend on broader reads or relationships among rows. PostgreSQL also documents cases where overlapping Serializable transactions can still encounter a uniqueness violation, so the application must handle the relevant database errors because the isolation level does not prevent every conflict.

Retry the whole transaction after a serialization failure

PostgreSQL does not rerun an aborted transaction automatically. The database cannot determine whether the application’s original decision remains valid after concurrent data changes.

Applications using Repeatable Read or Serializable should handle SQLSTATE 40001, which identifies a serialization failure, by restarting the entire transaction. Include the reads and application logic that determine which statements to issue; retrying only the statement that failed can reuse decisions based on stale data. SQLSTATE 40P01 identifies a deadlock, and the application may also retry the complete transaction after that error. PostgreSQL’s retry guidance explains these errors and why the whole transaction must run again.

Do not blindly retry every database error. A unique violation (23505) or exclusion violation (23P01) can reflect a persistent data conflict or a bug in the application’s decision logic, rather than a temporary serialization conflict. Inspect the error and retry only when rerunning the complete operation is valid.

Find blocked sessions before changing isolation

When a request waits longer than expected, check which sessions are active and which sessions block them. PostgreSQL provides pg_stat_activity for session details and pg_blocking_pids() to identify blockers. For example:

SELECT
    pid,
    pg_blocking_pids(pid) AS blocked_by,
    wait_event_type,
    wait_event,
    state,
    query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Use this information to find the transaction holding a lock and the query waiting for it. PostgreSQL’s lock-monitoring documentation recommends pg_blocking_pids() for identifying blocking sessions.

A lock_timeout limits how long a statement waits to acquire a lock. The other setting, statement_timeout, caps the statement’s total execution time. These settings address different waits; choose them based on the behavior the application should expose when a request cannot proceed. Long-running transactions and transactions left idle also hold locks. They can prevent PostgreSQL from removing older row versions that might still be visible to them.

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