You run a query on your Postgres database. A SELECT, an UPDATE, or maybe a schema change – something you’ve done many times before. And it just… sits there. No response, no error, no result. And then at some point it probably just times out.
Your first instinct is that something is wrong with the query, and it’s too slow to finish within the timeout. So you start analyzing it, trying to figure out the problem. You look at the execution plan, review the indexes, but nothing really pops out.
I’ve been there. Staring at a forever-blinking cursor is not fun.
But here is the thing: there’s a good chance nothing is actually wrong with your query. Rather, something else is holding a lock that your query is waiting behind. And the good news is that it’s fairly easy to check. Just three simple steps.
Step 1: Confirm it’s actually a lock
First and foremost, you need to check whether your query is indeed waiting on a lock.
Postgres exposes a lot of useful information about client processes via the pg_stat_activity system view, which we can use here. Just run the following query:
SELECT pid, usename, wait_event_type, query
FROM pg_stat_activity
WHERE state = 'active';In case your database is used by many clients (application instances, querying tools, etc.), you might want to narrow down the list. For example, this is how you can find clients that are running under your username:
SELECT pid, usename, wait_event_type, query
FROM pg_stat_activity
WHERE state = 'active'
AND usename = '<your_username>';Once you’ve located the row for the stuck process, look at the value for the wait_event_type field. If it says Lock, then you are on the right path and it’s time to proceed to the second step. Otherwise, keep digging – there might be something wrong with the query after all.
For example, here is my stuck query:
postgres=> SELECT pid, usename, wait_event_type, query
FROM pg_stat_activity
WHERE state = 'active';
pid | usename | wait_event_type | query
------+----------------+-----------------+--------------------------
2903 | val_kulichenko | Lock | SELECT * FROM lock_test;Step 2: Find the blocker
Now that you know your query is waiting for a lock, let’s locate the process (or processes) that hold that lock. For that, you can use the pg_blocking_pids system function.
First, go back to the results of the pg_stat_activity query and note the ID of your process: you’ll find it in the pid field of the corresponding row. Once you have the value, run the following query:
SELECT pid, usename, client_addr, client_port, state, query
FROM pg_stat_activity
WHERE pid = ANY (pg_blocking_pids(<your_pid>));This will give you the list of “offenders”, and in most cases there will be only one row, which will look something like this:
postgres=> SELECT pid, usename, client_addr, client_port, state, query
FROM pg_stat_activity
WHERE pid = ANY (pg_blocking_pids(2903));
pid | usename | client_addr | client_port | state | query
------+----------------+----------------+-------------+---------------------+------------------------------------------------
3018 | val_kulichenko | 111.111.111.111 | 56376 | idle in transaction | LOCK TABLE lock_test IN ACCESS EXCLUSIVE MODE;Now look at the query and try to identify it. Could it be a long-running schema change operation that you recognize (this will be ALTER TABLE or similar)? Or a long report (like a SELECT statement scanning multiple tables)? It’s also possible that someone explicitly holds a lock on a table that you’re trying to access – this is exactly what you see in my example (I did this purely for test purposes, of course).
Regardless, at this point you have information to work with and figure out what exactly is causing the problem. You know where that process is running, which query it is attempting to execute, and which user initiated it.
Step 3: Decide what to do
Now that you’ve identified the problem, it’s time to solve it. Here, a lot depends on your circumstances and judgment.
If you know this process performs a piece of legitimate work that’s expected to take a significant amount of time, and you also know these locks don’t affect end users, most likely the best approach is to just let it do its thing and wait for completion.
If it’s a “junk” query, then kill the process.
If you are part of a team, it’s not your process, and you are unsure – contact the owner of the process (use the usename field to identify them).
Your case might not match any of these exactly – but that’s the point. Once you know what’s blocking you and who owns it, the right move is usually obvious.
A hanging query is not a mystery, and it isn’t a reason for rebooting the database or other drastic measures. More often than not, it’s a lock created by some other process, which you can easily find and resolve using the steps described here.
Good luck!