Sometimes it is necessary to troubleshoot locks in PostgreSQL to figure out what is going on in the system. While locks are important and necessary in a database system, too many locks can also be an issue, performance wise and resource consumption wise. There are several levels of locks, e.g. table level and row level locks. Depending on what kind of locks are causing an issue you need to take other actions to resolve it.

We’ll start with some basics and a very simple playground: One simple table containing one row:

postgres=# create table t (a int);
CREATE TABLE
postgres=# insert into t values (1);
INSERT 0 1

One source of information about locking is pg_locks. Do we already see something in this catalog view? Indeed we do:

postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks;
  locktype  | database | relation | pid  |      mode       | granted | waitstart 
------------+----------+----------+------+-----------------+---------+-----------
 relation   | postgres | pg_locks | 9119 | AccessShareLock | t       | 
 virtualxid |          |          | 9119 | ExclusiveLock   | t       | 
(2 rows)

What we see here is our own session (ignore the second row with “virtualxid” for now):

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
           9119
(1 row)

The question is why? We only did a simple select, but even a select which does not modify the database needs locking because while we’re reading nobody else is allowed to modify the underlying objects. This is what “AccessShareLock” is about: Others can still read, but not change.

If you do the same in a second session you might expect to see four rows in total, but:

-- session 2
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks;
  locktype  | database | relation | pid  |      mode       | granted | waitstart 
------------+----------+----------+------+-----------------+---------+-----------
 relation   | postgres | pg_locks | 9673 | AccessShareLock | t       | 
 virtualxid |          |          | 9673 | ExclusiveLock   | t       | 
(2 rows)

Seems a bit surprising but pg_locks shows only locks from active processes and once the statement completes the process is not active anymore and the lock is gone.

So what we can do to exclude our own locks is this:

postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();
 locktype | database | relation | pid | mode | granted | waitstart 
----------+----------+----------+-----+------+---------+-----------
(0 rows)

The story is a bit different if we do the same thing in a transaction in the first session and then query the view in the second session:

-- session 1
postgres=# begin;
BEGIN
postgres=*# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();
 locktype | database | relation | pid | mode | granted | waitstart 
----------+----------+----------+-----+------+---------+-----------
(0 rows)

-- session 2
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();
  locktype  | database | relation | pid  |      mode       | granted | waitstart 
------------+----------+----------+------+-----------------+---------+-----------
 relation   | postgres | pg_locks | 9119 | AccessShareLock | t       | 
 virtualxid |          |          | 9119 | ExclusiveLock   | t       | 
(2 rows)

Because the first session is still active and the transaction is open, this session still holds the lock. Ending the transaction releases the lock:

-- session 1
postgres=*# end;
COMMIT

-- session 2
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();
 locktype | database | relation | pid | mode | granted | waitstart 
----------+----------+----------+-----+------+---------+-----------
(0 rows)

Lets start doing something with our table in session two:

-- session 2
postgres=# begin;
BEGIN
postgres=*# select * from t;
 a 
---
 1
(1 row)

How does it look like in session one?

postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();
  locktype  | database | relation | pid  |      mode       | granted | waitstart 
------------+----------+----------+------+-----------------+---------+-----------
 relation   | postgres | t        | 9673 | AccessShareLock | t       | 
 virtualxid |          |          | 9673 | ExclusiveLock   | t       | 
(2 rows)

This is consistent to the results above. As the second session is reading from the table and the transaction is still open, the lock is still there, no surprise. Lets update the row in the still open transaction and then have a look again in session one:

-- session 2
postgres=*# update t set a = 2 where a = 1;
UPDATE 1

-- session 1
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();
   locktype    | database | relation | pid  |       mode       | granted | waitstart 
---------------+----------+----------+------+------------------+---------+-----------
 relation      | postgres | t        | 9673 | AccessShareLock  | t       | 
 relation      | postgres | t        | 9673 | RowExclusiveLock | t       | 
 virtualxid    |          |          | 9673 | ExclusiveLock    | t       | 
 transactionid |          |          | 9673 | ExclusiveLock    | t       | 
(4 rows)

Now we got a “RowExclusiveLock” in addition and that means nobody else can do anything with the new row/tuple we’ve just created. When someone else (third session) is trying to perform the same update this will block:

-- session 3
postgres=# begin;
BEGIN
postgres=*# update t set a = 2 where a = 1;

… and the results in the first session look like this (I have added an order by and the “page” and “tuple” columns):

-- session 1
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,page,tuple from pg_locks where pid != pg_backend_pid() order by pid;
   locktype    | database | relation | pid  |       mode       | granted |           waitstart           | page | tuple 
---------------+----------+----------+------+------------------+---------+-------------------------------+------+-------
 relation      | postgres | t        | 9673 | RowExclusiveLock | t       |                               |      |      
 virtualxid    |          |          | 9673 | ExclusiveLock    | t       |                               |      |      
 transactionid |          |          | 9673 | ExclusiveLock    | t       |                               |      |      
 relation      | postgres | t        | 9673 | AccessShareLock  | t       |                               |      |      
 tuple         | postgres | t        | 9691 | ExclusiveLock    | t       |                               |    0 |     1
 transactionid |          |          | 9691 | ExclusiveLock    | t       |                               |      |      
 relation      | postgres | t        | 9691 | RowExclusiveLock | t       |                               |      |      
 virtualxid    |          |          | 9691 | ExclusiveLock    | t       |                               |      |      
 transactionid |          |          | 9691 | ShareLock        | f       | 2026-01-08 09:14:00.330564+01 |      |      
