PostgreSQL Shared vs Exclusive Lock Debugger

Threat/Impact Level: CRITICAL | Exploitability/Downtime Risk: HIGH | Time to Fix: 15–30 mins

TL;DR

• What broke: A transaction holding a ShareLock (e.g., from a SELECT FOR SHARE, FK check, or CREATE INDEX CONCURRENTLY) is blocked by — or directly conflicts with — another transaction demanding an ExclusiveLock or AccessExclusiveLock (e.g., ALTER TABLE, TRUNCATE, LOCK TABLE, or a long-running UPDATE).
• How to fix it: Identify the blocking PID via pg_locks + pg_stat_activity, kill or wait out the offending transaction, then restructure your migration and application lock acquisition order to prevent re-occurrence.
• Use our Client-Side Sandbox above to paste your migration SQL or ORM-generated queries and auto-refactor the lock acquisition sequence.

ERROR:  deadlock detected
DETAIL:  Process 47382 waits for ShareLock on transaction 9201; blocked by process 47401.
         Process 47401 waits for ExclusiveLock on relation 16423 of database 16384; blocked by process 47382.
HINT:  See server log for query details.

ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,42) in relation "orders"
DETAIL:  shared lock conflicted with exclusive lock on relation "orders"

How to Fix It (The Solution)

Step 1: Identify the Blocker Right Now

SELECT
  blocked.pid,
  blocked.query AS blocked_query,
  blocked.wait_event_type,
  blocked.wait_event,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocking.state AS blocking_state,
  now() - blocking.query_start AS blocking_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

If the blocker is an idle-in-transaction session or a runaway analytics query:

SELECT pg_terminate_backend(<blocking_pid>);

Basic Fix: Set `lock_timeout` and `statement_timeout`

Never run DDL or long transactions without a timeout. This prevents indefinite blocking:

- ALTER TABLE orders ADD COLUMN metadata jsonb;

+ SET lock_timeout = '2s';
+ SET statement_timeout = '30s';
+ ALTER TABLE orders ADD COLUMN metadata jsonb;

If the lock cannot be acquired within 2 seconds, the statement fails fast instead of queuing and starving the connection pool.

Enterprise Best Practice: Zero-Downtime DDL with `pg_repack` or `CONCURRENTLY`

For index creation — never block reads:

- CREATE INDEX idx_orders_user_id ON orders(user_id);

+ CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

For ALTER TABLE on large tables — use pg_repack or a multi-phase shadow-table migration instead of direct DDL.

For FK constraints — add them as NOT VALID first, then validate separately:

- ALTER TABLE order_items
-   ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id);

+ -- Phase 1: Add constraint without full table scan (takes ShareRowExclusiveLock briefly)
+ ALTER TABLE order_items
+   ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id)
+   NOT VALID;
+
+ -- Phase 2: Validate in a separate, low-priority transaction
+ ALTER TABLE order_items VALIDATE CONSTRAINT fk_order;

For application-level lock ordering — always acquire locks in a consistent global order to prevent deadlocks:

- -- Transaction A: locks accounts then transfers
- BEGIN;
- SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
- SELECT * FROM transfers WHERE id = 99 FOR UPDATE;
- COMMIT;
-
- -- Transaction B: locks transfers then accounts (DEADLOCK)
- BEGIN;
- SELECT * FROM transfers WHERE id = 99 FOR UPDATE;
- SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
- COMMIT;

+ -- Both transactions acquire locks in the same order (accounts → transfers)
+ BEGIN;
+ SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
+ SELECT * FROM transfers WHERE id = 99 FOR UPDATE;
+ COMMIT;

💡 Tired of pasting proprietary infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.

(For CI/CD pipeline automation, install via npm i -g stackengine).

# Install
npm install -g squawk-cli

# Lint a migration file
squawk migration_20240801_add_metadata.sql

# Example output:
# migration.sql:3:1: warning: ban-drop-column
# migration.sql:7:1: error: require-concurrent-index-creation

# .github/workflows/db-lint.yml
- name: Lint PostgreSQL Migrations
  run: squawk migrations/*.sql

ALTER ROLE app_user SET lock_timeout = '3s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_user SET statement_timeout = '60s';

-- Prometheus-compatible query for pg_exporter custom metrics
SELECT count(*) AS lock_waiters
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND state = 'active';

package db.migrations

deny[msg] {
  input.sql_statements[_] == regex.match(`(?i)^\s*ALTER TABLE`, input.sql_statements[_])
  not regex.match(`(?i)lock_timeout`, input.preamble)
  msg := "ALTER TABLE requires SET lock_timeout in preamble. Use pg_repack or phased migration."
}