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

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/explicit-locking.html
Description:

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

The next paragraph explains session and transaction level advisory locks and
mentions that transaction level locks are "often more convenient than the
session-level behavior". This seemed to be true for this use case, so I
chose to use them.

Later I discovered that obtaining a transaction level lock as first
statement _within_ a transaction is not sufficient to emulate global
pessimistic locking and can occasionally still result in serialization
failures. While this makes sense to me after having discovered those
serialization failures and spending some time debugging my code to make sure
I am really using the locks as I intended, it was not obvious to me before.
Without knowing details of how transactions are implemented it seemed
equally plausible that acquiring the lock before the first statement that
accesses or modifies any data is sufficient. Given that transaction level
advisory locks exist and were introduced right after mentioning this use
case, I assumed this was one of their intended use cases.

I would thus suggest to add a warning, e.g.: "Note that this requires
session level locks acquired before beginning a transaction." after "For
example, a common use of advisory locks is to emulate pessimistic locking
strategies typical of so-called “flat file” data management systems." or to
mention this pitfall when both variants are introduced in the next
paragraph.

Best Regards,
Jannis

On Wed, 2021-09-08 at 08:23 +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/13/explicit-locking.html
> Description:
> 
> 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.)
> 
> The next paragraph explains session and transaction level advisory locks and
> mentions that transaction level locks are "often more convenient than the
> session-level behavior". This seemed to be true for this use case, so I
> chose to use them.
> 
> Later I discovered that obtaining a transaction level lock as first
> statement _within_ a transaction is not sufficient to emulate global
> pessimistic locking and can occasionally still result in serialization
> failures.

I don't see how that is related to session-level vs. transaction level locks.

erhaps you can explain your case in some more detail.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

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 you use serializable mode in Postgres, you had better be prepared to
retry serialization failures.  It's not optional, because even if the
client transactions theoretically can't cause serialization anomalies,
you can still get failures because our implementation analyzes anomaly
risks only approximately.  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.

> Later I discovered that obtaining a transaction level lock as first
> statement _within_ a transaction is not sufficient to emulate global
> pessimistic locking and can occasionally still result in serialization
> failures.

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.  So yeah,
this method won't keep you out of serialization anomalies --- but as
I explained above, you have a risk of those regardless.  (It's hard
to tell on the basis of what you've said whether the failures you saw
were due to this effect or were implementation-dependent false
positives.)

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.

            regards, tom lane

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