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
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 NOWAITorLOCK TABLE ... NOWAIThit a row or table another transaction has locked.- A
lock_timeoutexpired, typically on DDL such asALTER 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
-
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 ); -
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;
-
For schema changes, use a short
lock_timeoutdeliberately and retry, so theALTERdoesn't queue behind a long query and block everyone else.SET lock_timeout = '3s'; ALTER TABLE orders ADD COLUMN note text;
-
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
40P01deadlock detected57014canceling statement55006object in use1222SQL Server: Lock request time out period exceeded1205MySQL: Lock wait timeout exceeded3572MySQL: Lock couldn't be acquired with NOWAITORA-00054Oracle: resource busy and acquire with NOWAIT specifiedSQLITE_BUSYSQLite: database is locked
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.
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.