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