Locks in PostgreSQL: 3. Other locks (2020)

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.

Yes! Just implemented these the other day for a long running process that I didn't want to lock a specific row for.

They are great, although I wouldnt use the articles advice on using hashtext to get a number for the lock. This may cause collisions, especially when used with a large number of locks.

In a project Im working on we have a single go package that holds a list of all advisory lock numbers as constants.

These are great. Some time ago I was tasked with writing installation tooling for a startup's data analysis product, which was built as a distributed system. The system used a SQL database to store metadata, so every host needed the SQL database's connection details. Using an advisory lock to decide which host initializes the database schema made everything so much simpler - just install on all your hosts at once, in parallel, and don't worry about it.

This was MySQL but its advisory locks are pretty similar to Postgres.

It's also nice that the lock is released when the database connection terminates. Really easy to use. If you need exactly one of something running constantly, you can launch however many processes and let all but one spin trying to acquire the lock. When one dies and closes its SQL connection, thus releasing the lock, another will obtain the lock and begin work more or less instantly.

They're infinitely useful!

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

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.