Finding queries blocked by locks

What you see

A query that normally takes milliseconds hangs until it times out. Inserts or updates on one table stall while the rest of the database is fine. A migration "never finishes". Through the Data API, requests for one project hang or fail with canceling statement due to statement timeout or PGRST003.

Why it happens

Postgres uses locks to keep concurrent changes safe. A row being updated is locked until its transaction ends, and DDL like ALTER TABLE needs a lock on the whole table. A session that has to wait for a lock simply sits there.

Typical blockers:

• A transaction left open: a script that ran begin and crashed, or a psql session at an open BEGIN prompt. These show as idle in transaction.
• A long-running query holding locks while a migration waits behind it. Everything else then queues behind the migration.
• Two requests updating the same rows in different orders (this ends in a deadlock error rather than a hang).

How to fix it

1. List blocked sessions and what blocks them, in this project's database:

   select blocked.pid                    as blocked_pid,
          blocked.usename                as blocked_user,
          now() - blocked.query_start    as waiting_for,
          left(blocked.query, 80)        as blocked_query,
          blocker.pid                    as blocking_pid,
          blocker.usename                as blocking_user,
          blocker.state                  as blocking_state,
          now() - blocker.xact_start     as blocker_transaction_age,
          left(blocker.query, 80)        as blocking_query
   from pg_stat_activity blocked
   cross join lateral unnest(pg_blocking_pids(blocked.pid)) as b(pid)
   join pg_stat_activity blocker on blocker.pid = b.pid
   where blocked.datname = current_database()
   order by waiting_for desc;
   

   For an idle in transaction blocker, blocking_query is the last statement it ran, not one that's still running.

2. See which locks are held on a table, if you know which one is stuck:

   select l.pid, l.mode, l.granted, a.state, left(a.query, 80) as query
   from pg_locks l
   join pg_stat_activity a on a.pid = l.pid
   where l.relation = 'public.bookings'::regclass
   order by l.granted desc;
   

3. End the blocker. Try cancelling its current statement first, then end the session if it's idle in a transaction:

   select pg_cancel_backend(23456);
   select pg_terminate_backend(23456);
   

   Ending a session rolls back its open transaction. The postgres role can end its own sessions (Studio, the SSH tunnel, cron jobs). If Postgres refuses with a permission error, the blocker belongs to another role, such as authenticator for the Data API; it ends when its statement finishes or times out.

4. Stop it happening again:

   • Give migrations a lock_timeout so they fail fast instead of freezing the table:

     set lock_timeout = '3s';
     

   • Close abandoned transactions automatically in your own sessions, for example in a direct connection string: ?options=-c%20idle_in_transaction_session_timeout%3D60000.

   • Keep transactions short, and don't wait on network calls (HTTP requests, user input) inside one.

   • Update rows in a consistent order (for example by id) when one transaction touches several.

On SupaHost

• Run these queries in Studio's SQL editor (Dashboard → your project → Database tab → Open Studio). Studio's own sessions appear as postgres.
• Each project's Data API has a pool of 5 connections. If a few API requests are stuck behind a lock, the rest of that project's API requests queue for a free connection, so a single blocked table can look like the whole API is down.
• Locks never cross projects, since each project has its own database.

Related

• ALTER TABLE takes forever on a large table
• Connections and timeouts
• Supabase API call hangs or never returns
• Freezing writes during a migration