September 25, 2024
Summary: in this tutorial, you will learn how to check the lock level taken by operations in PostgreSQL.
Table of Contents
Introduction
PostgreSQL’s documentation is generally pretty clear about the locks taken by various operations – but nothing’s perfect. If you need to, you can also ask PostgreSQL directly.
You can check lock levels trivially with psql or PgAdmin.
For example, to see what lock alter table some_table disable trigger some_trigger takes:
BEGIN;
ALTER TABLE some_table DISABLE TRIGGER some_trigger;
SELECT locktype, mode FROM pg_locks
WHERE pid = pg_backend_pid() AND relation = 'some_table'::regclass;
locktype | mode
----------+-----------------------
relation | ShareRowExclusiveLock
(1 row)
That’s for a lock on a table. It’s easy to see from this that we take a SHARE ROW EXCLUSIVE lock, which according to the documentation:
… protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.
Other uses of pg_locks
You can also filter on pg_locks in other ways to see other locks held by a transaction. You’ll have to do some joins on pg_class etc to decode the relation OIDs to names – which is why we really need a pg_stat_locks view in PostgreSQL to make this easier.
People use a variety of canned queries of varying quality for looking to see which processes block others at the moment. With the addition of more detailed lock wait information and pg_blocking_pids() in PostgreSQL 9.6, this will get a lot easier, though it doesn’t add a helper view yet.
What are the virtualxid and transactionid locks?
One important and possibly confusing thing you’ll see in pg_locks is that every transaction holds a special lock on its self, called the virtualxid lock:
BEGIN;
SELECT * FROM pg_locks WHERE pid = pg_backend_pid();
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 16386 | 11673 | | | | | | | | 2/3983 | 24250 | AccessShareLock | t | t
virtualxid | | | | | 2/3983 | | | | | 2/3983 | 24250 | ExclusiveLock | t | t
(2 rows)
select '11673'::regclass;
regclass
----------
pg_locks
(1 row)
As you can see, the relation AccessShareLock is just the lock we take on pg_locks when we query it, so you can ignore that.
The virtualxid lock is special. It’s a exclusive lock on the transaction’s own virtual transaction ID (the “2/3983”, above) that every transaction always holds. No other transaction can ever acquire it while the transaction is running. The purpose of this is to allow one transaction to wait until another transaction commits or rolls back using PostgreSQL’s locking mechanism, and it’s used internally. You don’t normally need to use it yourself, but it’s useful to understand what it is when you see it in pg_locks.
There’s a similar entry for transactions that get a real read/write transaction ID that other transactions can use to wait until they commit or roll back:
select txid_current();
txid_current
--------------
2774
(1 row)
SELECT * FROM pg_locks WHERE pid = pg_backend_pid();
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 16386 | 11673 | | | | | | | | 2/3984 | 24250 | AccessShareLock | t | t
virtualxid | | | | | 2/3984 | | | | | 2/3984 | 24250 | ExclusiveLock | t | t
transactionid | | | | | | 2774 | | | | 2/3984 | 24250 | ExclusiveLock | t | f
(3 rows)
so if you’ve ever wondered what they are, now you know.
You’ll see a transactionid lock wait at when two concurrent transactions try to insert the same key into a unique index (or primary key), among other things. In that case the second transaction must wait until the first commits or rolls back to know whether it should fail with an error or continue to insert.