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?

up vote 1 down vote accepted

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.

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.