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

The following bug has been logged on the website:

Bug reference: 17686
Logged by: Mike Adelson
Email address: mike(dot)adelson314(at)gmail(dot)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