PostgreSQL

Postgres "LOCK TABLE" Deadlock Waiting Forever

An explicit LOCK TABLE statement that just hangs, rather than producing Postgres's usual automatic deadlock error, usually means it's waiting on a lock held by a long-running or idle transaction — not a true circular deadlock, which Postgres actually does detect and resolve automatically within a second.

The Problem

A LOCK TABLE statement, or any statement requiring a strong table-level lock, sits waiting indefinitely with no error and no completion:

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
-- hangs with no output, no error

If it were a genuine circular deadlock between two transactions each waiting on the other, Postgres would detect and report it within its deadlock detection timeout, typically producing:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for AccessExclusiveLock on relation 16401; blocked by process 5678.

The fact that you're not seeing this error, just an indefinite hang, is actually the key clue: it's not a deadlock in the technical sense, it's simple lock contention with no circular dependency for Postgres to detect.

Why It Happens

A hanging (not erroring) lock wait means some other session is holding a conflicting lock and simply hasn't released it — often because that other transaction is still open, sometimes because it's idle and forgotten rather than actively doing work. Common causes:

  • An application connection began a transaction, ran a query touching the table, and never committed or rolled back — the connection is sitting idle in a transaction, silently holding a lock that blocks everything else needing that table
  • A long-running report or batch job holds a lock for its entire duration, and anything else needing a conflicting lock on the same table simply queues up behind it until that job finishes
  • A previous session crashed or was killed at the client level without properly closing its database connection, leaving Postgres still tracking it as an active transaction holding a lock
  • Connection pooling misconfiguration where a connection is returned to the pool without the application ever explicitly committing or rolling back the transaction it started

The Fix

First, identify what's actually blocking your lock request. Query for blocking sessions directly rather than guessing:

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

This surfaces exactly which process (PID) is holding the lock your statement is waiting for, along with what query that blocking session was last running — often immediately revealing an idle transaction that was simply never closed.

Check specifically for connections idle in a transaction, since these are the most common silent culprit:

SELECT pid, state, query, now() - xact_start AS transaction_duration
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY transaction_duration DESC;

A connection showing idle in transaction for an unusually long duration is almost always the cause of an unexpected lock hang — it began a transaction, did something, and simply never committed or rolled back.

Once identified, you can terminate the blocking connection directly if it's safe to do so (confirm it isn't legitimately mid-way through important, uncommitted work first):

SELECT pg_terminate_backend(<blocking_pid>);

To prevent this recurring, set a statement timeout and an idle-in-transaction timeout at the database or role level, so connections can't hold locks indefinitely by accident:

ALTER DATABASE mydb SET idle_in_transaction_session_timeout = '5min';

This automatically terminates any connection that's been idle in a transaction for longer than the specified duration, preventing exactly this kind of accidental long-held lock from ever accumulating in the first place.

Still Not Working?

If the blocking session turns out to be another legitimate, actively-running long process rather than an accidentally idle one, the real fix is scheduling conflicting operations (like a maintenance job requiring an exclusive lock) to avoid overlapping with normal application traffic, rather than terminating a genuinely necessary in-progress operation. Reviewing pg_stat_activity's query_start alongside the blocking query's expected duration helps distinguish "this is taking unexpectedly long and something's wrong" from "this is just a big job that legitimately needs this much time."

You might also like