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

Started by PG Bug reporting formover 3 years ago5 messagesbugs

noreply@postgresql.org

over 3 years ago

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

Tom Lane

tgl@sss.pgh.pa.us

over 3 years ago

In reply to: PG Bug reporting form (#1)

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

PG Bug reporting form <noreply@postgresql.org> writes:

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).

Yeah, there's a fairly basic race condition there, which is that the
lock might get granted to us immediately after the timeout fires.
In ordinary usage this isn't very problematic because the lock would
get released anyway during the transaction abort resulting from the
timeout error. However, when you're asking for a session-level
advisory lock, that doesn't happen.

I spent a little time studying whether there's a way to close the race,
but if it's possible at all it'd take major restructuring of what's
already quite complex and delicate code. I can't get excited about
putting such effort into it, because related problems will exist
no matter what: if you get an error from the pg_advisory_lock command,
was the lock granted before that error occurred? I don't think there's
any bulletproof way to deal with that except to check the lock status
afterwards (e.g., via pg_locks). It's very much like the inherent issue
with errors late in COMMIT --- there's an atomic point where the commit
is logged, but the error report isn't going to be very clear about
whether we got past that, if indeed the error report gets to the client
at all.

regards, tom lane

Mike Adelson

mike.adelson314@gmail.com

over 3 years ago

In reply to: Tom Lane (#2)

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

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style></style></head><body lang=EN-US link=blue vlink="#954F72" style='word-wrap:break-word'><div class=WordSection1>Thanks for looking into this Tom,<o:p></o:p><o:p> </o:p>Glad to have the race condition confirmed. In my view the “real” fix here would be to offer an advisory locking function that accepts a timeout. FWIW, similar functionality on other RDBMS systems (SQL Server, MySQL, Oracle) offers this. That way, users wouldn’t have to rely in SET LOCAL lock_timeout to get this functionality. Thoughts? Is there a backlog item for this or has the idea been considered previously?<o:p></o:p><o:p> </o:p>-Mike<o:p> </o:p><div style='mso-element:para-border-div;border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'>From: <a href="mailto:tgl@sss.pgh.pa.us">Tom Lane</a> Sent: Saturday, November 12, 2022 11:47 AM To: <a href="mailto:mike.adelson314@gmail.com">mike.adelson314@gmail.com</a> Cc: <a href="mailto:pgsql-bugs@lists.postgresql.org">pgsql-bugs@lists.postgresql.org</a> Subject: Re: BUG #17686: SELECT pg_advisory_lock(...) with low lock_timeout sometimes times out but the lock is acquired</div><o:p> </o:p>PG Bug reporting form <noreply@postgresql.org> writes:> 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).<o:p> </o:p>Yeah, there's a fairly basic race condition there, which is that thelock might get granted to us immediately after the timeout fires.In ordinary usage this isn't very problematic because the lock wouldget released anyway during the transaction abort resulting from thetimeout error. However, when you're asking for a session-leveladvisory lock, that doesn't happen.<o:p> </o:p>I spent a little time studying whether there's a way to close the race,but if it's possible at all it'd take major restructuring of what'salready quite complex and delicate code. I can't get excited aboutputting such effort into it, because related problems will existno matter what: if you get an error from the pg_advisory_lock command,was the lock granted before that error occurred? I don't think there'sany bulletproof way to deal with that except to check the lock statusafterwards (e.g., via pg_locks). It's very much like the inherent issuewith errors late in COMMIT --- there's an atomic point where the commitis logged, but the error report isn't going to be very clear aboutwhether we got past that, if indeed the error report gets to the clientat all.<o:p> </o:p> regards, tom lane<o:p> </o:p></div></body></html>

Tom Lane

tgl@sss.pgh.pa.us

over 3 years ago

In reply to: Mike Adelson (#3)

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

Mike Adelson <mike.adelson314@gmail.com> writes:

Glad to have the race condition confirmed. In my view the “real” fix
here would be to offer an advisory locking function that accepts a
timeout.

How would that remove the low-level race condition?

regards, tom lane

Mike Adelson

mike.adelson314@gmail.com

over 3 years ago

In reply to: Tom Lane (#4)

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