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?
Best Answer
I found an answer.
There were two parts to this.
Firstly, what is important is
virtualtransaction
rather thanvirtualxid
. Even thoughvirtualxid
andtransactionid
are null for everything I care about, they all share avirtualtransaction
id.Secondly, although
txid_current()
returns atransactionid
, 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 thetransactionid
returned by that function, which we can see if we querypg_locks
. This lock will share avirtualtransaction
id with everything else in the current transaction.For example:
So now from this we can get the
virtualtransaction
id shared by everything in the current transaction, and filter on that.In other words:
...will return only locks relevant to the current transaction.