PostgreSQL Locking & Concurrency - Complete Notes

An in-depth, practical and interview-focused guide to table and row locks, locking reads, deadlocks, advisory locks, lock monitoring, timeouts, and reliable worker queues with SKIP LOCKED.

00. The locking mental model

MVCC lets readers and writers overlap, while locks protect operations that must not overlap. Correct concurrency comes from identifying the exact resource, choosing the weakest sufficient lock, acquiring locks in a stable order, and holding them for the shortest complete transaction.

01. MVCC and locks work together

PostgreSQL does not replace locks with MVCC. MVCC handles visibility; locks coordinate conflicting actions and protect database objects.

An ordinary SELECT usually reads a snapshot and does not wait for an uncommitted row update. It sees the version allowed by its snapshot. At the same time, the query takes an ACCESS SHARE table lock so that a conflicting operation such as DROP TABLE cannot remove the relation while it is being read.

An UPDATE creates a new row version under MVCC, but it also takes a table-level ROW EXCLUSIVE lock and a row-level lock on each affected row. Another writer targeting the same row must wait, fail immediately with NOWAIT, skip it with SKIP LOCKED where supported, or be canceled by a timeout.

-- Unsafe when two sessions can do this concurrently.
SELECT stock FROM products WHERE id = 10;
-- Application sees stock = 1.
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- Better when the whole rule fits one atomic statement.
UPDATE products
SET stock = stock - 1
WHERE id = 10
  AND stock > 0
RETURNING id, stock;

The second form lets the database evaluate the condition and update under the write operation's concurrency rules. Zero returned rows means no stock was reserved. When several tables or additional business checks are involved, an explicit transaction with locking or Serializable isolation may still be necessary.

What PostgreSQL can lock

• Relations, including tables, indexes, views and sequences.
• Individual table rows, represented through tuple state and transaction IDs.
• Pages briefly for internal buffer access.
• Transaction IDs and virtual transaction IDs while sessions wait for transactions.
• Database objects and extension-specific resources.
• Application-defined numeric advisory-lock keys.
• Predicate-lock structures used by Serializable Snapshot Isolation. These detect dangerous dependency patterns and are not ordinary blocking row locks.

02. Lock acquisition, waiting and release

A request is granted immediately if it is compatible with existing holders. Otherwise it joins the wait queue until the conflict ends, a timeout fires, the query is canceled, or deadlock detection aborts a participant.

1. A statement requests the table locks required to access its relations.
2. As rows are found, a modifying or locking statement requests row locks as needed.
3. A compatible request is granted; an incompatible request waits.
4. The lock normally remains held until the owning transaction ends.
5. Commit or rollback releases transaction-scoped locks and wakes eligible waiters.

Locks acquired after a savepoint are released when the transaction rolls back to that savepoint. This also applies when a PL/pgSQL exception block rolls back its internal subtransaction. Earlier locks survive.

BEGIN;

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

SELECT id, balance
FROM accounts
WHERE id = 42
FOR UPDATE;

UPDATE accounts
SET balance = balance - 50
WHERE id = 42;

COMMIT;

03. Table-level lock modes

Every table access takes a table-level lock. The historical names can be misleading: ROW SHARE and ROW EXCLUSIVE are table-level modes, not row locks.

Explicit LOCK TABLE

BEGIN;

LOCK TABLE daily_totals IN SHARE ROW EXCLUSIVE MODE NOWAIT;

-- Perform the operation that requires this exclusion level.
UPDATE daily_totals
SET amount = amount + 100
WHERE business_date = CURRENT_DATE;

COMMIT;

LOCK TABLE must be used inside a transaction block because a lock released immediately after the statement would provide no useful protection. If the mode is omitted, PostgreSQL requests ACCESS EXCLUSIVE, the strongest mode. Always state the mode explicitly and verify its conflict set.

