Acquiring advisory locks in postgres

Question

I think that there must be something basic, I do not understand about advisory locking in postgres. If I enter the following commands in 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 the second command to return false, since the lock should already be obtained. Oddly enough, I get the following, suggesting that the castle was purchased twice:

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

So, I think, my question is how to get an advisory lock so that it stops being received again?

Answer 1

What if you try to do this from two different PostgreSQL sessions?

More details in the docs .

Answer 2

My first impression of advisory locks was similar. I was expecting the second request (SELECT pg_tryadvisory_lock (20)) to also return false (because the first got the lock). But this request only confirmed that bigInt with a value of 20 has a lock. The interpretation is user dependent.

Imagine advisory locks in the form of a table in which you can save a value and get a lock on this value (usually BigInt). This is not an obvious lock, and no transmission will be delayed. It is up to you how to interpret and use the result - and it does not block.

I use it in my projects with two integer parameters. SELECT pg_try_advisory_lock (classId, objId), while both parameters are integers.

To make it work with more than a table, just use the table OID as classId and the main identifier (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 identifier of the primary key (here: 17).

Using the bigInt as parameter allows you to use a wider range of identifiers, but if you use it with "second_table", except that identifier 17 is also blocked (since you blocked the number "17", and not the relation to a specific row in the table).

It took me a while to figure this out, so hopefully this helps to understand the inner workings of advisory locks.