How to check the lock level taken by operations in PostgreSQL

PostgreSQL’s manual 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:

test=> BEGIN;
BEGIN
test=> ALTER TABLE some_table DISABLE TRIGGER some_trigger;
ALTER TABLE
test=> 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 manual:

… 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 9.6, this will get a lot easier, though 9.6 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:

test=> BEGIN;
BEGIN
test=> 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)
test=> 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:

test=> select txid_current();
 txid_current 
--------------
         2774
(1 row)
test=> 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.