PostgreSQL Concurrency: Locks, MVCC, and Why Your Queries Are Waiting

-- Session 1:
BEGIN;
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (42, 7, 3);
-- Takes FOR KEY SHARE lock on orders.id = 42
-- Does NOT block SELECTs on orders

-- Session 2:
DELETE FROM orders WHERE id = 42;
-- BLOCKS! DELETE takes FOR UPDATE, which conflicts with FOR KEY SHARE
-- Even though Session 1 hasn't committed yet

This is actually correct behaviour — you don't want to delete an order that has in-flight items being inserted. But it manifests as a mysterious lock wait that developers don't expect.

Predicate Locks and Serializable Isolation: The Heavy Artillery

Serializable isolation in PostgreSQL doesn't use lock-based serialization (the way older databases do). Instead, PostgreSQL uses Serializable Snapshot Isolation (SSI), which tracks read-write dependencies between concurrent transactions and rolls back transactions that would create serialization anomalies.

The implementation relies on SIREAD locks (serializable read locks), also called predicate locks. These are not traditional locks that block — they're more like assertions that a read was valid at a specific point. If a concurrent write would invalidate that assertion, one transaction is rolled back.

-- Serializable transactions track what they've read
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- This SELECT establishes an SIREAD lock on the predicate:
-- "I read all orders where user_id = 123 AND status = 'active'"
SELECT COUNT(*) FROM orders WHERE user_id = 123 AND status = 'active';
-- Returns: 3

-- Meanwhile, another serializable transaction runs:
-- INSERT INTO orders (user_id, status) VALUES (123, 'active');
-- This write would change the result of the SELECT above.
-- PostgreSQL detects the rw-dependency.

-- If Session 1 tries to COMMIT:
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- DETAIL: Reason code: Canceled on commitment due to a read/write conflict.

The key difference from traditional locking: neither transaction blocked. They both ran concurrently. PostgreSQL detected the anomaly and rolled one back at commit time.

The practical rule for serializable: always code a retry loop. Serializable transactions will get serialization failures. It's not a bug — it's how the isolation level works:

import psycopg2
from psycopg2 import errors

def transfer_funds(conn, from_acct: int, to_acct: int, amount: int, max_retries: int = 3):
    for attempt in range(max_retries):
        try:
            with conn:
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                    cur.execute(
                        "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                        (amount, from_acct)
                    )
                    cur.execute(
                        "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                        (amount, to_acct)
                    )
                return True
        except errors.SerializationFailure:
            if attempt == max_retries - 1:
                raise
            continue

Spotting Lock Problems Before They Spot You

PostgreSQL exposes two invaluable views for lock debugging:

SELECT
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS table_name,
    a.query_start,
    a.state,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.locktype = 'relation'
  AND l.relation IS NOT NULL
  AND a.pid != pg_backend_pid()
ORDER BY a.query_start;

-- Find the blocker chain
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocking.state AS blocking_state,
    age(now(), blocking.query_start) AS blocking_duration
FROM pg_stat_activity blocked
CROSS JOIN LATERAL pg_blocking_pids(blocked.pid) AS blocker_pid
JOIN pg_stat_activity blocking ON blocking.pid = blocker_pid
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocking_duration DESC;

The fix is mechanical: do I/O outside the transaction. Either move the email/API call before the transaction starts, or after it commits:

def process_order
  order = Order.find(params[:id])
  # I/O first
  ShippingService.create_label(order)
  EmailService.send_confirmation(order)

  # Then the transactional update — fast, no waiting
  Order.transaction do
    order.update!(status: 'processing', shipping_label: label)
  end
end

The same applies in any language. In Go:

// BAD: I/O inside transaction
func processOrder(db *sql.DB, orderID int) error {
    tx, _ := db.Begin()
    tx.Exec("UPDATE orders SET status = 'processing' WHERE id = $1", orderID)
    sendEmail(orderID)     // 2-second HTTP call while holding row lock!
    tx.Exec("UPDATE orders SET shipping_label = $1 WHERE id = $2", label, orderID)
    return tx.Commit()
}

// GOOD: I/O outside transaction
func processOrder(db *sql.DB, orderID int) error {
    label := createShippingLabel(orderID) // I/O first
    sendEmail(orderID)                     // I/O first
    // Then fast, transactional update
    tx, _ := db.Begin()
    tx.Exec("UPDATE orders SET status = 'processing', shipping_label = $1 WHERE id = $2", label, orderID)
    return tx.Commit()
}

-- Kill any transaction that sits idle for more than 30 seconds
ALTER DATABASE myapp SET idle_in_transaction_session_timeout = '30s';

It's a blunt instrument — it'll kill legitimate long-running transactions too. But if your application has "idle in transaction" connections today, this is the fastest safety net you can deploy. You can always exclude specific sessions.

