BUG #17686: SELECT pg_advisory_lock(...) with low lock_timeout sometimes times out but the lock is acquired - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17686
Logged by:          Mike Adelson
Email address:      mike.adelson314@gmail.com
PostgreSQL version: 12.2
Operating system:   Windows 10
Description:

I am trying to use the pg_advisory_lock function in combination with setting
lock_timeout to wait on the lock with a timeout.

Here is my query:
```
SET LOCAL statement_timeout = 0;
SET LOCAL lock_timeout = 200;
SELECT pg_catalog.pg_advisory_lock(12345)
```

I'm finding that with relatively small values of lock_timeout and when the
system is under load (e.g. 8 connections acquiring concurrently), I will
encounter a case where the query exits with state 55P03 (lock_not_available)
and yet the lock was actually acquired (I can tell it has been acquired by
querying pg_locks and because other connections' calls to pg_advisory_lock
block).

Here's a standalone repro application which reliably recreates the behavior
for me: https://github.com/Tzachi009/distributed-locks-issue
More discussion here: https://github.com/madelson/DistributedLock/issues/147