DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 55P03 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 55P03: lock not available

PostgreSQL error 55P03 (lock_not_available) is raised when a transaction attempts to acquire a lock using the NOWAIT option, but the requested resource is already locked by another transaction. Unlike standard lock waits, NOWAIT instructs PostgreSQL to fail immediately rather than queue behind the existing lock holder. This error is most commonly seen in high-concurrency systems using SELECT ... FOR UPDATE NOWAIT or LOCK TABLE ... NOWAIT.

Top 3 Causes

1. Concurrent Transactions Competing for the Same Row

When two or more sessions try to lock the same row simultaneously with NOWAIT, all but the first will immediately receive error 55P03.

-- Session A (succeeds first)
BEGIN;
SELECT * FROM orders WHERE order_id = 101 FOR UPDATE NOWAIT;

-- Session B (fails immediately while Session A holds the lock)
BEGIN;
SELECT * FROM orders WHERE order_id = 101 FOR UPDATE NOWAIT;
-- ERROR: 55P03: could not obtain lock on row in relation "orders"
Enter fullscreen mode Exit fullscreen mode

This is extremely common in order processing or inventory systems where many workers compete for the same records.

2. Long-Running Transactions Holding Locks

A background job or analytics query that holds an open transaction for minutes or hours will block any NOWAIT attempt on the same resource.

-- Check for long-running transactions holding locks
SELECT
    pid,
    usename,
    now() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > INTERVAL '2 minutes'
ORDER BY duration DESC;

-- Terminate a blocking process if necessary
SELECT pg_terminate_backend(<blocking_pid>);
Enter fullscreen mode Exit fullscreen mode

3. DDL Operations Conflicting with DML

DDL statements like ALTER TABLE or TRUNCATE require an AccessExclusiveLock, which conflicts with nearly all other lock modes. If a DML query uses NOWAIT while a DDL operation is in progress (or vice versa), error 55P03 is triggered instantly.

-- Set a lock timeout before running DDL to avoid cascading blocks
SET lock_timeout = '3s';

ALTER TABLE orders ADD COLUMN notes TEXT;

RESET lock_timeout;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Use SKIP LOCKED for queue-style workloads — instead of failing on a locked row, skip it and process the next available one:

-- Worker queue pattern using SKIP LOCKED
BEGIN;

SELECT id, payload
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 5
FOR UPDATE SKIP LOCKED;

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Implement retry logic in PL/pgSQL for cases where NOWAIT is intentional:

DO $$
DECLARE
    acquired BOOLEAN := FALSE;
    attempts  INT     := 0;
BEGIN
    WHILE NOT acquired AND attempts < 5 LOOP
        BEGIN
            PERFORM id FROM inventory
            WHERE product_id = 42
            FOR UPDATE NOWAIT;

            acquired := TRUE;

        EXCEPTION WHEN lock_not_available THEN
            attempts := attempts + 1;
            PERFORM pg_sleep(0.2 * attempts);
        END;
    END LOOP;

    IF NOT acquired THEN
        RAISE EXCEPTION 'Could not acquire lock after % attempts', attempts;
    END IF;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Set lock_timeout at the role level as a safer alternative to NOWAIT:

-- Prefer lock_timeout over NOWAIT for more flexibility
ALTER ROLE app_user SET lock_timeout = '5s';
SHOW lock_timeout;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always configure lock_timeout and enable lock wait logging

Never let connections wait indefinitely for locks in production. Set sensible timeouts and log all lock waits for observability.

-- Enable in postgresql.conf or via ALTER SYSTEM
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

2. Keep transactions short and acquire locks in a consistent order

Long-lived transactions are the root cause of most lock contention. Remove any external I/O, user interaction, or heavy computation from inside transactions. Always lock multiple tables or rows in the same order across all application code paths to prevent deadlocks and reduce 55P03 frequency.

Related Errors

Code Name Relationship
40P01 deadlock_detected Mutual lock waiting; related root cause
57014 query_canceled Triggered when lock_timeout expires
55006 object_in_use Object-level lock conflict during DDL

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)