Postgres locking is a deep topic with surface-level mistakes. The defaults are good; the gotchas hide in concurrent paths. This post is the working playbook.

Lock modes

Postgres has many lock modes; the ones you’ll touch:

  • FOR UPDATE — strongest row lock; blocks other UPDATE/SELECT FOR UPDATE.
  • FOR NO KEY UPDATE — slightly weaker; allows FK references.
  • FOR SHARE — blocks UPDATE but not SELECT FOR SHARE.
  • FOR KEY SHARE — weakest; for FK enforcement.

Default SELECT takes no row lock — just a snapshot.

SELECT FOR UPDATE

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- locked row; other tx waits
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- lock released

Read-modify-write atomically. Without FOR UPDATE, two concurrent transactions can both read 500, both decrement to 400, lose 100.

NOWAIT

SELECT ... FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- error if locked, instead of blocking

For “if this is locked, give up” — nice in interactive UIs.

SKIP LOCKED

SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY priority, created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

The job queue pattern: each worker grabs an unlocked row. Other workers skip it. Multi-consumer queue without external infra.

-- Worker
BEGIN;
SELECT id, payload FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

UPDATE job_queue SET status = 'processing' WHERE id = ANY($1);
COMMIT;

-- ... process ...

UPDATE job_queue SET status = 'done' WHERE id = ANY($1);

Postgres as message queue. See also Postgres LISTEN/NOTIFY for real-time triggers .

Advisory locks

For application-level locking that doesn’t need row state:

SELECT pg_advisory_lock(12345);
-- exclusive lock by key 12345
-- ... critical section ...
SELECT pg_advisory_unlock(12345);

Or transaction-scoped:

SELECT pg_advisory_xact_lock(12345);
-- released on commit/rollback automatically

Use cases:

  • Cron jobs — only one instance runs.
  • Initialization — only one process bootstraps.
  • Per-entity locking without row contention.
async def with_advisory_lock(key: int, fn):
    await conn.execute("SELECT pg_advisory_lock($1)", key)
    try:
        return await fn()
    finally:
        await conn.execute("SELECT pg_advisory_unlock($1)", key)

Isolation levels

LevelPhantom reads?Non-repeatable reads?Serialization anomalies?
READ COMMITTED (default)YesYesYes
REPEATABLE READNoNoYes
SERIALIZABLENoNoNo

For most apps: READ COMMITTED is fine. Use REPEATABLE READ for read-heavy reports that need consistency. Use SERIALIZABLE only when you need true serializability (rare; expensive).

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

Deadlocks

Tx1: lock row A → tries to lock row B
Tx2: lock row B → tries to lock row A
→ deadlock; Postgres aborts one

Postgres detects and aborts. The aborted tx gets an error. Your app should retry.

async def retry_on_deadlock(fn, max_attempts=3):
    for attempt in range(max_attempts):
        try:
            return await fn()
        except DeadlockError:
            if attempt == max_attempts - 1: raise
            await asyncio.sleep(0.1 * (2 ** attempt))

Avoid them by acquiring locks in a consistent order.

Lock contention diagnosis

-- Currently waiting locks
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks bg ON bg.relation = bl.relation AND bg.granted
JOIN pg_stat_activity blocking ON blocking.pid = bg.pid
WHERE blocked.pid <> blocking.pid;

Find what’s waiting on what. Long-running transactions are the usual culprit.

-- Long-running transactions
SELECT pid, age(clock_timestamp(), query_start), query
FROM pg_stat_activity
WHERE state = 'active' AND query_start < now() - interval '5 minutes';

Common lock pitfalls

1. Long-held FOR UPDATE

async def transfer(from_id, to_id, amount):
    async with db.transaction():
        await db.fetchrow("SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", from_id)
        await long_external_api_call()  # BAD: row locked while we wait
        await db.execute(...)

Locks held during external calls = blast radius. Acquire late, release early.

2. Reading without a lock then writing

balance = await db.fetchval("SELECT balance FROM accounts WHERE id = $1", id)
# ... no lock ...
await db.execute("UPDATE accounts SET balance = $1 WHERE id = $2", balance - 100, id)
# Race condition!

Add FOR UPDATE or use atomic UPDATE: SET balance = balance - 100.

3. UPDATE without WHERE

Locks every row. Always WHERE.

4. SERIALIZABLE for everything

Adds overhead; can fail on serialization conflicts. Use only when necessary.

5. Forgetting NOWAIT in interactive paths

User clicks Edit; backend blocks for 30s waiting on a lock. Use NOWAIT and surface “someone else is editing.”

Optimistic locking alternative

Instead of locks, use a version column:

UPDATE posts SET title = $1, version = version + 1
WHERE id = $2 AND version = $3;

-- 0 rows affected = someone else updated; refetch and retry

No lock; lower contention. Only viable if you can detect and retry conflicts. Common in REST APIs (If-Match: <etag>).

What I’d ship today

For DB-heavy apps:

  • READ COMMITTED as default isolation.
  • FOR UPDATE for read-modify-write.
  • SKIP LOCKED for queue workers.
  • Advisory locks for cross-cutting (cron, init).
  • Atomic UPDATEs when possible (SET x = x + 1).
  • Deadlock retry wrapper at the repo layer.
  • Lock contention monitoring.

Read this next

If you want my Postgres-as-queue starter (SKIP LOCKED), it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .