canceling statement due to lock timeout

What it means

To change a table or a row, a statement first needs a lock on it. If another session holds a lock that conflicts, the statement waits. lock_timeout limits that wait: when it runs out, the server cancels the statement with this error. The statement never got to do its work, so nothing changed. The limit applies to each lock the statement asks for, separately.

lock_timeout is off (0) by default, so someone set it, usually on purpose. It’s a safety net for schema changes: an ALTER TABLE waiting for its lock makes every later query on that table wait behind it, so it’s better for the ALTER TABLE to give up quickly than to stall the application. In that case this error means the safety net worked; deal with the blocker and try again.

The SQLSTATE is 55P03 (lock_not_available). NOWAIT uses the same code, without any waiting: could not obtain lock on row in relation "…" or could not obtain lock on relation "…".

Common causes

1. A session left idle in a transaction. Someone ran BEGIN and an UPDATE in a desktop client, or app code opened a transaction and then waited on something slow. Its locks stay until it commits or rolls back.
2. A long-running query. Even a SELECT holds a lock that conflicts with most forms of ALTER TABLE, so a long report blocks a schema change for as long as it runs.
3. Busy rows. Many sessions update the same rows, and each waits for the one before.
4. A very short limit for the amount of traffic on the table.

How to fix it

Find who holds the lock

Run this while the statement is waiting (without a timeout, or with a longer one):

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state,
       now() - xact_start AS in_transaction_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state <> 'idle'
ORDER BY xact_start;

blocked_by lists the sessions holding things up. A blocker in state idle in transaction isn’t running anything; it’s waiting for its client to commit. The query column shows the last statement it ran, which may not be the one that took the lock.

Deal with the blocker

Ask whoever owns it to commit or roll back. If you can’t, end it:

SELECT pg_terminate_backend(<blocking pid>);

That rolls back the blocker’s transaction. pg_cancel_backend() isn’t enough for an idle session: it only cancels a statement that’s running, and an idle transaction keeps its locks.

To stop forgotten transactions holding locks in future, have the server end them:

ALTER ROLE <user> SET idle_in_transaction_session_timeout = '5min';

Run schema changes with a short timeout, and retry

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

If it times out, wait and run it again, from a script that retries a few times. Keep lock_timeout below any statement_timeout, or the statement timeout always fires first. Where PostgreSQL offers a form that takes a weaker lock, such as CREATE INDEX CONCURRENTLY, use it.

Don’t wait at all

For job queues and similar, FOR UPDATE SKIP LOCKED skips rows other sessions have locked, and FOR UPDATE NOWAIT fails at once instead of waiting.

Reproduce it

On PostgreSQL 18.6, session A holds a row lock and sits idle in its transaction:

BEGIN;
UPDATE seo_pgconn.accounts SET balance = balance - 10 WHERE id = 1;
-- (no COMMIT yet)

Session B, with a one-second limit:

SET lock_timeout = '1s';
SET
UPDATE seo_pgconn.accounts SET balance = balance + 10 WHERE id = 1;
ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,36) in relation "accounts"
ALTER TABLE seo_pgconn.accounts ADD COLUMN note text;
ERROR:  canceling statement due to lock timeout
SELECT * FROM seo_pgconn.accounts WHERE id = 1 FOR UPDATE NOWAIT;
ERROR:  could not obtain lock on row in relation "accounts"

With \set VERBOSITY verbose, the code shows: ERROR: 55P03: canceling statement due to lock timeout. PostgreSQL 14.24 gives the same message.

While an ALTER TABLE was waiting (with lock_timeout = '3s'), pg_stat_activity showed the chain:

  pid   | blocked_by |        state        |                          query                          
--------+------------+---------------------+---------------------------------------------------------
 276585 | {}         | idle in transaction | UPDATE seo_pgconn.accounts SET balance = balance - 10 W
 276590 | {276585}   | active              | ALTER TABLE seo_pgconn.accounts ADD COLUMN note text;
(2 rows)

Without a lock timeout on the ALTER TABLE, a plain SELECT count(*) FROM seo_pgconn.accounts from a third session queued behind it, and failed on its own two-second statement timeout:

ERROR:  canceling statement due to statement timeout

In Inlet

Inlet’s Activity monitor shows sessions, the locks they hold, and which query blocks which, and it can cancel or terminate a session. Its structure editor shows the DDL before you run it and warns when a change rewrites or scans the table.