Deadlocks: It's Almost Always a Lock Ordering Problem

PostgreSQL detects deadlocks automatically and resolves them by aborting one of the transactions. The victim gets:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890;
blocked by process 98765.
Process 98765 waits for ShareLock on transaction 54321;
blocked by process 12345.
HINT: See server log for query details.

Deadlocks are not random. They're deterministic — two (or more) transactions lock resources in different orders. The classic example:

-- Transaction A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Wants to lock id=2

-- Transaction B (concurrent):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- Locks id=2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- Wants to lock id=1
-- DEADLOCK: A holds 1, wants 2. B holds 2, wants 1.
``$

The fix is also deterministic: **establish a consistent lock ordering.** In the accounts example, always lock the lower ID first:

-- A single function that enforces ordering:

UPDATE accounts SET balance = balance - $3 WHERE id = LEAST($1, $2);

UPDATE accounts SET balance = balance + $3 WHERE id = GREATEST($1, $2);

This eliminates the deadlock entirely. No two concurrent transfers can deadlock because both always lock the lower ID first.

Advisory Locks: The Power Tool Nobody Uses Correctly

PostgreSQL has a mechanism called advisory locks — application-level locks that don't correspond to any database object. They're incredibly useful for distributed coordination, but most developers don't know they exist.

-- Session-level advisory lock (held until transaction ends or explicit unlock):
SELECT pg_advisory_lock(42);
-- ... do exclusive work ...
SELECT pg_advisory_unlock(42);

-- Transaction-level advisory lock (released on COMMIT/ROLLBACK):
SELECT pg_advisory_xact_lock(42);
-- Released automatically at end of transaction

The killer use case: distributed job scheduling without Redis.

-- Multiple workers compete to process the same job.
-- Exactly one will get the lock:
SELECT pg_try_advisory_lock(42) AS acquired;
-- Returns true for exactly one worker. That worker processes the job.

No Redis. No ZooKeeper. No external dependency. Just Postgres.

-- Use two-argument advisory locks with a namespace:
-- First argument = application ID (unique per app)
-- Second argument = resource ID within that app
SELECT pg_advisory_lock(hashtext('myapp_jobs'), 42);

SET lock_timeout = '100ms';
SELECT pg_advisory_lock(42);
-- If not acquired in 100ms, raises: ERROR: canceling statement due to lock timeout
``$

---

## Practical Recommendations

After years of PostgreSQL concurrency debugging, here's what I configure on every project:

### 1. Set a global `idle_in_transaction_session_timeout`

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';

SELECT pg_reload_conf();

Start conservative (5 minutes) then tighten based on your application's transaction patterns. The goal is to prevent the "idle in transaction holding locks" scenario from lasting longer than necessary.

### 2. Set `lock_timeout$ in your migration runner

Every migration tool (Alembic, Flyway, golang-migrate) should set a lock timeout before running DDL:

SET lock_timeout = '2s';

-- Your migration SQL here

If the migration can't grab its locks in 2 seconds, it fails fast instead of building a queue that takes down your application. You can retry the migration during off-peak hours.

### 3. Add indexes on ALL foreign key columns

-- Find missing FK indexes:

SELECT

conname AS constraint_name,

conrelid::regclass AS table_name,

a.attname AS column_name

FROM pg_constraint c

JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid

WHERE c.contype = 'f'

AND NOT EXISTS (

SELECT 1 FROM pg_index i

WHERE i.indrelid = c.conrelid

AND i.indkey[0] = a.attnum

);

Every FK column without an index is a potential deadlock source on `DELETE$ and a guaranteed sequential scan on `JOIN$.

### 4. Use `FOR UPDATE SKIP LOCKED$ for queue-like workloads

If you're implementing a job queue in PostgreSQL (which is fine for moderate throughput — don't let anyone tell you otherwise), use `SKIP LOCKED$ to avoid contention:

-- Instead of: SELECT * FROM jobs WHERE status = 'pending' LIMIT 1 FOR UPDATE

-- (which blocks if another worker is processing any pending job)

-- Use:

SELECT * FROM jobs

WHERE status = 'pending'

ORDER BY created_at

LIMIT 1

FOR UPDATE SKIP LOCKED;

-- Skips rows locked by other workers. Never blocks.

### 5. Monitor lock wait times

-- Check for queries that spend time waiting for locks:

SELECT

pid,

wait_event_type,

wait_event,

state,

query_start,

query

FROM pg_stat_activity

WHERE wait_event_type = 'Lock'

AND state != 'idle';

Summary

PostgreSQL's concurrency model is brilliant — when you understand it. The problems start when developers assume it works the same way as MySQL, or when ORMs hide transaction boundaries in ways that produce "idle in transaction" without anyone noticing.

The invariants worth remembering: