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