(9 rows)

What we see now is, that the third session (pid=9691) is trying to get the lock but can’t and must wait (waitstart) for the second session to release the lock. We can also see that the row/tuple in question is in block/page 0 and the tuple/row is the first one in that block. Once session two either commits or rollbacks the update in session three will proceed but there is nothing to update as there is no row which matches the where clause anymore.

-- session 2
postgres=*# commit;
COMMIT

-- session 3
UPDATE 0
postgres=*# select * from t;
 a 
---
 2
(1 row)

postgres=*# end;
COMMIT

In session one we can verify that all locks are gone:

postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,page,tuple from pg_locks where pid != pg_backend_pid() order by pid;
 locktype | database | relation | pid | mode | granted | waitstart | page | tuple 
----------+----------+----------+-----+------+---------+-----------+------+-------
(0 rows)

Another example with 5 rows in the table and two sessions update only the last row:

-- session 1
postgres=# truncate t;
TRUNCATE TABLE
postgres=# insert into t values (1),(2),(3),(4),(5);
INSERT 0 5

-- session 2
postgres=# begin;
BEGIN
postgres=*# update t set a = -1 where a = 5;
UPDATE 1

-- session 3
postgres=# begin;
BEGIN
postgres=*# update t set a = -1 where a = 5;

-- session 1
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,page,tuple from pg_locks where pid != pg_backend_pid() order by pid;
   locktype    | database | relation | pid  |       mode       | granted |           waitstart           | page | tuple 
---------------+----------+----------+------+------------------+---------+-------------------------------+------+-------
 relation      | postgres | t        | 9673 | RowExclusiveLock | t       |                               |      |      
 virtualxid    |          |          | 9673 | ExclusiveLock    | t       |                               |      |      
 transactionid |          |          | 9673 | ExclusiveLock    | t       |                               |      |      
 virtualxid    |          |          | 9691 | ExclusiveLock    | t       |                               |      |      
 transactionid |          |          | 9691 | ShareLock        | f       | 2026-01-08 09:58:38.867534+01 |      |      
 tuple         | postgres | t        | 9691 | ExclusiveLock    | t       |                               |    0 |     5
 transactionid |          |          | 9691 | ExclusiveLock    | t       |                               |      |      
 relation      | postgres | t        | 9691 | RowExclusiveLock | t       |                               |      |      
(8 rows)

Now it is row number 5 in block 0.

The rows with “transactionid” and “virtualxid” are about the transactions, details here. Those values are also recorded in pg_locks:

-- session 1
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,page,tuple,transactionid,virtualxid from pg_locks where pid != pg_backend_pid() order by pid;
   locktype    | database | relation | pid  |       mode       | granted |           waitstart           | page | tuple | transactionid | virtualxid 
---------------+----------+----------+------+------------------+---------+-------------------------------+------+-------+---------------+------------
 relation      | postgres | t        | 9673 | RowExclusiveLock | t       |                               |      |       |               | 
 virtualxid    |          |          | 9673 | ExclusiveLock    | t       |                               |      |       |               | 24/22
 transactionid |          |          | 9673 | ExclusiveLock    | t       |                               |      |       |           836 | 
 virtualxid    |          |          | 9691 | ExclusiveLock    | t       |                               |      |       |               | 25/18
 transactionid |          |          | 9691 | ShareLock        | f       | 2026-01-08 09:58:38.867534+01 |      |       |           836 | 
 tuple         | postgres | t        | 9691 | ExclusiveLock    | t       |                               |    0 |     5 |               | 
 transactionid |          |          | 9691 | ExclusiveLock    | t       |                               |      |       |           837 | 
 relation      | postgres | t        | 9691 | RowExclusiveLock | t       |                               |      |       |               |

When there are only a few locks in the database this is an easy way to find out who is blocking whom but if you have hundreds of sessions and locks there is a more convenient way to check for blockers and waiters, and this is the function pg_blocking_pids:

-- session 1
postgres=# select pg_blocking_pids(9691);
 pg_blocking_pids 
------------------
 {9673}
(1 row)

Another option to check more details in regards to row locking is the extension pgrowlocks:

-- session 1
postgres=# create extension pgrowlocks;
CREATE EXTENSION
postgres=# select * from t as a, pgrowlocks('t') as b where b.locked_row = a.ctid;
 a | locked_row | locker | multi | xids  |       modes       |  pids  
---+------------+--------+-------+-------+-------------------+--------
 5 | (0,5)      |    836 | f     | {836} | {"No Key Update"} | {9673}
(1 row)

In the next post we’ll look more closely on transaction IDs and virtual transaction IDs when it comes to locking.

Post Views: 58
Thumbnail [60x60]
by
Daniel Westermann