Skip to content
Tutorials Center
My learning

Topics

Lesson 47 of 90 ยท Transactions, MVCC and Concurrency

Locks, Row Locking and Deadlocks

Learn PostgreSQL locking: table lock modes, SELECT FOR UPDATE, NOWAIT, SKIP LOCKED job queues, advisory locks, deadlocks and how to inspect pg_locks.

MVCC lets readers and writers work side by side, but some situations still need one transaction to wait for another: two sessions changing the same row, a migration that needs to rewrite a table, a job that must be picked up by exactly one worker. PostgreSQL handles these with locks, and knowing how they behave is the difference between a smooth deployment and a production outage.

This lesson covers the three kinds of locks you will meet (table, row and advisory), the SELECT ... FOR UPDATE family including NOWAIT and SKIP LOCKED, how deadlocks arise and get resolved, and how to find out who is blocking whom. Locking needs several simultaneous sessions, so the server commands are read-only; the live examples simulate the ideas in SQL.

Three kinds of locks

Table locks Eight modes. Taken by every statement, automatically.
Row locks Taken by writes and by SELECT FOR UPDATE.
Advisory locks Names you invent, enforced by the server.

Locks are released at the end of the transaction, except session-level advisory locks.

1 / 2

Table-level locks

Every statement takes a lock on each table it touches, and PostgreSQL has eight modes, from weakest to strongest:

Mode Typically taken by
ACCESS SHARE SELECT
ROW SHARE SELECT ... FOR UPDATE / FOR SHARE
ROW EXCLUSIVE INSERT, UPDATE, DELETE, MERGE
SHARE UPDATE EXCLUSIVE VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY
SHARE CREATE INDEX (not concurrent)
SHARE ROW EXCLUSIVE CREATE TRIGGER, some ALTER TABLE forms
EXCLUSIVE REFRESH MATERIALIZED VIEW CONCURRENTLY
ACCESS EXCLUSIVE DROP TABLE, TRUNCATE, VACUUM FULL, most ALTER TABLE, plain LOCK TABLE

You do not memorize the conflict matrix. What matters in practice:

  • SELECT, INSERT, UPDATE and DELETE do not conflict with each other at table level. Ordinary traffic never queues behind other ordinary traffic here.
  • ACCESS EXCLUSIVE conflicts with everything, including plain SELECT. A migration that takes it blocks all reads and writes of the table until it finishes.
  • A plain (non-concurrent) CREATE INDEX takes SHARE, which allows reads but blocks writes. That is why you use CREATE INDEX CONCURRENTLY on busy tables.

The lock queue

Locks are granted in order. Suppose a long report is running a SELECT on orders, and you run ALTER TABLE orders ADD COLUMN note text;, which needs ACCESS EXCLUSIVE. The ALTER waits for the report to finish. Meanwhile, every new query on orders queues behind the waiting ALTER, even harmless SELECTs. A migration that "does nothing" for ten minutes can freeze the whole application.

Protect migrations with a lock timeout, so they give up instead of blocking everyone:

SQL
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;    -- fails fast with an error if it cannot get the lock

and retry later. The details of safe migrations are in Schema Changes and Migrations.

Row-level locks

Row locks are what stop two sessions changing the same row at once. UPDATE and DELETE take them automatically on the rows they touch, and a second writer waits until the first transaction commits or rolls back (the behavior at each isolation level is in Isolation Levels and Anomalies).

You can also lock rows without changing them, to make a decision on data that nobody else may modify in the meantime:

SQL
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- lock the row
-- compute in the application, then
UPDATE accounts SET balance = 400 WHERE id = 1;
COMMIT;

There are four strengths:

Clause Blocks Used for
FOR UPDATE every other row lock and every change rows you intend to change or delete
FOR NO KEY UPDATE other writers, but allows FOR KEY SHARE an update that does not change key columns (what a normal UPDATE takes)
FOR SHARE writers, but allows other FOR SHARE "this row must not change while I look at related data"
FOR KEY SHARE only deletes and key changes what foreign key checks take on the parent row

The weaker modes exist so that inserting a child row (which needs the parent row to stay in existence) does not block updates of the parent's non-key columns.

A few facts that surprise people:

  • Row locks are stored in the row, in the xmax field described in the MVCC lesson, not in a shared-memory table. That is why you can lock millions of rows without running out of memory, but also why SELECT ... FOR UPDATE writes to the table pages.
  • A locked row can still be read by ordinary SELECT statements; they just see the version they are allowed to see.
  • Lock only what you need: SELECT ... FOR UPDATE on a big range holds up everyone else. Add ORDER BY and LIMIT, or filter on an indexed column.
  • In a join, FOR UPDATE OF accounts locks only rows from the named table.

