March 4, 2025
Summary: in this tutorial, you will learn how to troubleshooting deadlocks in PostgreSQL.
Table of Contents
In this tutorial, let’s look at why locks happen, and more importantly how to get to the bottom of a lock issue and the one process blocking everything else. Once you find the source of the lock, I’ll give you the options for terminating the process that created all your troubles in the first place.
Finding the source of the lock
Often you won’t immediately know that you have a lock issue. If something is off, queries aren’t returning, or your application is slow, finding statements blocked by locks is a great place to start.
1. Find processes that are waiting
Take a look at the pg_stat_activity view for processes with active state but have a wait_event or wait_event_type that are non-NULL:
SELECT
pid,
datname,
usename,
application_name,
client_addr,
client_port,
to_char(now(), 'YYYY-MM-DD HH24:MI:SS') as now,
to_char(now() - xact_start, 'DD HH24:MI:SS MS') as xact_time,
to_char(now() - query_start, 'DD HH24:MI:SS MS') as query_time,
state,
to_char(now() - state_change, 'DD HH24:MI:SS MS') as state_time,
wait_event,
wait_event_type,
left(query, 40)
FROM
pg_stat_activity
WHERE
state != 'idle'
and pid != pg_backend_pid()
ORDER BY
query_time desc;
If a connection is active and waiting on a lock, then the wait_event and wait_event_type columns will be non-NULL. If that’s the case (and it stays that way after a couple of runs of the query to ensure that you didn’t just catch a short lock wait), record that affected PID. Here is a very simple example where I ran an update in a transaction, then in a different session added a column to the same table. The ALTER TABLE in this case will not proceed until the transaction from the prior thread has been committed or rolled back. Here are the results - note the PID 295998 that is “active” but has wait_event=relation and wait_event_type=Lock
pid | datname | usename | application_name | client_addr | client_port | now | xact_time | query_time | state | state_time | wait_event | wait_event_type | left
--------+----------+----------+------------------+----------------+-------------+---------------------+-----------------+-----------------+---------------------+-----------------+------------+-----------------+------------------------------------------
295995 | postgres | postgres | psql | 149.42.105.253 | 49327 | 2025-02-25 20:41:10 | 00 00:02:11 535 | 00 00:02:01 755 | idle in transaction | 00 00:02:01 755 | ClientRead | Client | RELEASE pg_psql_temporary_savepoint
295998 | postgres | postgres | psql | 149.42.105.253 | 49344 | 2025-02-25 20:41:10 | 00 00:01:55 550 | 00 00:01:01 138 | active | 00 00:01:01 138 | relation | Lock | alter table sampledata add column data02
(2 rows)
2. Find which PID is locking the table
Now we know that the PID (295998) is awaiting a lock on a relation (table), but we don’t know what process currently holds the lock on which it is waiting. To find it, we start by querying pg_locks using the ID of the awaiting process:
SELECT
*
FROM
pg_locks
WHERE
pid = 295998
AND granted IS NOT true;
Here’s the result of that query:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+---------------------+---------+----------+------------------------------
relation | 5 | 16501 | | | | | | | | 6/6743 | 295998 | AccessExclusiveLock | f | f | 2025-02-25 20:40:08.98843+00
(1 row)
The locktype column shows which of the other columns describe what Postgres is waiting on. In this example, locktype is relation, so we look to the relation column to see the OID of the relation (16501) where the blocking process has an active lock.
3. Find the process with the existing lock
Now that we know which object is locked, we can once again query pg_locks using the relation OID to see what is holding the current lock(s):
SELECT
*
FROM
pg_locks
WHERE
relation = 16501
AND granted IS true;
Here is the result:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+------------------+---------+----------+-----------
relation | 5 | 16501 | | | | | | | | 3/243227 | 295995 | RowExclusiveLock | t | f |
(1 row)
This shows that PID 295995 is the process holding the lock.
4. Find what that blocking process is doing
Now that we know which process has been granted the lock, we can go back to pg_stat_activity to see what that PID is doing:
SELECT
pid,
state,
wait_event,
wait_event_type,
left (query, 40)
FROM
pg_stat_activity
WHERE
pid = 295995;
Here is the result:
pid | state | wait_event | wait_event_type | left
--------+---------------------+------------+-----------------+-------------------------------------
295995 | idle in transaction | ClientRead | Client | RELEASE pg_psql_temporary_savepoint
The last column is showing the last statement executed by that session, which in this case was the savepoint release after an update, but in most cases it will show an active transaction.
One lock to rule them all
The above statements are pretty straightforward once you know what you are looking for, but they can also be combined into a single statement for a general blocking / blocked query. The Postgres wiki has some good combined versions.
Often times you might find that the blocked statement is blocked by another (and another, and another still…). In those cases, it is still possible to trace all the way up to the One PID that blocks all the rest, but that can be an arduous, unexpected journey. For those cases, the following query can help you to show locks waiting on other locks, rolling up to the PID holding the initial lock:
WITH sos AS (
SELECT array_cat(array_agg(pid),
array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid), 1)])) pids
FROM pg_locks
WHERE NOT granted
)
SELECT a.pid, a.usename, a.datname, a.state,
a.wait_event_type || ': ' || a.wait_event AS wait_event,
current_timestamp-a.state_change time_in_state,
current_timestamp-a.xact_start time_in_xact,
l.relation::regclass relname,
l.locktype, l.mode, l.page, l.tuple,
pg_blocking_pids(l.pid) blocking_pids,
(pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid), 1)] last_session,
coalesce((pg_blocking_pids(l.pid))[1] || '.' ||
coalesce(case when locktype = 'transactionid' then 1
else array_length(pg_blocking_pids(l.pid), 1) + 1 end,
0),
a.pid || '.0') lock_depth,
a.query
FROM pg_stat_activity a
JOIN sos s on (a.pid = any(s.pids))
LEFT OUTER JOIN pg_locks l on (a.pid = l.pid and not l.granted)
ORDER BY lock_depth;
Example output from that statement:
pid | usename | datname | state | wait_event | time_in_state | time_in_xact | relname | locktype | mode | page | tuple | blocking_pids | last_session | lock_depth | query
--------+-------------+----------+---------------------+---------------------+-----------------+-----------------+------------+---------------+---------------------+------+-------+------------------------+--------------+------------+----------------------------------------------------
879401 | application | postgres | idle in transaction | Client: ClientRead | 00:29:53.512147 | 00:30:01.31748 | | | | | | | | 879401.0 | select * from sampledata where id=101 for update;
880275 | application | postgres | active | Lock: transactionid | 00:01:00.342763 | 00:01:00.459375 | | transactionid | ShareLock | | | {879401} | 879401 | 879401.1 | update sampledata set data = 'abc' where id = 101;
880204 | application | postgres | active | Lock: relation | 00:00:29.722705 | 00:00:29.722707 | sampledata | relation | AccessExclusiveLock | | | {879401,880275,879488} | 879488 | 879401.4 | alter table sampledata add column data03 integer;
880187 | application | postgres | active | Lock: relation | 00:00:03.580716 | 00:00:03.580718 | sampledata | relation | RowExclusiveLock | | | {880204} | 880204 | 880204.2 | update sampledata set data = 'abc' where id = 103;
879527 | application | postgres | active | Lock: relation | 00:00:14.974433 | 00:28:32.80346 | sampledata | relation | RowExclusiveLock | | | {880204} | 880204 | 880204.2 | update sampledata set data = 'abc' where id = 102;
879488 | application | postgres | active | Lock: tuple | 00:00:41.35361 | 00:00:41.47118 | sampledata | tuple | ExclusiveLock | 2 | 21 | {880275} | 880275 | 880275.2 | update sampledata set data = 'def' where id = 101;
(6 rows)
In this manufactured example we have:
879401 - the “idle in transaction” PID - This is a SELECT... FOR UPDATE within a transaction. Its blocking_pids field is blank because it’s not blocked by any other process. This is the process in this example that is blocking everything else.
880275 - Attempting to update the same id=101 - It’s blocked until the FOR UPDATE is completed.
879488 - Again attempting to update the same id=101 - It can’t execute until the process blocking it completes. It’s waiting on 880275 since it came in afterwards. If 880275 is canceled, it will just roll up to the next blocker, 879401.
880204 - Here added in an ALTER TABLE - since it takes an access exclusive lock, note it’s blocking_pids shows all three of the prior statements - it won’t be able to execute until each of those are out of the way.
879527 - Blocked by the ALTER TABLE since it requires an AccessExclusiveLock. Note that it’s still blocked, even though it’s a different row (id=102).
880187 - Blocked also by ALTER TABLE. They are at the same lock_depth since they are both blocked by the same thing, but not by each other.
Ending the process holding the lock
Ok, now we’ve found the PID at the top of the tree, that one locking holding the key to the rest of our locks. Fortunately, as Postgres wizards, we do possess the craft to unmake the lock.
Commit
If the statement is showing as idle in transaction it is possible that you have a non-committed transaction open that started with a BEGIN statement. In that case you can commit with:
COMMIT;
Rollback
You may have performed some unintended updates, or run into an error. In that case you can abort the transaction and rollback any changes already made with:
ROLLBACK;
Cancel the PID
If this wasn’t an transaction you initiated, in most cases you can cancel the running query with:
SELECT pg_cancel_backend(PID);
Terminate the backend connection and process
If the cancel statement above doesn’t work, you can force the backend process terminated by executing a terminate backend statement. This will end the process and its associated database connection.
SELECT pg_terminate_backend(PID);