Ask for an explicit table lock only when the invariant truly concerns the table or a broad set of rows. Locking an entire hot table to protect one account is correct but unnecessarily destroys concurrency. A row lock, unique constraint, exclusion constraint, atomic DML statement, or advisory lock may express the rule more precisely.

04. Row-level lock modes and conflicts

PostgreSQL has four row-lock strengths. The right choice describes what concurrent changes must be prevented, especially whether a key referenced by a foreign key may change.

The conflict table is symmetric even though it is often read as "requested versus existing." Shared modes can coexist only where the matrix permits. A transaction never conflicts with itself, and it can strengthen a lock it already owns.

Locks acquired by data changes

• DELETE takes the strongest FOR UPDATE-like row lock.
• An UPDATE that changes columns covered by a suitable unique index usable by a foreign key takes a FOR UPDATE-like lock.
• Other updates normally take the weaker FOR NO KEY UPDATE-like lock.
• Foreign-key checks can take FOR KEY SHARE-like protection on referenced rows so a parent key cannot disappear while the relationship is established.

The exact set of columns that causes the stronger update lock is an implementation detail that can change. Design for documented semantics, not for a fragile assumption that one update will always choose a particular internal lock.

05. SELECT locking clauses

A locking read finds rows and locks them as part of one statement. It closes the race between "read a row" and "reserve that row" when the transaction will act on the result.

SELECT select_list
FROM table_expression
WHERE condition
FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE }
    [ OF table_alias [, ...] ]
    [ NOWAIT | SKIP LOCKED ];

FOR UPDATE

Use FOR UPDATE when the selected row may be updated in any way or deleted and the transaction needs the strongest reservation. Other sessions attempting incompatible row locks, updates, or deletes wait until the transaction ends.

BEGIN;

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

UPDATE accounts SET balance = balance - 100 WHERE id = 10;
UPDATE accounts SET balance = balance + 100 WHERE id = 20;

COMMIT;

Both accounts are locked in ascending ID order before either balance changes. Every transfer path should use the same order. The transaction must still validate sufficient funds and rely on constraints where suitable.

FOR NO KEY UPDATE

Use FOR NO KEY UPDATE when a row will change but its key values must not change. It is weaker than FOR UPDATE because it can coexist with FOR KEY SHARE. This can improve concurrency when child inserts only need to protect a referenced parent key.

FOR SHARE

FOR SHARE lets multiple sessions hold shared row locks, but blocks updates, deletes, FOR UPDATE, and FOR NO KEY UPDATE. Use it only when shared lockers may proceed together and any row change must wait. It is stronger than many read-only checks need.

FOR KEY SHARE

FOR KEY SHARE is the weakest row lock. It prevents deletion and changes to key values while allowing non-key updates and all weaker compatible locking reads. It is useful when the identity of a referenced row must remain stable but other attributes may change.

NOWAIT

BEGIN;

SELECT id, status
FROM orders
WHERE id = 7001
FOR UPDATE NOWAIT;

-- If the row is immediately available, continue.
UPDATE orders SET status = 'cancelled' WHERE id = 7001;

COMMIT;

NOWAIT is appropriate when waiting would be worse than returning a "currently being edited" response. The error still leaves the transaction failed unless handled through a savepoint. It applies to row locking, not to the required ROW SHARE table lock. Use explicit LOCK TABLE ... NOWAIT first if table-lock waiting must also fail fast.

06. Locking-read scope and edge cases

The visible syntax is short, but joins, limits, ordering, CTEs and isolation levels determine which rows are locked and what happens after a wait.

Limit locking to selected join inputs with OF

SELECT o.id, o.status, c.display_name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.id = 7001
FOR UPDATE OF o;

Without an OF list, a top-level locking clause affects rows from every lockable table contributing to the result. Use table aliases in OF to avoid locking unrelated join inputs. The clause identifies tables, not selected output columns.

LIMIT, OFFSET and returned rows

