DBAtlas · Database operations atlas

Apply DDL safely with lock_timeout and retries (avoid lock queues)

PostgreSQLSessions

Command

-- Single attempt: give up if the lock is not acquired within 3 s instead of queueing and blocking traffic
SET lock_timeout = '3s';
ALTER TABLE customer ADD COLUMN IF NOT EXISTS segment text;
RESET lock_timeout;

-- Automatic retry: up to 5 attempts, 3 s apart
DO $$
DECLARE
  attempt int := 0;
BEGIN
  LOOP
    attempt := attempt + 1;
    BEGIN
      SET LOCAL lock_timeout = '2s';
      ALTER TABLE customer ADD COLUMN IF NOT EXISTS segment text;
      RAISE NOTICE 'OK, attempt %', attempt;
      EXIT;
    EXCEPTION WHEN lock_not_available THEN
      RAISE NOTICE 'lock not acquired, attempt %', attempt;
      IF attempt >= 5 THEN RAISE; END IF;
      PERFORM pg_sleep(3);
    END;
  END LOOP;
END $$;

What it does

Keeps a DDL that needs ACCESS EXCLUSIVE from waiting indefinitely when it cannot get the lock, and from blocking every query that queues up behind it (lock queue); retries at short intervals.

When to use it

When applying quick but heavily locking changes such as ALTER TABLE, CREATE/DROP TRIGGER, ATTACH PARTITION or RENAME on busy tables in production.

Caution

Without lock_timeout, an ALTER TABLE queued behind a single long SELECT also makes every subsequent read/write wait and can exhaust the connection pool within minutes. lock_timeout only limits the lock wait; it does not limit the runtime of DDL that rewrites the table (e.g. changing a column type). The DO block is a single transaction and holds a snapshot across attempts; for long retry scenarios prefer an external shell loop.

Open in DBAtlas →

Related