NOWAIT and SKIP LOCKED

By default, a statement that hits a locked row waits (forever, unless lock_timeout is set). Two options change that:

SQL
-- Give up immediately if any chosen row is locked:
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR:  could not obtain lock on row in relation "accounts"   (SQLSTATE 55P03)

-- Quietly leave out rows that are locked by someone else:
SELECT * FROM jobs WHERE status = 'queued' FOR UPDATE SKIP LOCKED LIMIT 5;

NOWAIT suits interactive requests ("someone else is editing this record" is better than a hung page). SKIP LOCKED suits queues.

Job queues with SKIP LOCKED

Several workers must each claim different jobs, without a separate coordination service. With SKIP LOCKED, each worker takes the first job nobody else has claimed:

SQL
WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'running', worker = 'worker-7', started_at = now()
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.id, jobs.payload;

If worker A has locked job 1 inside its transaction, worker B's inner SELECT skips job 1 and locks job 2 instead. No two workers get the same job, and nobody waits. After processing, the worker marks the job done (or puts it back as queued on failure) and commits. If a worker crashes, its transaction rolls back and the job becomes available again automatically, provided you keep the claim and the work in one transaction, or reset stale running rows with a timeout.

The caveat: SKIP LOCKED deliberately gives an inconsistent view of the table, so use it for queue-like access, never for reports. The complete build is in the job queue project.

The in-browser engine has only one session, so a real skip cannot happen. This simulation claims jobs with the same shape of statement; marking the job as running plays the role of the lock, and the second worker's query skips it because it is no longer queued:

SQL Workers claim distinct jobs (simulated)

Press Run (or Ctrl + Enter) to see the result here.

Each worker received a different job (1, 2 and 3), and job 4 is still queued. In PostgreSQL the FOR UPDATE SKIP LOCKED clause gives you the same result when workers run at the same instant, which is where the plain subquery above would fail: two workers could pick the same row before either one updated it.

Advisory locks

Sometimes the thing you want to protect is not a row but an idea: "only one process may run the nightly report", "only one worker may refresh the cache for customer 42". Advisory locks let you lock an arbitrary number (or a pair of numbers) that you choose. PostgreSQL enforces them but gives them no meaning:

SQL
-- Session-level: held until you unlock or disconnect
SELECT pg_try_advisory_lock(42);        -- true if acquired, false if somebody holds it
SELECT pg_advisory_unlock(42);

-- Transaction-level: released automatically at COMMIT or ROLLBACK
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('nightly-report'));   -- waits until free
-- ... do the work ...
COMMIT;

Rules of thumb:

  • Prefer the _xact_ variants: they cannot be leaked, because ending the transaction releases them.
  • Session-level locks belong to the connection. Behind a transaction-mode pooler such as PgBouncer, the next transaction may run on a different connection, so session-level locks are unsafe there. See Connection Pooling.
  • Use pg_try_advisory_lock when you would rather skip a job than wait for it.
  • Make sure different parts of your application use different key spaces (for example pg_advisory_xact_lock(class_id, object_id) with the two-integer form).

The following simulation uses a table with a primary key as the "lock name". Inserting the name succeeds only for the first session, which is exactly the contract of pg_try_advisory_lock:

SQL Try-lock with a primary key (simulated)

Press Run (or Ctrl + Enter) to see the result here.

Only the first and last INSERT return a row. The difference from a real advisory lock: nothing releases this row if the session disappears, which is the very problem PostgreSQL's connection-bound locks solve for you.

Deadlocks

A deadlock is a cycle of waiting: each transaction holds something another one needs.

sequenceDiagram
  participant A as Transaction A
  participant B as Transaction B
  A->>A: UPDATE row 1 (locks row 1)
  B->>B: UPDATE row 2 (locks row 2)
  A->>B: UPDATE row 2 (waits for B)
  B->>A: UPDATE row 1 (waits for A)
  Note over A,B: Cycle! After deadlock_timeout PostgreSQL aborts one

Neither transaction can finish, so the server breaks the cycle by cancelling one of them.

PostgreSQL does not check for deadlocks all the time. After a transaction has waited for a lock for deadlock_timeout (1 second by default), it runs the detector. If it finds a cycle, it aborts one participant:

TEXT
ERROR:  deadlock detected
DETAIL:  Process 18211 waits for ShareLock on transaction 9120; blocked by process 18305.
         Process 18305 waits for ShareLock on transaction 9118; blocked by process 18211.
HINT:  See server log for query details.

