Advisory locks are a very useful feature in PostgreSQL, and they just aren’t used enough.
Here’s a scenario: You have a bulk import job. While that job is running, there’s an analysis job you want to prevent from starting, and you don’t want a bulk import to start while the analysis job is running. But any number of bulk importers can run at the same time. How do you communicate this?
With an advisory lock!
Each of the bulk importers can take a shared advisory lock. Those locks don’t block each other, so they can run freely. But the analysis job takes an exclusive advisory lock of the same type. It will wait until all the importers are done, and the importers will not be issued their shared locks until it completes.
Even better, PostgreSQL cleans them up for you when a session terminates; you don’t have to worry about a lock lingering when you didn’t mean it to.
To make using advisory locks easier in Django, I have a small context manager than can be used to wrap code that should run with an advisory lock held; it makes it much easier to use them in your application. You can find it on GitHub, and it installs using pip.
Comments
Adrian Klaver · 2 July 2013
Interesting. I am a little confused by this sentence though: "While that job is running, there’s an analysis job you want to suspend, and you don’t want a bulk import to start while the bulk import is running" If I am following correctly, should it not be: While that job is running, there’s an analysis job you want to suspend, and you don’t want a bulk import to start while the analysis job is running.Xof · 2 July 2013
I have no idea what you could possibly be referring to. :)plumqqz · 3 July 2013
You can do the same task using standard tools - select for share/update. And if you nevertheless want to use adivsory lock I think it would be more convenient to use pg_advisory_xact_lock etc.Xof · 3 July 2013
That's not correct. Row-level locks such as SELECT FOR SHARE and SELECT FOR UPDATE acquire work at a different logical level than an advisory lock. In my example of a bulk insert, a row level lock wouldn't do you any good, because you are inserting things into a table, so there's no generally-visible row to lock to signal the operation. Advisory locks can also span multiple transactions, which other kinds of locks cannot. That's a matter of taste, of course, but I think that being able to cleanly wrap the code that should have a lock applied to it, with the automatic cleanup on exit, is a big plus. Of course, if you *like* writing cursor.execute() statements, no one is stopping you. :)