←back to thread

Locks in PostgreSQL: 3. Other locks (2020)

(habr.com)
60 points fanf2 | 1 comments | 07 Dec 25 15:42 UTC | HN request time: 0s | source
Show context
semiquaver ◴[07 Dec 25 16:06 UTC] No.46182699[source]▶
Don’t skip the discussion on advisory locks. In my experience nearly every nontrivial application that spans multiple machines has concurrency bugs that advisory locks are perfectly suited to fix.
replies(4): >>46183073 #>>46183165 #>>46183758 #>>46183971 #
sa46 ◴[07 Dec 25 18:45 UTC] No.46183971[source]▶
Advisory locks aren’t all sunshine and rainbows. They can only be unlocked by the Postgres connection that acquired the lock. That means you need to track the connection, typically by dedicating a connection to the job that needs locking.

Here’s a good issue describing the tradeoffs between a lock table and advisory locks.

https://github.com/bensheldon/good_job/discussions/831

replies(1): >>46187132 #
1. FreakLegion ◴[08 Dec 25 00:59 UTC] No.46187132[source]▶
Do people use advisory locks as the actual locking mechanism? I've always used them to synchronize access to a flag on the target resource, so the advisory lock is only held long enough to query or update that resource as locked. The alternative seems, yes, incredibly brittle.