The error code is 40P01, and the other transaction continues. Treat it like a serialization failure: roll back and retry the whole transaction.

The detector finds cycles by walking a wait-for graph: an arrow from each waiting transaction to the one it waits for. Here is that idea in SQL, with a recursive query that finds the cycle in a small graph. T4 is stuck behind the cycle but is not part of it:

SQL Find a deadlock cycle in a wait-for graph

Press Run (or Ctrl + Enter) to see the result here.

The result lists T1, T2 and T3 (each shown with its own rotation of the same cycle). T4 and T5 wait for the cycle but are not members, so a real server would abort only one of T1, T2 or T3, and T4 and T5 would then proceed.

Avoiding deadlocks

  • Lock in a consistent order. If every transaction updates accounts in ascending id order, no cycle can form. When you must lock several rows, do it up front: SELECT ... FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE.
  • Keep transactions short and avoid user interaction or network calls while holding locks.
  • Foreign keys can create hidden lock edges (a child insert takes FOR KEY SHARE on the parent), so deadlocks can appear in code that never mentions FOR UPDATE.
  • Turn on log_lock_waits = on to log any wait longer than deadlock_timeout, which helps to find slow lock chains before they become deadlocks.

Looking at locks: pg_locks

pg_locks lists every lock held or requested. The columns you need are locktype, relation, mode and granted. Joined with pg_stat_activity, it shows who is waiting:

SQL
SELECT a.pid, a.state, a.wait_event_type, a.wait_event,
       pg_blocking_pids(a.pid) AS blocked_by,
       left(a.query, 50) AS query
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;
TEXT
  pid  | state  | wait_event_type | wait_event | blocked_by |             query
-------+--------+-----------------+------------+------------+-------------------------------
 18305 | active | Lock            | relation   | {18211}    | ALTER TABLE orders ADD COLUMN
 18390 | active | Lock            | relation   | {18305}    | SELECT * FROM orders WHERE id

This is the lock queue from earlier: the ALTER TABLE waits for session 18211, and an innocent SELECT waits behind the ALTER. The fix is to look at the root of the chain (18211, whose own state you should check with state, xact_start, query in pg_stat_activity) and, if necessary, end it with SELECT pg_cancel_backend(18211) (cancels the query) or pg_terminate_backend(18211) (closes the connection).

To see the table locks that one transaction holds, filter by pid:

SQL
SELECT locktype, relation::regclass AS relation, mode, granted
FROM pg_locks
WHERE pid = 18211;

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which table lock mode conflicts with a plain SELECT?

    Only ACCESS EXCLUSIVE conflicts with ACCESS SHARE, the lock a SELECT takes. Statements like DROP TABLE, TRUNCATE and most ALTER TABLE forms use it.
  2. 2. Why can a slow migration freeze an application even though it is waiting for a lock?

    Locks are granted in order. Once the ALTER TABLE is waiting, later queries on that table wait behind it. A lock_timeout makes the migration give up instead.
  3. 3. What does FOR UPDATE SKIP LOCKED do?

    SKIP LOCKED leaves out rows currently locked by others. That is how several workers can each claim different jobs without waiting for one another.
  4. 4. What is the safest way to avoid deadlocks between transactions that update several rows?

    A deadlock needs a cycle of waits. If every transaction acquires locks in the same order, no cycle can form.
  5. 5. Why prefer pg_advisory_xact_lock over pg_advisory_lock?

    Transaction-level advisory locks end with the transaction. Session-level ones stay until unlocked or disconnected, and they misbehave behind transaction-mode connection poolers.

Summary

  • Ordinary SELECT, INSERT, UPDATE and DELETE do not block each other at table level; ACCESS EXCLUSIVE (most ALTER TABLE, TRUNCATE, DROP) blocks everything, and waiting requests form a queue that blocks later queries too.
  • Use lock_timeout for migrations, and the CONCURRENTLY forms for indexes.
  • SELECT ... FOR UPDATE (and the weaker NO KEY UPDATE, SHARE, KEY SHARE) lock rows you read; NOWAIT fails fast and SKIP LOCKED ignores locked rows, which is the basis of database job queues.
  • Advisory locks protect names you choose; prefer the transaction-level versions.
  • Deadlocks are cycles of waits; PostgreSQL aborts one participant (SQLSTATE 40P01), so retry, and avoid them by locking in a consistent order.
  • Use pg_stat_activity, pg_blocking_pids() and pg_locks to find the root of a blocking chain.

Next, see how PostgreSQL cleans up all the versions and dead rows these transactions leave behind in VACUUM, Autovacuum and Bloat.