From: Jannis Harder <me@jix.one>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Warn against transaction level advisory locks for emulating "flat file" DBMS locking strategies
Date: Wed, 8 Sep 2021 19:21:16 +0200
Message-ID: <c70adbd8-d80b-b0ff-8b9e-a0d424d995fc@jix.one> (raw)
In-Reply-To: <3433006.1631119060@sss.pgh.pa.us>
References: <163108943900.696.7027791431029866787@wrigleys.postgresql.org>
<3433006.1631119060@sss.pgh.pa.us>
On 08.09.21 18:37, Tom Lane wrote:
> PG Doc comments form <noreply@postgresql.org> writes:
>> The docs mention "For example, a common use of advisory locks is to emulate
>> pessimistic locking strategies typical of so-called “flat file” data
>> management systems" which is exactly what I wanted to use to port some code
>> from using SQLite to using PostgreSQL. (The code in question requires
>> serializable transactions and cannot not handle retries.)
>
> Hmm. I'm afraid you're out of luck on that combination of requirements:
If it's not possible even with conservative locking using advisory
locks, the current phrasing of emulating "flat file" databases certainly
seems a bit misleading to me, as these are exactly the guarantees those
offer (or at least several of them do).
> The approximation is conservative in the sense
> that it won't let any actual failures get by; but it may produce false
> positives. We haven't felt this is a problem, because if you're using
> this stuff in the first place, you likely have *actual* anomaly hazards
> and thus need the retry logic anyway.
Is the approximation so conservative that it can fail even when there is
just a single transaction at a time? What about multiple "SERIALIZABLE
READ ONLY DEFERRABLE" transactions concurrent to at most a single
writing transaction? Those are question I now have and would love to see
answered by the documentation.
My testing with session level advisory locks indicates that this works
fine without serialization failures, but of course that testing is far
from exhaustive.
> The advice in the manual is thinking about READ COMMITTED mode, where
> I think this should work fine. It is a bit problematic in serializable
> mode, because when you do "SELECT pg_advisory_lock...", the SELECT will
> acquire the transaction snapshot before getting the lock.
Yes that's roughly what I assumed was happening (modulo not knowing any
details of the implementation) given the behavior I saw, and how those
failures did not happen when taking a session level lock before
beginning a transaction.
> What I'm inclined to think here is that maybe the docs are not
> sufficiently vocal about the fact that you can't avoid serialization
> failures altogether.
If even taking a session level lock before any writing transaction,
combined with "SERIALIZABLE READ ONLY DEFERRABLE" for all reading
transaction is not sufficient to avoid this, I would certainly agree.
Again, especially because of the comparison to "flat file" databases
where this can be avoided.
Thank you for the clarifications!
Best Regards,
Jannis
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-docs@postgresql.org
Cc: me@jix.one, tgl@sss.pgh.pa.us, pgsql-docs@lists.postgresql.org
Subject: Re: Warn against transaction level advisory locks for emulating "flat file" DBMS locking strategies
In-Reply-To: <c70adbd8-d80b-b0ff-8b9e-a0d424d995fc@jix.one>
* 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