# Finding queries blocked by locks

> Find which session is blocking your queries with pg_stat_activity and pg_blocking_pids, end it safely, and stop it happening again with lock_timeout and idle transaction timeouts.

Source: https://supahost.app/kb/blocked-queries · Updated 2026-11-11

## 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:

   ```sql
   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:

   ```sql
   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:

   ```sql
   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:

     ```sql
     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](/kb/slow-alter-table)
- [Connections and timeouts](/docs/database/connections-and-timeouts)
- [Supabase API call hangs or never returns](/kb/api-call-not-returning)
- [Freezing writes during a migration](/kb/read-only-mode)