Search code examples
postgresqllocking

Acquiring advisory locks in postgres

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?

Solution

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

    Check out more in the docs.