I'm trying to get transactionid from pg_locks table when aquired Postgres advisory lock within transaction, but it's always NULL. I want to be sure that lock aquired witin transaction. Is there is a way to do it?

BEGIN TRANSACTION;

SELECT pg_try_advisory_xact_lock(4);

SELECT transactionid
FROM pg_locks
WHERE locktype = 'advisory'
AND objid = 4;

Postgres container: postgres:13.4-alpine

CodePudding user response：

The doc on pg_locks says:

transactionid xid

ID of the transaction targeted by the lock, or null if the target is not a transaction ID

In other words, pg_locks.transactionid is filled in when locktype='transactionid'.

The normal way to check if the lock is acquired is to test the return value of pg_try_advisory_xact_lock.

To test it after the fact looking up pg_locks, you could use the fact that it has been acquired by the current backend:

SELECT 1
FROM pg_locks
WHERE locktype = 'advisory'
AND pid = pg_backend_pid()
AND objid = 4;

CodePudding user response：

When you get a lock, your transaction is not yet holding a permanent id, you can see this:

BEGIN TRANSACTION;

SELECT pg_try_advisory_xact_lock(4);

SELECT transactionid
FROM pg_locks;

There is no lock on hold in the list of locks transaction id.

From the doc:

Every transaction holds an exclusive lock on its virtual transaction ID for its entire duration. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on its permanent transaction ID until it ends. When one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID (either virtual or permanent ID depending on the situation). That will succeed only when the other transaction terminates and releases its locks.

You can try to search for the locks you need by pid:

SELECT * FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'advisory';