• A top-level locking clause locks rows that are returned by the query.
• With LIMIT, locking stops after enough rows have been returned.
• Rows stepped over by OFFSET are still locked.
• A cursor locks rows that it fetches or steps past, not necessarily the whole result at once.
• Use a unique, deterministic ORDER BY when a limited worker query must choose rows predictably.

CTEs, views and subqueries

A locking clause on a view or subquery can propagate to underlying tables, but a top-level clause does not automatically lock rows inside an independently referenced WITH query. Put the locking clause inside the CTE that selects the rows when that is the intended claim point.

WITH selected_orders AS (
    SELECT id
    FROM orders
    WHERE status = 'ready'
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 20
)
SELECT o.*
FROM orders AS o
JOIN selected_orders AS s ON s.id = o.id;

Query shapes that cannot identify individual rows

PostgreSQL rejects locking clauses where result rows cannot be clearly mapped back to table rows. Current restrictions include several uses with aggregation, GROUP BY, HAVING, DISTINCT, window clauses, and set-operation results. Lock base rows in a separate query layer and aggregate afterward when the design genuinely requires both.

What happens after waiting

At READ COMMITTED, a locking read can wait for a row-changing transaction. After the other transaction ends, PostgreSQL attempts to lock and return the updated version if it still qualifies, or returns no row if it was deleted or no longer qualifies. At REPEATABLE READ or SERIALIZABLE, a row changed since the transaction began can instead cause a serialization failure.

07. DML, constraints and concurrency patterns

The best lock is often the one PostgreSQL takes for an atomic statement or constraint. Prefer declarative correctness before adding manual lock protocols.

Conditional UPDATE

UPDATE events
SET seats_remaining = seats_remaining - 1
WHERE id = 55
  AND seats_remaining > 0
RETURNING id, seats_remaining;

Concurrent updates to the same row are serialized. PostgreSQL rechecks the condition against the relevant current row version at READ COMMITTED. The application uses the returned-row count as the outcome instead of trusting an earlier read.

Let constraints arbitrate races

