// PostgreSQL error reference

PostgreSQL 55P03: lock not available

A lock request gave up instead of waiting: NOWAIT was used, or lock_timeout expired while another session held a conflicting lock.

SQLSTATE
55P03
Name
lock_not_available
Category
Object Not In Prerequisite State
Error message
could not obtain lock on row in relation "orders"

What it means

Normally PostgreSQL waits as long as it takes for a lock. 55P03 means the statement was told not to: SELECT ... FOR UPDATE NOWAIT or LOCK TABLE ... NOWAIT fail immediately if the lock is taken, and when lock_timeout is set any statement fails once it has waited that long. A lock timeout reports canceling statement due to lock timeout with the same code.

The other session holding the lock is not affected. Inside a transaction block, the failed statement aborts the transaction.

Common causes

  • FOR UPDATE NOWAIT, FOR SHARE NOWAIT or LOCK TABLE ... NOWAIT hit a row or table another transaction has locked.
  • A lock_timeout expired, typically on DDL such as ALTER TABLE, which needs an exclusive lock and waits behind long-running queries.
  • A long transaction or a session left idle in a transaction holding locks.

How to fix it

  1. Find the session that holds the lock.

    SELECT pid, usename, state, xact_start, query
    FROM pg_stat_activity
    WHERE pid IN (
        SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity
    );
  2. For job queues, skip locked rows instead of failing.

    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY id
    LIMIT 10
    FOR UPDATE SKIP LOCKED;
  3. For schema changes, use a short lock_timeout deliberately and retry, so the ALTER doesn't queue behind a long query and block everyone else.

    SET lock_timeout = '3s';
    ALTER TABLE orders ADD COLUMN note text;
  4. End or shorten the blocking transaction; if it is abandoned, terminate it.

    SELECT pg_terminate_backend(12345);  -- the blocking pid

Reproduce it safely

Run in two connections; end session 1 with ROLLBACK afterwards.

-- Session 1:
CREATE TABLE q (id int PRIMARY KEY);
INSERT INTO q VALUES (1);
BEGIN; SELECT * FROM q WHERE id = 1 FOR UPDATE;
-- Session 2:
SELECT * FROM q WHERE id = 1 FOR UPDATE NOWAIT;  -- ERROR: could not obtain lock on row in relation "q"

Related errors

More object not in prerequisite state errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.