CODEWITHSUNDEEP
databasepostgresqltransactionslocking
Anton Komarev
Anton Komarev

Reputation: 48

Get transactionid in Postgres Advisory Lock within transaction

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

Upvotes: 1

Views: 832

Answers (2)

TheNord
TheNord

Reputation: 73

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 *
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.

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

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

Upvotes: 2

Daniel Vérité
Daniel Vérité

Reputation: 61546

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;

Upvotes: 1

Related Questions