PostgreSQL Transactions in Practice: Isolation, Row Locks, and Deadlocks

A transaction is more than wrapping several SQL statements in BEGIN and COMMIT. When requests concurrently read and modify data, an application must choose an isolation level, lock the correct rows, keep transactions short, and deliberately retry concurrency failures.

ACID does not automatically protect every business rule

PostgreSQL provides atomicity and constraint-level consistency, but it cannot infer “never oversell stock” or “seat count must remain below capacity” when an application reads and writes in separate steps. Two transactions may read valid state and make conflicting decisions.

Constraints protect simple invariants; locks or higher isolation protect decisions based on changing data.

MVCC enables concurrent reads and writes

PostgreSQL uses Multi-Version Concurrency Control. Each statement or transaction reads an appropriate snapshot while updates create new row versions. Readers therefore normally do not block writers in the manner of simple read locking.

MVCC does not remove writer conflicts. VACUUM must eventually clean old versions, and long transactions retain old snapshots and can increase bloat.

1. Structure transactions correctly

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE id = 10 AND balance >= 500;

UPDATE accounts
SET balance = balance + 500
WHERE id = 20;

COMMIT;

The application must check the affected-row count of the debit. If it is zero, roll back because funds are insufficient or the account is missing. An atomic update is safer than reading a balance into the application and writing a new value.

Do not call APIs, send email, or wait for user input while holding a transaction. Perform side effects after commit or use a transactional outbox to coordinate database writes with reliable messaging.

2. Read Committed is the practical default

At Read Committed, each statement sees a snapshot from the beginning of that statement. Two SELECT statements in one transaction may see different results if another transaction commits between them.

It works well when a write can be expressed atomically using UPDATE ... WHERE, UPSERT, unique/check constraints, or explicit row locks. It does not protect multi-read business decisions without additional controls.

3. Repeatable Read provides a stable snapshot

Repeatable Read gives a transaction a stable snapshot from its first query. It helps multi-query reports and complex logic, but conflicting updates can raise serialization failures. Applications must rerun the complete transaction.

Do not use higher isolation as a substitute for concurrency design. Longer transactions retain snapshots, increase conflict probability, and can interfere with vacuum.

4. Serializable produces serial-equivalent results

PostgreSQL implements Serializable Snapshot Isolation and permits commits only when results are equivalent to a valid serial order. It may abort a transaction with SQLSTATE 40001 when an anomaly could occur.

This is powerful for invariants spanning rows or predicates, but the contract requires application retries. Retry the entire transaction callback, including every decision based on prior reads, not only the failing statement.

5. SELECT FOR UPDATE and row locks

BEGIN;

SELECT id, stock
FROM products
WHERE id = 42
FOR UPDATE;

UPDATE products
SET stock = stock - 1
WHERE id = 42 AND stock > 0;

COMMIT;

FOR UPDATE prevents conflicting updates, deletes, and locks on the row until transaction end. Use it for read-modify-write logic in application code. When one UPDATE ... WHERE stock > 0 RETURNING ... can express the operation, it is generally shorter and locks less.

• FOR NO KEY UPDATE: weaker when foreign-key-relevant keys do not change.
• FOR SHARE and FOR KEY SHARE: protect specific read/reference cases.
• NOWAIT: fail immediately instead of waiting.
• SKIP LOCKED: skip locked rows, useful for worker queues but not general queries because it returns an inconsistent view.

6. How deadlocks form

Transaction A locks account 10 and waits for 20; transaction B holds 20 and waits for 10. PostgreSQL detects the cycle and aborts one transaction with SQLSTATE 40P01. Never depend on which transaction is selected.

The best defense is a consistent lock order:

SELECT id
FROM accounts
WHERE id IN (10, 20)
ORDER BY id
FOR UPDATE;

Update after locking. Transactions should be short and acquire the strongest needed lock first. Deadlocks can still occur, so production code needs bounded retries.

7. Retry correctly with backoff

for ($attempt = 1; $attempt <= 3; $attempt++) {
    try {
        return runWholeTransaction();
    } catch (DatabaseException $e) {
        if (! in_array($e->sqlState(), ['40001', '40P01'], true)) {
            throw $e;
        }

        if ($attempt === 3) {
            throw $e;
        }

        usleep(random_int(20_000, 100_000) * $attempt);
    }
}

Retry the complete transaction with a finite limit and jitter. The callback must be safe to repeat: do not email, charge a payment, or publish a non-idempotent message before commit. A 23505 unique violation can be a race or a permanent data error, so do not retry it blindly.

8. Fail predictably with timeouts

SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';
SET LOCAL idle_in_transaction_session_timeout = '30s';

lock_timeout limits waiting for locks; statement_timeout limits statements; idle_in_transaction_session_timeout handles abandoned open transactions. Tune them to the SLA and workload rather than applying one value everywhere.

SET LOCAL lasts only for the current transaction. After a timeout, the transaction may be aborted and must be rolled back before returning its connection to a pool.

9. Find blocking sessions in production

SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocker.pid AS blocker_pid,
    blocker.query AS blocker_query,
    now() - blocker.xact_start AS blocker_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker
  ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

pg_locks provides a cluster-wide view of granted and waiting locks. Combine it with pg_stat_activity for query, user, state, and transaction age. Find idle in transaction sessions that may retain locks and snapshots while doing no work.

Do not immediately call pg_terminate_backend. Identify the blocker, rollback impact, owner, and retry behavior. Afterward, fix code or timeouts instead of merely killing the session.

10. Prefer constraints and idempotency to manual locks

Let the database enforce representable invariants using UNIQUE, CHECK, FOREIGN KEY, exclusion constraints, and atomic DML. A unique idempotency key, for example, prevents duplicate requests from creating two payment records.

Advisory locks fit logical resources that do not map to a row, but keys must be stable and transaction-level locks are often safer than session-level locks. They are cooperative: every code path must follow the same convention.

Common anti-patterns

• Reading a balance or stock count and then updating without a condition.
• Holding a transaction during HTTP calls or queue waits.
• Retrying one statement instead of the complete transaction.
• Locking rows in different orders across code paths.
• Using SKIP LOCKED when complete query results are required.
• Returning idle in transaction sessions to a pool.
• Making everything Serializable without handling 40001.

Production checklist

1. Database constraints protect every representable business invariant.
2. Transactions are short and contain no network side effects.
3. Isolation level matches the anomaly being prevented.
4. Multiple rows are locked in a consistent order.
5. 40001 and 40P01 trigger bounded whole-transaction retries.
6. Side effects use idempotency or an outbox.
7. Timeouts and connection-pool behavior are configured.
8. Dashboards monitor lock waits, deadlocks, and transaction age.

Conclusion

Correct PostgreSQL concurrency combines atomic SQL, constraints, MVCC, isolation, and deliberate locking. Read Committed handles most CRUD when statements are designed well; row locks protect read-modify-write; Serializable protects complex invariants with a retry requirement. Short transactions, consistent lock order, and good production visibility turn deadlocks into controlled errors instead of mysteries.

References

• PostgreSQL: Concurrency Control
• PostgreSQL: Transaction Isolation
• PostgreSQL: Explicit Locking and Deadlocks
• PostgreSQL: Serialization Failure Handling
• PostgreSQL: Viewing Locks