Postgres Lock Conflict Checker

Will my ALTER TABLE wait behind a long report query? Can two VACUUMs run on the same table? Does CREATE INDEX block writes? PostgreSQL answers all of these with one table of eight lock modes, but nobody remembers it. Pick the two statements (or explicit LOCK TABLE modes) that will run in parallel and this checker tells you whether they conflict, marks the pair in the full conflict matrix, and generates a two-session reproduction script plus the pg_locks and pg_blocking_pids queries to find the blocker on a real server. Everything runs in your browser; nothing is uploaded.

Session 1 runs

Session 2 runs

Table namelock_timeout

Verdict

BLOCKS: "ALTER TABLE (most forms) / DROP TABLE / TRUNCATE" (ACCESS EXCLUSIVE) and "SELECT (plain read)" (ACCESS SHARE) conflict on table orders.

Whichever session runs second waits until the first transaction commits or rolls back. If the second one is an ACCESS EXCLUSIVE request (DDL), every new query on orders queues behind it as well, even plain SELECTs, because lock requests are granted in order.

Lock modes taken by each statement

Session 1: ALTER TABLE (most forms) / DROP TABLE / TRUNCATE
  lock mode: ACCESS EXCLUSIVE (AccessExclusiveLock in pg_locks)
  ADD COLUMN, DROP COLUMN, SET DATA TYPE, RENAME, SET NOT NULL, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW (non-concurrent) and LOCK TABLE default. Blocks even SELECT. Always set lock_timeout first.

Session 2: SELECT (plain read)
  lock mode: ACCESS SHARE (AccessShareLock in pg_locks)
  Any query that only reads a table. Blocked only by ACCESS EXCLUSIVE, so ordinary DML never blocks readers.

Conflict matrix (your pair marked with [ ])

Requested \ Held | AccessShare | RowShare | RowExcl | ShareUpdExcl | Share | ShareRowExcl | Excl | AccessExcl
-----------------+-------------+----------+---------+--------------+-------+--------------+------+-----------
AccessShare      |  .          |  .       |  .      |  .           |  .    |  .           |  .   | [X]       
RowShare         |  .          |  .       |  .      |  .           |  .    |  .           |  X   |  X        
RowExcl          |  .          |  .       |  .      |  .           |  X    |  X           |  X   |  X        
ShareUpdExcl     |  .          |  .       |  .      |  X           |  X    |  X           |  X   |  X        
Share            |  .          |  .       |  X      |  X           |  .    |  X           |  X   |  X        
ShareRowExcl     |  .          |  .       |  X      |  X           |  X    |  X           |  X   |  X        
Excl             |  .          |  X       |  X      |  X           |  X    |  X           |  X   |  X        
AccessExcl       | [X]         |  X       |  X      |  X           |  X    |  X           |  X   |  X        

X = conflict (the requester waits), . = compatible. Your selection is marked with [ ].
Source: PostgreSQL documentation, Table 13.2 Conflicting Lock Modes.

Two-session reproduction script

-- Session 1 (leave the transaction open)
BEGIN;
ALTER TABLE orders ADD COLUMN note text;

-- Session 2 (will wait)
SET lock_timeout = '3s';  -- fail fast instead of queueing forever
BEGIN;
SELECT * FROM orders WHERE customer_id = 42;
COMMIT;

-- Session 1
COMMIT;

Diagnostic SQL: pg_locks and blocking PIDs

-- Who holds or waits for locks on this table right now?
SELECT l.pid,
       l.mode,
       l.granted,
       a.state,
       now() - a.xact_start AS xact_age,
       left(a.query, 80) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'orders'::regclass
ORDER BY l.granted DESC, a.xact_start;

-- Which session is blocking which?
SELECT w.pid AS waiting_pid,
       left(w.query, 60) AS waiting_query,
       b.pid AS blocking_pid,
       left(b.query, 60) AS blocking_query,
       b.state AS blocking_state
FROM pg_stat_activity w
JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid) ON true
JOIN pg_stat_activity b ON b.pid = bp.pid
WHERE cardinality(pg_blocking_pids(w.pid)) > 0;

-- Cancel the blocker's current statement, or terminate its backend
-- SELECT pg_cancel_backend(<blocking_pid>);
-- SELECT pg_terminate_backend(<blocking_pid>);

Do more than postgres lock conflict checker — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Choose what Session 1 and Session 2 will run. For an explicit LOCK TABLE, pick the exact lock mode in the extra selector.
Read the verdict: BLOCKS means the second session waits until the first transaction ends; COMPATIBLE means both proceed. The pair is marked in the matrix.
Copy the two-session script to reproduce the wait locally, and the diagnostic SQL to inspect pg_locks and blocking PIDs in production.

Frequently asked questions

Which PostgreSQL statements block SELECT?

Only statements that take an ACCESS EXCLUSIVE lock block a plain SELECT: most forms of ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX (non-concurrent), CLUSTER, VACUUM FULL, a non-concurrent REFRESH MATERIALIZED VIEW, and LOCK TABLE without a mode. INSERT, UPDATE, DELETE, VACUUM, ANALYZE, CREATE INDEX (with or without CONCURRENTLY) and CREATE TRIGGER all let readers continue. The dangerous case is the queue: if an ALTER TABLE is itself waiting behind a long query, every new SELECT queues behind the ALTER, so a short DDL can freeze an application. Always run DDL with a lock_timeout so it fails fast instead of queueing.

Can two VACUUM or CREATE INDEX CONCURRENTLY commands run on the same table at once?

No. VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY and several light ALTER TABLE forms take SHARE UPDATE EXCLUSIVE, which conflicts with itself. The second command waits for the first to finish, although reads and writes from other sessions continue normally. Autovacuum uses the same mode, so a manual VACUUM can also wait on an autovacuum worker, and an autovacuum worker will cancel itself when it blocks a conflicting DDL request unless it is a wraparound-prevention run.

How do I find which session is holding the lock that blocks my query?

Call pg_blocking_pids(<pid>) on the waiting backend, or use the generated diagnostic query, which joins pg_stat_activity to that function and shows the blocking PID, its state and its current query. A blocker in state 'idle in transaction' is the usual culprit: an application opened a transaction, ran a statement and never committed. You can end it with pg_cancel_backend or pg_terminate_backend. Chat2DB shows the same information in a session list and lets you run these queries against any PostgreSQL server: download it at https://chat2db.ai/download or open https://app.chat2db.ai.