Good day,
I am working on a system which re-centralizes a distributed system to
publish the aggregated data somewhere.
We make heavy use of advisory locks to prevent race conditions in our
application.
We use the following bulk query as we sometimes need acquire multiple
locks at the same time and want to avoid round-trips to the database:
|WITH keys(key) AS (SELECT unnest(:keysToLock)) SELECT
pg_advisory_xact_lock(hashtextextended(key, 0)) FROM keys|
:keysToLock is a text[] parameter which is pre-sorted in our
application. This pre-sorting is done to prevent dead locks when two
concurrent transactions try acquire the same advisory locks (e.g.
[a,b,c] [b,a,c] can easily deadlock).
We thought this would be enough, but we occasionally still run into
deadlocks.
I tried to research this topic and learned that the SQL standard does
not guarantee the order of execution without ORDER BY, so I whipped up
the following variant:
|SELECT pg_advisory_xact_lock(hashtextextended(ordered_keys.key, 0))
FROM ( SELECT * FROM unnest(?) WITH ORDINALITY keys(key, index)
ORDER BY index ) ordered_keys|
Would this suffice? It's really difficult for me to find reliable
documentation about this topic.
A user on StackOverflow suggested this variant to create an
"optimization fence" so that the subquery cannot be flattened:
|SELECT pg_advisory_xact_lock(hashtextextended(ordered_keys.key, 0))
FROM (SELECT * FROM unnest(?) WITH ORDINALITY AS keys(key, index)
ORDER BY index /* a no-op, but it prevents subquery flattening */
OFFSET 0) AS ordered_keys;|
Somehow, wanting a guaranteed order of pg_advisory_xact_lock execution
turned out to be quite complicated.
So what is the correct way to do this? And I would love for some form of
documentation link to read up on this.
Thank you for your time,
Nico Heller
