To: Tom Lane <tgl@sss.pgh.pa.us>
References: <163108943900.696.7027791431029866787@wrigleys.postgresql.org>
 <3433006.1631119060@sss.pgh.pa.us>
From: Jannis Harder <me@jix.one>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Warn against transaction level advisory locks for emulating "flat
 file" DBMS locking strategies
Message-ID: <c70adbd8-d80b-b0ff-8b9e-a0d424d995fc@jix.one>
Date: Wed, 8 Sep 2021 19:21:16 +0200
MIME-Version: 1.0
In-Reply-To: <3433006.1631119060@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/c70adbd8-d80b-b0ff-8b9e-a0d424d995fc%40jix.one>
Precedence: bulk

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