CREATE TABLE username_claims (
    username text PRIMARY KEY,
    user_id bigint NOT NULL,
    claimed_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

INSERT INTO username_claims (username, user_id)
VALUES (lower($1), $2)
ON CONFLICT (username) DO NOTHING
RETURNING username, user_id;

A unique index resolves concurrent attempts correctly even when both sessions initially observe no row. A check-then-insert protocol with only SELECT FOR UPDATE cannot lock a row that does not exist. Unique constraints, exclusion constraints, or Serializable isolation are usually better tools for absence-based rules.

Foreign keys and hidden contention

A child insert must ensure that its referenced parent key remains valid, while a parent delete or key update must inspect referencing rows. Missing indexes on referencing columns can make parent changes scan and lock far more work than expected. PostgreSQL does not automatically create the child-side index for every foreign key, so add one when the workload needs efficient lookups or parent changes.

UPSERT hot keys

INSERT ... ON CONFLICT safely coordinates conflicting unique keys, but many sessions targeting one key still serialize around that key. Correct syntax does not remove a hot spot. Shard counters, batch writes, or redesign the contention point only when measurements show the single key is a bottleneck.

08. Deadlocks: detection, prevention and retry

Blocking is a line: one transaction waits for another. A deadlock is a cycle: every participant waits for a lock that another participant in the cycle holds, so none can make progress.

-- Session A                              -- Session B
BEGIN;                                    BEGIN;
UPDATE accounts                           UPDATE accounts
SET balance = balance - 10                SET balance = balance - 20
WHERE id = 1;                             WHERE id = 2;

UPDATE accounts                           UPDATE accounts
SET balance = balance + 10                SET balance = balance + 20
WHERE id = 2;  -- waits for B             WHERE id = 1;  -- waits for A

PostgreSQL waits for deadlock_timeout before running the relatively expensive deadlock check. If it finds a cycle, it aborts one transaction and reports SQLSTATE 40P01. Which participant becomes the victim is not a contract and must not be relied on.

Prevent cycles

1. List every resource that a transaction path can lock.
2. Define one global ordering key, such as account ID ascending.
3. Make every code path, trigger and background job follow that order.
4. Acquire the strongest mode that path will need when it first reaches the resource.
5. Keep transactions short and never wait for user input or remote HTTP calls while holding locks.
6. Index predicates so write statements find and lock target rows efficiently.

Retry the complete transaction

for attempt in 1..max_attempts:
    try:
        begin transaction
        execute every read, validation, lock and write
        commit
        return success
    catch SQLSTATE 40P01 or retryable serialization failure:
        rollback
        sleep using bounded exponential backoff plus jitter

return a controlled retry-exhausted error

Retry from the beginning because the aborted transaction lost all its work and locks. Cap retries, add jitter to avoid synchronized collisions, and make external side effects idempotent. Do not retry every database error. Invalid SQL, violated business constraints, and authentication failures need different handling.

09. Advisory locks

Advisory locks attach application-defined meaning to numeric keys. PostgreSQL coordinates the keys, but it does not know which business resource they represent and does not force every code path to participate.

Keys and lock strengths

A resource can be identified by one signed 64-bit integer or by a pair of signed 32-bit integers. These are separate key spaces. Advisory locks can be exclusive or shared, blocking or try-lock, and session-scoped or transaction-scoped.

BEGIN;

-- Namespace 20 means "monthly billing"; tenant_id is the resource.
SELECT pg_advisory_xact_lock(20, $1);

INSERT INTO invoices (tenant_id, billing_month, amount)
SELECT $1, date_trunc('month', CURRENT_DATE), SUM(unbilled_amount)
FROM usage_events
WHERE tenant_id = $1
  AND billed_at IS NULL;

UPDATE usage_events
SET billed_at = clock_timestamp()
WHERE tenant_id = $1
  AND billed_at IS NULL;

COMMIT;

Session scope versus transaction scope

• Transaction-level locks release automatically on commit or rollback and are safest for database work contained in one transaction.
• Session-level locks survive transaction rollback and remain until matching unlock calls or session end.
• Session-level acquisition is reentrant and stacks. Three successful acquisitions need three matching unlocks.
• Connection pooling makes session ownership easy to misuse because a later request may receive a different connection, while the original pooled connection still owns the lock.

Control evaluation before taking many advisory locks

Do not assume that a LIMIT is evaluated before an advisory-lock function in the select list. Put the limited row selection in a subquery, then lock its output. This prevents acquiring more session locks than the application expects.

SELECT pg_advisory_xact_lock(q.id)
FROM (
    SELECT id
    FROM tenants
    WHERE billing_due
    ORDER BY id
    LIMIT 100
) AS q;

Advisory locks share the server lock-manager memory pool with regular locks. An unbounded high-cardinality lock scheme can exhaust shared lock capacity. Use them for active coordination, not as a permanent registry of every business object.

10. SKIP LOCKED and a PostgreSQL job queue

SKIP LOCKED lets workers ignore rows currently claimed by competitors. It is useful for queue-like workloads because workers need distinct available jobs, not a globally consistent view of all jobs.

Queue schema and claim index

CREATE TABLE jobs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    queue_name text NOT NULL,
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'running', 'succeeded', 'failed')),
    priority integer NOT NULL DEFAULT 0,
    run_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    attempts integer NOT NULL DEFAULT 0,
    max_attempts integer NOT NULL DEFAULT 10,
    locked_at timestamptz,
    locked_by text,
    last_error text,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    finished_at timestamptz,
    CHECK (attempts >= 0),
    CHECK (max_attempts > 0)
);

CREATE INDEX jobs_claim_idx
    ON jobs (queue_name, priority DESC, run_at, id)
    WHERE status = 'pending';

The partial index matches the fixed pending-state predicate and starts with the queue partition. The remaining keys support priority and deterministic tie-breaking. Validate the exact query with EXPLAIN (ANALYZE, BUFFERS) on representative queue volume.

Claim and mark jobs in one statement

