Understanding postgres txid_current and pg_locks

Question

I am trying to get a list of pg_locks relevant to the current transaction

eg

> BEGIN;
BEGIN
> ALTER TABLE ... ;
ALTER TABLE
> select locktype,virtualxid,transactionid,mode,relation from pg_locks;
  locktype  | virtualxid | transactionid |        mode         | relation
------------+------------+---------------+---------------------+----------
 relation   | [NULL]     |        [NULL] | AccessShareLock     |    11695
 virtualxid | 2/24699    |        [NULL] | ExclusiveLock       |   [NULL]
 relation   | [NULL]     |        [NULL] | AccessExclusiveLock |   801091
(3 rows)

Ok so what I think of as "my" lock here is the last one, the AccessExclusiveLock

But it has no transactionid or virtualxid.

If there is other activity in the db I will see other locks too in this list.

How do I filter out just the locks belonging to my current transaction, if they have null transaction ids?

Answer 1

I found an answer.

There were two parts to this.

Firstly, what is important is virtualtransaction rather than virtualxid. Even though virtualxid and transactionid are null for everything I care about, they all share a virtualtransaction id.

Secondly, although txid_current() returns a transactionid, which seems like it wouldn't help because my locks all have null for that, we can still use it to get what we want.

What happens is when you call txid_current() postgres assigns a lock with the transactionid returned by that function, which we can see if we query pg_locks. This lock will share a virtualtransaction id with everything else in the current transaction.

For example:

> BEGIN;
BEGIN
> SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks;
  locktype  | transactionid | virtualtransaction |      mode
------------+---------------+--------------------+-----------------
 relation   |        [NULL] | 3/38672            | AccessShareLock
 virtualxid |        [NULL] | 3/38672            | ExclusiveLock
(2 rows)

> SELECT txid_current();
 txid_current
--------------
       355056
(1 row)

> SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks;
   locktype    | transactionid | virtualtransaction |      mode
---------------+---------------+--------------------+-----------------
 relation      |        [NULL] | 3/38672            | AccessShareLock
 virtualxid    |        [NULL] | 3/38672            | ExclusiveLock
 transactionid |        355056 | 3/38672            | ExclusiveLock
(3 rows)

So now from this we can get the virtualtransaction id shared by everything in the current transaction, and filter on that.

In other words:

SELECT
    *
FROM pg_locks pl
WHERE
    virtualtransaction=(
        SELECT virtualtransaction FROM pg_locks
            WHERE
                transactionid::text = (txid_current() % (2^32)::bigint)::text
                -- compare int to xid, see https://dba.stackexchange.com/a/123183/10371 
                AND locktype='transactionid'
        LIMIT 1
    )

...will return only locks relevant to the current transaction.

Answer 2

How about this:

pgb=# begin;
BEGIN
pgb=# select * from pg_locks where pid=pg_backend_pid();
  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid   |      mode       | granted | fastp
ath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+-----------------+---------+------
----
 relation   |    33154 |    12143 |      |       |            |               |         |       |          | 6/20625            | 167525 | AccessShareLock | t       | t
 virtualxid |          |          |      |       | 6/20625    |               |         |       |          | 6/20625            | 167525 | ExclusiveLock   | t       | t
(2 rows)

pgb=# insert into test values(4);
INSERT 0 1
pgb=# select * from pg_locks where pid=pg_backend_pid();
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid   |       mode       | granted | f
astpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+------------------+---------+--
--------
 relation      |    33154 |    33212 |      |       |            |               |         |       |          | 6/20625            | 167525 | RowExclusiveLock | t       | t
 relation      |    33154 |    12143 |      |       |            |               |         |       |          | 6/20625            | 167525 | AccessShareLock  | t       | t
 virtualxid    |          |          |      |       | 6/20625    |               |         |       |          | 6/20625            | 167525 | ExclusiveLock    | t       | t
 transactionid |          |          |      |       |            |        592429 |         |       |          | 6/20625            | 167525 | ExclusiveLock    | t       | f
(4 rows)

Answer 3

See if this helps:

select l.relation::regclass, l.relation, l.locktype, l.database, l.classid, l.objid, l.objsubid, l.pid, l.mode, l.granted, l.fastpath 
from pg_locks l
where granted = false ;

It will display the table name and where granted = false shows where access has not been granted (i.e., a lock exists).

Also, you can get your current PID in BASH:

$ ( echo $$; echo $BASHPID )

get pid in shell (bash)

Some useful queries here for monitoring locks: Lock Monitoring