How do PostgreSQL advisory locks work

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

PostgreSQL, like many modern RDBMS, offers both MVCC (Multi-Version Concurrency Control) and explicit pessimistic locking for various use cases when you want a custom concurrency control mechanism.

However, PostgreSQL also offers advisory locks which are very convenient to implement application-level concurrency control patterns. In this article, we are going to explain how PostgreSQL advisory locks work and how you should use them.

Exclusive and Shared advisory locks

Just like explicit pessimistic locks, advisory locks can be split into two categories:

  • exclusive advisory locks
  • shared advisory locks

Exclusive advisory locks

An exclusive advisory lock will block any exclusive or shared advisory lock on the same lock key.

Advisory lock exclusive locks shared

Shared advisory locks

A shared advisory lock will block any exclusive advisory lock on the same lock key while still allowing other shared advisory locks to be acquired for the same lock key.

Advisory lock share DB locks exclusive

Session-level and Transaction-level advisory locks

Based on their scope, there are two types of advisory locks that you can acquire in PostgreSQL:

  • Session-level advisory locks
  • Transaction-level advisory locks

The Session-level locks are not bound to any database transaction and, once acquired, they need to be explicitly released by the application developer. Transaction-level advisory locks are bound to the currently executing transaction, and so the locks are released when the transaction ends, either with a commit or a rollback.

Session-level advisory locks

PostgreSQL defines multiple API functions which allow you to acquire a session-level advisory lock:

void pg_advisory_lock(bigint key)
void pg_advisory_lock(int… key)

boolean pg_try_advisory_lock(bigint key)
boolean pg_try_advisory_lock(int… key)

void pg_advisory_lock_shared(bigint key)
void pg_advisory_lock_shared(int… key)

boolean pg_try_advisory_lock_shared(bigint key)
boolean pg_try_advisory_lock_shared(int… key)

Every lock is associated with an identifier, which can be a 32-bits integer or a 64-bits bigint. While the pg_advisory_lock method will block the currently executing thread if the advisory lock is already acquired by a different Session, the try_ variants return right away and you can use the boolean result value to verify if the lock has been successfully acquired.

Session-level advisory locks need to be released explicitly. Even if advisory locks are reentrant, if you acquired the same lock twice, you need to release it twice as well.

To release a Session-level advisory lock, you can use one of the following PostgreSQL functions:

void pg_advisory_unlock(bigint key)
void pg_advisory_unlock(int… key)

void pg_advisory_unlock_shared(bigint key)
void pg_advisory_unlock_shared(int… key)

void pg_advisory_unlock_all()

Transaction-level advisory locks

To acquire a transaction-level advisory lock, you need to use one of the following PostgreSQL functions:

void pg_advisory_xact_lock(bigint key)
void pg_advisory_xact_lock(int… key)

boolean pg_try_advisory_xact_lock(bigint key)
boolean pg_try_advisory_xact_lock(int… key)

void pg_advisory_xact_lock_shared(bigint key)
void pg_advisory_xact_lock_shared(int… key)

boolean pg_try_advisory_xact_lock_shared(bigint key)
boolean pg_try_advisory_xact_lock_shared(int… key)

You don’t have to release these locks since they are automatically released at the end of their parent transaction.

Application-level Concurrency Control

Now that you understand what advisory locks are and how you can acquire and release them, it’s time to see when you need to use them. It turns out that advisory locks are very suitable for implementing various application-level concurrency control mechanisms.

For instance, let’s assume we need to coordinate access to a shared document storage. Since we have multiple nodes that can read and write these documents, we need a way to prevent both file corruption as well as reading intermediary results.

Advisory lock network file system no lock

Typically, this task is very well suited for a read/write locking mechanism. However, we cannot use a Java ReadWriteLock since that would only work for a single JVM (a single node).

So, we need a way to coordinate all nodes that read and write documents, and PostgreSQL advisory locks are a very good fit for this task.

Advisory lock network file system lock

This way, whenever we want to write a document, we need to acquire an exclusive lock first. Once we acquired the exclusive lock, no other concurrent Session can acquire a shared or an exclusive lock for this document, so we block both reads and writes. When we are done writing the document, we release the exclusive lock.