WITH picked AS (
    SELECT id
    FROM jobs
    WHERE queue_name = $1
      AND status = 'pending'
      AND run_at <= clock_timestamp()
      AND attempts < max_attempts
    ORDER BY priority DESC, run_at, id
    FOR UPDATE SKIP LOCKED
    LIMIT $2
)
UPDATE jobs AS j
SET status = 'running',
    attempts = j.attempts + 1,
    locked_at = clock_timestamp(),
    locked_by = $3
FROM picked
WHERE j.id = picked.id
RETURNING j.id, j.payload, j.attempts, j.locked_at;

1. The CTE finds eligible rows in deterministic priority order.
2. FOR UPDATE reserves each selected row.
3. SKIP LOCKED moves past rows another worker already reserved.
4. The same SQL statement changes claimed rows to running.
5. RETURNING gives the worker exactly the jobs it claimed.
6. The application commits promptly, then performs long-running work outside that transaction.

Keeping the transaction open while executing a slow job holds row locks, grows contention, and makes worker failure harder to recover. Persist a lease-like claim, commit, then do the work. The row state, not a long-lived database lock, records ownership during processing.

Complete with an ownership check

UPDATE jobs
SET status = 'succeeded',
    finished_at = clock_timestamp(),
    locked_at = NULL,
    locked_by = NULL,
    last_error = NULL
WHERE id = $1
  AND status = 'running'
  AND locked_by = $2
RETURNING id;

Treat zero returned rows as a lost or stale claim. A stronger design gives every claim a unique token and requires that token on completion, preventing a restarted worker with the same name from completing work owned by a newer attempt.

Recover abandoned work

