Acquiring advisory locks in postgres

Question

I think there must be something basic I'm not understanding about advisory locking in postgres. If I enter the following commands on the psql command line client, the function returns true both times:

SELECT pg_try_advisory_lock(20); --> true
SELECT pg_try_advisory_lock(20); --> true

I was expecting that the second command should return false, since the lock should already have been acquired. Oddly, I do get the following, suggesting that the lock has been acquired twice:

SELECT pg_advisory_unlock(20); --> true
SELECT pg_advisory_unlock(20); --> true
SELECT pg_advisory_unlock(20); --> false

So I guess my question is, how does one acquire an advisory lock in a way that stops it being acquired again?

Answer 1

What if you will try doing this from the 2 different PostgreSQL sessions?

Check out more in the docs.

Answer 2

My first impression on advisory locks was similar. I expected the second query (SELECT pg_tryadvisory_lock(20)) to return false too (because the first one got the lock). But this query only confirmed that a bigInt with value 20 has a lock. The interpretation is up to the user.

Imagine the advisory locks as a table where you can store a value and get a lock on that value (normally a BigInt). It is no explicit lock and no transction will be delayed. It depends on you how to interpret and use the result - and it is not blocking.

I use it in my projects with the two-integers-options. SELECT pg_try_advisory_lock(classId, objId) whereas both parameters are integers.

To make it work with more than a table just use the OID of the table as classId and the primary id (here 17) as objId:

SELECT pg_try_advisory_lock((SELECT 'first_table'::regclass::oid)::integer, 17);

In this example "first_table" is the name of the table and the second integer is the primary key id (here: 17).

Using a bigInt as parameter allows a wider range of ids, but if you use it with "second_table" than the id 17 is locked as well (because you locked the number "17" and not the relation to a specific row in a table).

It took me some time to figure that out, so hopefully it helps to understand the inner-workings of advisory locks.