could not obtain lock

This error occurs when a query can't acquire the necessary lock, usually due to NOWAIT or lock timeout.

The could not obtain lock error means the required lock couldn't be acquired.

Understanding the Error

ERROR: could not obtain lock on relation "users"

Another transaction holds an incompatible lock and NOWAIT was specified or lock_timeout was exceeded.

Common Causes

1. Using NOWAIT

SQL

-- Transaction A
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- holds lock, doesn't commit

-- Transaction B
SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;  -- Error!

2. Lock Timeout Exceeded

SQL

SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN new_col INTEGER;  -- Error if locked > 5s

3. DDL Waiting for Locks

SQL

-- Long transaction reading table
BEGIN;
SELECT * FROM big_table;
-- ... not committing

-- DDL needs AccessExclusive lock
ALTER TABLE big_table ADD COLUMN x INT;  -- Waits or fails

How to Fix It

Solution 1: Remove NOWAIT (Wait for Lock)

SQL

-- Wait for lock instead of failing immediately
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- Waits if locked

Solution 2: Use SKIP LOCKED

SQL

-- Skip locked rows instead of waiting
SELECT * FROM tasks WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;

Solution 3: Increase Lock Timeout

SQL

-- For session
SET lock_timeout = '30s';

-- For specific statement
SET LOCAL lock_timeout = '60s';
ALTER TABLE users ADD COLUMN status VARCHAR(20);
RESET lock_timeout;

Solution 4: Retry on Lock Failure

JAVASCRIPT

async function withLockRetry(fn, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await fn();
    } catch (error) {
      if (error.code === '55P03' && attempt < maxRetries) {
        await sleep(1000 * attempt);  // Increasing delay
        continue;
      }
      throw error;
    }
  }
}

Solution 5: Find and Kill Blocking Query

SQL

-- Find blocking queries
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocking.state
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.relation = blocking_locks.relation
  AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

-- Cancel blocking query (graceful)
SELECT pg_cancel_backend(blocking_pid);

-- Terminate blocking connection (forceful)
SELECT pg_terminate_backend(blocking_pid);

Solution 6: Use Lower Lock Levels

SQL

-- Use FOR SHARE instead of FOR UPDATE when possible
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- Use FOR KEY SHARE for even less locking
SELECT * FROM users WHERE id = 1 FOR KEY SHARE;

Lock Types

SQL

-- From weakest to strongest:
ACCESS SHARE        -- SELECT
ROW SHARE          -- SELECT FOR UPDATE/SHARE
ROW EXCLUSIVE      -- INSERT, UPDATE, DELETE
SHARE UPDATE EXCLUSIVE -- VACUUM, ANALYZE
SHARE              -- CREATE INDEX (non-concurrent)
SHARE ROW EXCLUSIVE -- CREATE TRIGGER
EXCLUSIVE          -- REFRESH MAT VIEW CONCURRENTLY
ACCESS EXCLUSIVE   -- ALTER TABLE, DROP TABLE

Best Practices

Keep transactions short to minimize lock duration
Use SKIP LOCKED for queue-like patterns
Set appropriate lock_timeout for DDL operations
Monitor long-running transactions that hold locks
Use CONCURRENTLY for index creation when possible