Re: Warn against transaction level advisory locks for emulating "flat file" DBMS locking strategies

On 08.09.21 18:37, Tom Lane wrote:
> PG Doc comments form <noreply(at)postgresql(dot)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