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.<\/p>\n\n\n\n
We’ll start with some basics and a very simple playground: One simple table containing one row:<\/p>\n\n\n
\npostgres=# create table t (a int);\nCREATE TABLE\npostgres=# insert into t values (1);\nINSERT 0 1\n<\/pre><\/div>\n\n\nOne source of information about locking is pg_locks<\/a>. Do we already see something in this catalog view? Indeed we do:<\/p>\n\n\n\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks;\n locktype | database | relation | pid | mode | granted | waitstart \n------------+----------+----------+------+-----------------+---------+-----------\n relation | postgres | pg_locks | 9119 | AccessShareLock | t | \n virtualxid | | | 9119 | ExclusiveLock | t | \n(2 rows)\n<\/pre><\/div>\n\n\nWhat we see here is our own session (ignore the second row with “virtualxid” for now):<\/p>\n\n\n
\npostgres=# select pg_backend_pid();\n pg_backend_pid \n----------------\n 9119\n(1 row)\n<\/pre><\/div>\n\n\nThe 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.<\/p>\n\n\n\n
If you do the same in a second session you might expect to see four rows in total, but:<\/p>\n\n\n
\n-- session 2\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks;\n locktype | database | relation | pid | mode | granted | waitstart \n------------+----------+----------+------+-----------------+---------+-----------\n relation | postgres | pg_locks | 9673 | AccessShareLock | t | \n virtualxid | | | 9673 | ExclusiveLock | t | \n(2 rows)\n<\/pre><\/div>\n\n\nSeems 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.<\/p>\n\n\n\n
So what we can do to exclude our own locks is this:<\/p>\n\n\n
\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();\n locktype | database | relation | pid | mode | granted | waitstart \n----------+----------+----------+-----+------+---------+-----------\n(0 rows)\n<\/pre><\/div>\n\n\nThe 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:<\/p>\n\n\n
\n-- session 1\npostgres=# begin;\nBEGIN\npostgres=*# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();\n locktype | database | relation | pid | mode | granted | waitstart \n----------+----------+----------+-----+------+---------+-----------\n(0 rows)\n\n-- session 2\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();\n locktype | database | relation | pid | mode | granted | waitstart \n------------+----------+----------+------+-----------------+---------+-----------\n relation | postgres | pg_locks | 9119 | AccessShareLock | t | \n virtualxid | | | 9119 | ExclusiveLock | t | \n(2 rows)\n<\/pre><\/div>\n\n\nBecause the first session is still active and the transaction is open, this session still holds the lock. Ending the transaction releases the lock:<\/p>\n\n\n
\n-- session 1\npostgres=*# end;\nCOMMIT\n\n-- session 2\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();\n locktype | database | relation | pid | mode | granted | waitstart \n----------+----------+----------+-----+------+---------+-----------\n(0 rows)\n<\/pre><\/div>\n\n\nLets start doing something with our table in session two:<\/p>\n\n\n
\n-- session 2\npostgres=# begin;\nBEGIN\npostgres=*# select * from t;\n a \n---\n 1\n(1 row)\n<\/pre><\/div>\n\n\nHow does it look like in session one?<\/p>\n\n\n
\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();\n locktype | database | relation | pid | mode | granted | waitstart \n------------+----------+----------+------+-----------------+---------+-----------\n relation | postgres | t | 9673 | AccessShareLock | t | \n virtualxid | | | 9673 | ExclusiveLock | t | \n(2 rows)\n<\/pre><\/div>\n\n\nThis 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:<\/p>\n\n\n
\n-- session 2\npostgres=*# update t set a = 2 where a = 1;\nUPDATE 1\n\n-- session 1\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart from pg_locks where pid != pg_backend_pid();\n locktype | database | relation | pid | mode | granted | waitstart \n---------------+----------+----------+------+------------------+---------+-----------\n relation | postgres | t | 9673 | AccessShareLock | t | \n relation | postgres | t | 9673 | RowExclusiveLock | t | \n virtualxid | | | 9673 | ExclusiveLock | t | \n transactionid | | | 9673 | ExclusiveLock | t | \n(4 rows)\n<\/pre><\/div>\n\n\nNow 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:<\/p>\n\n\n
\n-- session 3\npostgres=# begin;\nBEGIN\npostgres=*# update t set a = 2 where a = 1;\n<\/pre><\/div>\n\n\n… and the results in the first session look like this (I have added an order by and the “page” and “tuple” columns):<\/p>\n\n\n
\n-- session 1\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,page,tuple from pg_locks where pid != pg_backend_pid() order by pid;\n locktype | database | relation | pid | mode | granted | waitstart | page | tuple \n---------------+----------+----------+------+------------------+---------+-------------------------------+------+-------\n relation | postgres | t | 9673 | RowExclusiveLock | t | | | \n virtualxid | | | 9673 | ExclusiveLock | t | | | \n transactionid | | | 9673 | ExclusiveLock | t | | | \n relation | postgres | t | 9673 | AccessShareLock | t | | | \n tuple | postgres | t | 9691 | ExclusiveLock | t | | 0 | 1\n transactionid | | | 9691 | ExclusiveLock | t | | | \n relation | postgres | t | 9691 | RowExclusiveLock | t | | | \n virtualxid | | | 9691 | ExclusiveLock | t | | | \n transactionid | | | 9691 | ShareLock | f | 2026-01-08 09:14:00.330564+01 | | \n(9 rows)\n<\/pre><\/div>\n\n\nWhat 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.<\/p>\n\n\n
\n-- session 2\npostgres=*# commit;\nCOMMIT\n\n-- session 3\nUPDATE 0\npostgres=*# select * from t;\n a \n---\n 2\n(1 row)\n\npostgres=*# end;\nCOMMIT\n<\/pre><\/div>\n\n\nIn session one we can verify that all locks are gone:<\/p>\n\n\n
\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,page,tuple from pg_locks where pid != pg_backend_pid() order by pid;\n locktype | database | relation | pid | mode | granted | waitstart | page | tuple \n----------+----------+----------+-----+------+---------+-----------+------+-------\n(0 rows)\n<\/pre><\/div>\n\n\nAnother example with 5 rows in the table and two sessions update only the last row:<\/p>\n\n\n
\n-- session 1\npostgres=# truncate t;\nTRUNCATE TABLE\npostgres=# insert into t values (1),(2),(3),(4),(5);\nINSERT 0 5\n\n-- session 2\npostgres=# begin;\nBEGIN\npostgres=*# update t set a = -1 where a = 5;\nUPDATE 1\n\n-- session 3\npostgres=# begin;\nBEGIN\npostgres=*# update t set a = -1 where a = 5;\n\n-- session 1\npostgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,page,tuple from pg_locks where pid != pg_backend_pid() order by pid;\n locktype | database | relation | pid | mode | granted | waitstart | page | tuple \n---------------+----------+----------+------+------------------+---------+-------------------------------+------+-------\n relation | postgres | t | 9673 | RowExclusiveLock | t | | | \n virtualxid | | | 9673 | ExclusiveLock | t | | | \n transactionid | | | 9673 | ExclusiveLock | t | | | \n virtualxid | | | 9691 | ExclusiveLock | t | | | \n transactionid | | | 9691 | ShareLock | f | 2026-01-08 09:58:38.867534+01 | | \n tuple | postgres | t | 9691 | ExclusiveLock | t | | 0 | 5\n transactionid | | | 9691 | ExclusiveLock | t | | | \n relation | postgres | t | 9691 | RowExclusiveLock | t | | | \n(8 rows)\n<\/pre><\/div>\n\n\nNow it is row number 5 in block 0. <\/p>\n\n\n\n
The rows with “transactionid” and “virtualxid” are about the transactions, details here<\/a>. Those values are also recorded in pg_locks:<\/p>\n\n\n\n-- session 1\npostgres=# 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;\n locktype | database | relation | pid | mode | granted | waitstart | page | tuple | transactionid | virtualxid \n---------------+----------+----------+------+------------------+---------+-------------------------------+------+-------+---------------+------------\n relation | postgres | t | 9673 | RowExclusiveLock | t | | | | | \n virtualxid | | | 9673 | ExclusiveLock | t | | | | | 24\/22\n transactionid | | | 9673 | ExclusiveLock | t | | | | 836 | \n virtualxid | | | 9691 | ExclusiveLock | t | | | | | 25\/18\n transactionid | | | 9691 | ShareLock | f | 2026-01-08 09:58:38.867534+01 | | | 836 | \n tuple | postgres | t | 9691 | ExclusiveLock | t | | 0 | 5 | | \n transactionid | | | 9691 | ExclusiveLock | t | | | | 837 | \n relation | postgres | t | 9691 | RowExclusiveLock | t | | | | | \n\n<\/pre><\/div>\n\n\nWhen 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<\/a>:<\/p>\n\n\n