From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mike.adelson314@gmail.com
Subject: BUG #17686: SELECT pg_advisory_lock(...) with low lock_timeout sometimes times out but the lock is acquired
Date: Sat, 12 Nov 2022 00:41:43 +0000
Message-ID: <17686-fb1fa3870138e394@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mike.adelson314@gmail.com
Subject: Re: BUG #17686: SELECT pg_advisory_lock(...) with low lock_timeout sometimes times out but the lock is acquired
In-Reply-To: <17686-fb1fa3870138e394@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox