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

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