If we want to read a document, we need to acquire a shared lock for the duration of the read operation to block other concurrent Sessions from writing the same document while we are still reading it.

Why not use row-level locks instead?

Although PostgreSQL offers the possibility to acquire exclusive and shared locks on a per table row basis, this is not always convenient. What if we stored the document metadata in Apache Cassandra? To use row-level locks, we need to store a row for each document that we administer. But if we have billions of documents, we don’t want to end up with a billion-row table whose only purpose is to allow us to acquire a shared or an exclusive lock on a per-row basis.

Online Workshops

If you enjoyed this article, I bet you are going to love my upcoming Online Workshops!

Conclusion

PostgreSQL advisory locking is a very useful concurrency control mechanism, and you should definitely use it to coordinate access among multiple microservices.

Transactions and Concurrency Control eBook

5 Comments on “How do PostgreSQL advisory locks work

  1. Hi Vlad,

    What happens if the PG Server crashes and comes up again? Will the registration of advisory locks be lost, and if yes, will clients that were holding those locks still think they hold them, while PG server is already giving them to other clients after its crash?

    • If the DB server goes down, the locks will be lost, and the client session will be disconnected. So, there connection will throw exception on every operation, and you’ll have to acquire a new connection to the new database primary node.

  2. Hi Vlad,
    while with PG advisory locks you do not need to create a table row for each (logical) lock, the thread holding the lock as well as all the threads waiting for the lock would still each need a DB connection, right?
    Considering this, would advisory locks still be a viable means for implementing a distributed lock manager (DLM)? How (well) does PG handle a great number of idle connections waiting for locks to be released?

    Merry Christmas & a happy new year

    Best,
    Frank

    • Yes, a database connection is indeed needed. PostgreSQL does not support a large number of DB connections as they are associated with OS processes. So, this is more useful for a reasonable enterprise system where multiple services try to coordinate reads and writes. There are other distributed lock managers that could scale better.

    • would advisory locks still be a viable means for implementing a distributed lock manager (DLM)?

      Any lock-managing service implemented on top of PG is a centralized (not distributed) service because it has one node (the PG instance that manages locks) that once failed renders the whole service unavailable.

      Anyway, if you need a lock manager (whether distributed or not does not matter much) to control access to resources external to this lock manager (e.g. use a PG instance to control access to data that is not stored in that same PG instance), then you are almost certainly doing something wrong. Here are a few things off the top of my head that can and will go wrong:

      an assumption that a write action to an external resource while holding a lock is guaranteed to be observed by a read action from an external resource that also holds the lock if the reader acquired the lock after the writer released it is almost certainly incorrect;
      an assumption that after a lock is acquired by a process, that process is guaranteed to still be holding it when accessing an external resource is incorrect: because the external resource is not accessed via the same PG session that was used to acquire a lock (whether session-level lock or transaction-level lock does not matter), it is impossible to guarantee that the process always detects an accidental unlock (which happened e.g. either because the session was terminated due to a connection problem or because of the idle_in_transaction_session_timeout has passed) before accessing the external resource that is protected by the lock.

      P.S. A lock manager (LM) regardless of its nature cannot be used for controlling access to resources external to the lock manager in situations when such controlling is required to implement safety properties (in other words, correctness) of an algorithm. It still can be used when it is done for efficiency (e.g. pessimistic concurrency control may reduce the amount of work that is thrown away when an optimistic approach is used in a situation with high contention). An obvious litmus test to differentiate between using an LM for safety vs efficiency is the following: your algorithm still has to be correct even if you simply remove an LM from it, but it is allowed to be less efficient.

      P.P.S I know there are many people who use LMs for controlling access to external resources for correctness, and there are many people claiming that they provide LMs that can be used for such purposes, but unfortunately, they probably have no idea of what they are talking about and their solutions are strictly speaking incorrect (just take a specific one and check whether the two aforementioned assumptions are both guaranteed to hold in that solution).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Hypersistence Optimizer 2.2 has been released!