WITH expired AS (
    SELECT id
    FROM jobs
    WHERE status = 'running'
      AND locked_at < clock_timestamp() - INTERVAL '15 minutes'
      AND attempts < max_attempts
    ORDER BY locked_at, id
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE jobs AS j
SET status = 'pending',
    run_at = clock_timestamp() + INTERVAL '30 seconds',
    locked_at = NULL,
    locked_by = NULL,
    last_error = 'worker lease expired'
FROM expired
WHERE j.id = expired.id
RETURNING j.id;

The lease duration must exceed normal work or be renewed safely with a heartbeat and claim token. Jobs at max_attempts should move to a terminal failed state or dead-letter workflow with enough error context for diagnosis.

Delivery is normally at least once

A worker can perform an external side effect and crash before recording success, so the recovered job may run again. A database row lock cannot create exactly-once behavior across PostgreSQL and an unrelated external service. Use idempotency keys, unique constraints, an outbox pattern, or a target API that deduplicates requests.

Fairness, starvation and throughput

• A hot high-priority stream can starve lower-priority jobs unless policy ages or quotas them.
• Large claim batches hold more locks and can make work distribution uneven.
• Small batches reduce lock duration but add more transactions and round trips.
• A deterministic final key such as id makes selection stable among equal priorities.
• Monitor the age of the oldest pending job, not only total queue depth. A flat depth can hide starvation.

11. Diagnose blocking with pg_stat_activity and pg_locks

Start with the waiting session, identify its direct blockers, inspect both transactions, and then decide whether to wait, cancel work, terminate a session, or fix the application.

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.application_name AS blocked_application,
    blocked.wait_event_type,
    blocked.wait_event,
    clock_timestamp() - blocked.query_start AS blocked_for,
    blocked.query AS blocked_query,
    blocker.pid AS blocker_pid,
    blocker.state AS blocker_state,
    clock_timestamp() - blocker.xact_start AS blocker_xact_age,
    blocker.query AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = b.pid
ORDER BY blocked_for DESC;

pg_blocking_pids() understands PostgreSQL's lock conflict and wait-queue rules and is safer than attempting to reconstruct every blocker with a simplistic self-join on pg_locks. An empty array means no lock-manager blocker is currently identified.

SELECT
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    l.waitstart,
    l.relation::regclass AS relation,
    l.page,
    l.tuple,
    l.transactionid,
    l.virtualxid
FROM pg_locks AS l
WHERE l.pid = $1
ORDER BY l.granted, l.locktype, l.mode;

Questions to answer during an incident

1. Which PID is waiting, and for how long?
2. Which direct and indirect sessions form the blocking chain?
3. Is the blocker actively working or idle in transaction?
4. When did the blocker's transaction begin?
5. Which relation and lock mode are involved?
6. Is a DDL statement waiting behind writes while new requests queue behind the DDL?
7. Can the owning application safely cancel or terminate the chosen session?
8. What code path, missing timeout, slow query, or transaction boundary caused the condition?

Canceling a query with pg_cancel_backend(pid) is less disruptive than terminating its session with pg_terminate_backend(pid). Either action can affect users and roll back work. Confirm the target, owner, transaction and business impact before intervening.

Useful operational settings

• Enable log_lock_waits when lock waits need server-log visibility. Messages are tied to deadlock_timeout.
• Keep deadlock_timeout high enough to avoid excessive checks but low enough for the desired diagnosis. Its default is normally suitable before workload-specific tuning.
• Set application-specific lock_timeout and statement_timeout.
• Tag connections with meaningful application_name values.
• Track transaction age and idle-in-transaction sessions continuously.

12. Performance implications

Locks are cheap when uncontended and expensive when they serialize hot work. Performance depends more on lock scope, acquisition order and hold time than on the number of locking keywords.

Lock amplification

A query intended to change ten rows may scan thousands because of a weak predicate or missing index. Depending on the plan and statement, it can spend time evaluating, waiting, or locking much more work than the business action suggests. Use EXPLAIN (ANALYZE, BUFFERS) safely in a representative environment and inspect rows, loops, filters, triggers, and foreign-key work.

Strong-lock queueing

A migration requesting ACCESS EXCLUSIVE can wait behind a long reader. New ordinary reads can then queue behind the waiting migration rather than bypass it forever. A seemingly harmless DDL deployment can therefore create an application-wide pileup. Use a short lock_timeout, understand the exact command's lock level, and retry during a safe window instead of waiting indefinitely.

Locking reads can write

SELECT FOR UPDATE marks tuples as locked and can cause disk writes. It also adds a LockRows operation to the execution plan and prevents a purely index-only result for those locked tuples because PostgreSQL must visit tuple state. Do not add locking clauses to read-only queries "just in case."

13. Practical concurrency patterns

Inventory reservation

1. Use one conditional update that decrements only when inventory is sufficient.
2. Return the new state with RETURNING.
3. Check that exactly one row was returned.
4. Record a unique reservation ID in the same transaction for idempotency.
5. Release or expire reservations through another guarded state transition.

Account transfer

1. Begin one database transaction.
2. Lock all participating accounts in ascending primary-key order.
3. Validate balances after acquiring the locks.
4. Apply both ledger entries and balance changes atomically.
5. Commit, then publish external notifications through an outbox.

One active task per business key

Use a transaction-level advisory lock when the resource is naturally identified by a stable numeric key and all participants share the protocol. Use a unique row or lease table when ownership must remain queryable and survive beyond a transaction or connection.

Optimistic version check

UPDATE documents
SET body = $1,
    version = version + 1,
    updated_at = clock_timestamp()
WHERE id = $2
  AND version = $3
RETURNING id, version;

Zero returned rows means another writer changed the document after it was read. This is useful when users edit for minutes and holding a transaction open would be unacceptable. The application can reload, merge, or report a conflict.

When no row is a natural mutex

If correctness depends on a set of rows that can change shape, manual row locks may miss phantoms or absent rows. Prefer a database constraint if the rule can be expressed declaratively. Otherwise consider SERIALIZABLE isolation and retry serialization failures. Do not invent a random existing row to lock without documenting why every participant uses it.

14. Common mistakes and safer choices

15. Concurrency design and incident checklist

Before shipping a concurrent workflow

1. Write the business invariant in one sentence.
2. Identify whether it concerns one row, many rows, absence, or an abstract resource.
3. Prefer a constraint or one atomic statement when it can enforce the invariant.
4. Choose the weakest sufficient row, table, advisory, or isolation mechanism.
5. Define a global acquisition order for multiple resources.
6. Keep the transaction free of user think time and remote service calls.
7. Add bounded timeouts and handle the resulting SQLSTATEs deliberately.
8. Design whole-transaction retries with idempotency.
9. Index target predicates and foreign-key paths.
10. Load-test with real contention, not only single-session correctness tests.
11. Expose application name, transaction age, queue age and blocker metrics.
12. Document failure recovery, especially for leases and background jobs.

During a blocking incident

1. Capture current pg_stat_activity and blocker chains before state disappears.
2. Find the root blocker, not only the final waiting query.
3. Check transaction age, state, application, user and current or last query.
4. Identify the lock type, relation and requested mode.
5. Check for a waiting schema migration that has become the queue head.
6. Coordinate cancel or termination with the workload owner.
7. Confirm recovery and watch for retried traffic creating another spike.
8. Fix the transaction boundary, query plan, lock order, timeout or deployment process.

16. Practice problems

1. Prevent overselling

Write one statement that reserves three units from product 7 only if at least three remain. It must return the remaining count and report failure through zero returned rows.

UPDATE products
SET stock = stock - 3
WHERE id = 7
  AND stock >= 3
RETURNING id, stock;

2. Limit lock scope in a join

Select order 99 with its customer name, but lock only the order because only its status will change.

SELECT o.id, o.status, c.display_name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.id = 99
FOR UPDATE OF o;

3. Remove a transfer deadlock

Transfers sometimes lock the source first and destination second. Explain the flaw and define one rule that works for transfers in either direction.

4. Build a two-job claim

Claim at most two pending email jobs without waiting for rows claimed by other workers. Persist ownership before returning work.

WITH picked AS (
    SELECT id
    FROM jobs
    WHERE queue_name = 'email'
      AND status = 'pending'
      AND run_at <= clock_timestamp()
    ORDER BY priority DESC, run_at, id
    FOR UPDATE SKIP LOCKED
    LIMIT 2
)
UPDATE jobs AS j
SET status = 'running',
    locked_by = 'worker-7',
    locked_at = clock_timestamp(),
    attempts = attempts + 1
FROM picked
WHERE j.id = picked.id
RETURNING j.id, j.payload;

5. Choose advisory-lock scope

A tenant-scoped report is generated entirely inside one transaction. Should its advisory lock be session-level or transaction-level?

6. Read a blocker chain

Session 501 is waiting, pg_blocking_pids(501) returns {420}, and session 420 is idle in transaction with a transaction age of 18 minutes. What is the likely issue and first safe operational step?

7. Protect an absent value

Two sessions both check that no user has email a@example.com. Why can SELECT ... FOR UPDATE still fail to prevent duplicates?

17. Interview Q&A

Why does PostgreSQL need locks if it has MVCC?

MVCC determines which row versions a statement can see and removes much reader-writer blocking. Locks still coordinate conflicting writes, locking reads, schema changes, object lifetime, uniqueness, foreign keys and application-defined critical sections.

Does SELECT FOR UPDATE block ordinary SELECT?

No. An ordinary query generally reads an MVCC-visible version. FOR UPDATE blocks incompatible row lockers, updates and deletes on the same rows. A strong table lock such as ACCESS EXCLUSIVE can block the ordinary query at the relation level.

What is the difference between FOR UPDATE and FOR NO KEY UPDATE?

FOR UPDATE is strongest and conflicts with every row-lock mode. FOR NO KEY UPDATE is weaker and can coexist with FOR KEY SHARE, making it suitable when non-key attributes change but referenced key identity remains stable.

What is the difference between FOR SHARE and FOR KEY SHARE?

FOR SHARE blocks all updates and deletes through its conflicts with both update-style modes. FOR KEY SHARE mainly prevents deletion and key-changing updates while allowing non-key updates.

What do NOWAIT and SKIP LOCKED do?

NOWAIT fails immediately when a selected row cannot be locked. SKIP LOCKED silently omits such rows. Both apply to the row-lock request, not the required table lock. SKIP LOCKED is appropriate for queue-like consumers, not general consistent reads.

How does PostgreSQL resolve a deadlock?

After waiting for deadlock_timeout, PostgreSQL checks the wait-for graph. If it finds a cycle, it aborts one participant with SQLSTATE 40P01, releasing its locks so others can continue. Applications should prevent cycles through stable lock ordering and retry the whole aborted transaction when safe.

Can row locks be exhausted through max_locks_per_transaction?

Not directly. PostgreSQL stores row-lock state with tuples and transaction metadata, so there is no configured count limit on locked rows. Table and advisory locks use shared lock-manager memory, and large row-locking transactions are still operationally expensive.

Why might a row-lock wait appear as transactionid in pg_locks?

The row's lock state is stored on disk. A waiter commonly waits for the transaction that owns the conflicting tuple state to finish, so pg_locks shows a share request on that transaction ID rather than an obvious held tuple lock.

How do advisory locks differ from row locks?

Row locks are tied to real table rows and are automatically honored by relevant DML. Advisory locks coordinate numeric keys whose meaning the application defines. PostgreSQL manages conflicts but does not force non-participating SQL to respect an advisory lock.

Why prefer transaction-level advisory locks?

They release automatically on commit or rollback, match database transaction semantics, and are less likely to leak through a connection pool. Session locks are useful only when coordination intentionally spans transactions and connection ownership is controlled.

How does SKIP LOCKED enable multiple workers?

Each worker orders eligible rows, attempts to lock them, and skips rows already locked by other workers. An atomic CTE plus UPDATE ... RETURNING persists distinct claims. Short claim transactions, leases, retry limits and idempotent processing complete the design.

Does that queue provide exactly-once execution?

Normally no. A worker can complete an external effect and fail before marking the job successful, causing a retry. The queue is usually at least once. Idempotency keys, deduplication, transactional outbox patterns and guarded state changes control duplicate effects.

What is the most common production lock problem?

Often it is not a sophisticated deadlock. It is a long or idle transaction holding ordinary locks, a hot row serializing writes, or DDL waiting for a strong lock. Transaction age, blocker chains and application context reveal which one is happening.

When is SERIALIZABLE better than manual row locking?

It is useful when an invariant spans a changing predicate or absent rows and no fixed set of rows can be locked safely. PostgreSQL detects dangerous serialization patterns and can abort a transaction, so the application must retry complete transactions.

18. Concise cheat sheet

• Locks normally live until transaction end.
• Only ACCESS EXCLUSIVE blocks a plain table read.
• ROW SHARE and ROW EXCLUSIVE are table-lock names.
• Use OF alias to restrict which join inputs are row-locked.
• NOWAIT and SKIP LOCKED do not bypass the table lock.
• Never hold a transaction open for user input or remote service work.
• Index write and queue predicates to reduce time spent finding target rows.
• Use unique deterministic ordering before locking multiple resources.
• Persist queue claims, commit quickly, recover leases, and make jobs idempotent.

19. Official reference map

These current PostgreSQL manuals are the primary references for the behavior and syntax in this guide.

• Explicit locking: table locks, row locks, deadlocks and advisory locks
• SELECT locking-clause syntax, NOWAIT, SKIP LOCKED and query-shape details
• LOCK TABLE syntax and behavior
• Advisory-lock functions
• The pg_locks system view
• pg_stat_activity and activity monitoring
• System information functions, including pg_blocking_pids
• Lock-management settings, including deadlock_timeout
• Client statement and transaction timeout settings
• Transaction isolation and serialization behavior