Database locks is a powerful feature that allows to manage concurrent access to different types of database resources. Each modern database management system provides its own set of locking mechanisms and so does Postgres. In this article I’ll describe one of the specific lock types in Postgres - advisory locks.

Introduction to locks

Let’s start with the general description of what lock is and what types of locks exist. Database lock is a mechanism to provide data consistency when concurrent transactions are trying to access the same database resources at the same time. A lock can prevent access to the resource or allow it depending on the type of operation (read, write). Thus, a locking mechanism is essential for DBMS to provide ACID guarantees for their transactions [1].

Database locks can be classified in several different ways. And first one is the way by which the lock was obtained. Lock can be explicit and implicit. It’s often said that explicit locks are obtained by the “developers”, but it would be more accurate to say that they are obtained by the database client application. Implicit locks, on the other hand, are automatically obtained by the database server to provide data integrity. This article is dedicated to explicit locks, but it is worth mentioning that implicit locks occur much more often and sometimes it’s good not to forget about it.

Implicit locks in Postgres occur while executing SELECT, INSERT, CREATE INDEX, ALTER TABLE, TRUNCATE, DROP TABLE, VACUUM and many other commands.

Another way to classify locks is by the type of resources they are acquired on. It may be:

• row-level - applies on the individual rows in the table
• table-level - applies on the whole table
• page-level - used to control read/write access to table pages in the shared buffer pool
• database-level - applies on the whole database

Some of the database servers can provide more types here depending on the inner entities existing in the particular database.

Also locks differ by their exclusiveness. The level of exclusiveness of the lock determines whether an acquired lock allows other locks to be obtained on the same resource. In general locks can be exclusive and shared. When the exclusive lock is acquired, the database doesn’t allow any other transaction to acquire any other lock on this resource, while the shared lock can be acquired several times on the same resource by several different transactions. But there are also tens of possible types that lie between these two extremes and each DB system provides its own set of lock modes.

So, what is an advisory lock?

Postgres offers a special type of lock that is completely driven by the client application. A client controls when to set up this lock and when to release it. This type of lock is called advisory [2]. To acquire this type of lock client should choose a unique key (single 64-bit value or two 32-bit values) and pass it into one of the Postgres advisory lock functions [3].

What is the purpose of advisory locks? As Postgres documentation says, it is intended to be used to lock application-defined resources. Usually, such resources have their direct analogues in the database, e.g. domain entities map to database rows. But sometimes there may be exceptions (we’ll look at them later) and application-defined resources have no analogue in the database. And an advisory lock is a good fit for such cases. The key mentioned earlier is the identifier of such a resource and used by Postgres to find other concurrent transactions that acquire locks on the same resource.

Let’s return to our classification. Advisory locks are obviously explicit as they are initiated by the client application and there is no other way to obtain them. It’s getting more interesting when we talk about the type of resource it’s acquired on. As it’s intended to be used for custom resources, these resources can be of any type. It may be a table, a row, or a group of table rows even from several tables. There even may be no underlying database resource at all, an advisory lock can be used to manage access to resources which are stored only in memory or in another database.

From the exclusiveness point of view advisory locks can be shared or exclusive. Shared locks (initiated by the pg_advisory_lock_shared function) don’t conflict with other shared locks, they conflict only with exclusive locks. At the same time exclusive locks (initiated by the pg_advisory_lock function) conflict with any other lock with the same key, both exclusive and shared.

Another feature of Postgres advisory locks is a behavior control on conflict. It allows a different behavior when the lock for the given identifier is already held. It can wait until the resource is unlocked and available (using pg_advisory_lock function) or just return false and continue execution (using pg_try_advisory_lock, pg_try_advisory_lock_shared, pg_try_advisory_xact_log functions).

Session and transaction-level locks

There is another important attribute that can be different for advisory locks. These locks can be obtained on two different levels: on session and transaction levels. Session-level locks (pg_advisory_lock function) do not depend on current transactions and are held until they are unlocked manually (with pg_advisory_unlock function) or at the end of the session. Transaction-level logs (pg_advisory_xact_log function) behave more familiar for those who use Postgres row locks - they live until the end of the transaction and don’t require manual unlocking.

And what does the “session” mean in this context? In Postgres the session is the same as a database connection. As connections can be shared between several processes, there is a possible issue with session-level locks. When a process that locked the resource dies before it called unlock, the session is not closed, because the connection is used by other processes. It means that the lock will be held until the connection is closed, which can take a long time. Thus, I would recommend using transaction-level locks whenever it’s possible to avoid such problems.

Working with PgBouncer is another nuance [4]. The majority of the relatively high load projects use Postgres with some kind of connection poolers and PgBouncer is the most popular of them. As you may know, PgBouncer has several modes of connection rotation. And one of them is transaction pooling [5]. In this mode, a server connection is assigned to a client only during a transaction. When PgBouncer notices that the transaction is over, the connection will be returned into the pool. It also makes it impossible to use session-level advisory locks.

Use Cases

Aforementioned features give developers a lot of flexibility in possible use cases. I would mention just some of them:

• manage concurrent inserts into a single table - instead of locking the whole table a client can create a lock with the more specific identifier - it may be foreign key, or some combination of fields
• lock table for a single operation, but allow other operations go on. Example: you want an operation to work with the table exclusively, not allowing the same operation to run at the same table; at the same time, other operations shouldn’t be blocked and should run in parallel. It may be step-by-step table processing or analytical processes. Using advisory locks in such cases will help to prevent race conditions.
• lock set of rows stored in separate tables. In this case you can acquire locks on each record separately, but sometimes it’s better to treat them as a single set
• multi-thread table processing, e.g. when you use your table as a queue (generally not recommended)
• distributed locking (but usually there are better alternatives [6])

Example

As an example I would take an implementation of a simple rate-limiting system inside a single table. Let’s say we have a model Like with the fields user_id and object_id and a business rule that allows a single user to like something only 20 times within an hour. Pseudocode:

transaction do 
    likes_count = get_hour_likes_by_user_id(user_id)
    if likes_count < 20
        create_like(user_id, object_id)
    end
end

This code will obviously fail to achieve a task because of concurrent requests. There is a possible race condition, as the likes count can be changed after fetching likes count but before creating a like. But this can be easily fixed by adding advisory lock to this transaction:

transaction do 
    lock_key = crc32(“user_likes_#{user_id}”)
    db_run(“select pg_advisory_xact_lock(#{lock_key})”)

    likes_count = get_hour_likes_by_user_id(user_id)
    if likes_count < 20
        create_like(user_id, object_id)
    end
end

crc32 function here is used to convert a string key to the integer using CRC algorithm [7].

Now the advisory lock guarantees that no new Like entity with the same user_id will be inserted into a table until the transaction commits and the lock is released.

Conclusion

As you can see, advisory locks is a very powerful and flexible tool provided to developers by the Postgres database. It can be adapted for plenty of possible scenarios. I will just leave several tips of using advisory locks here:

• use transaction-level locks instead of session-level if possible
• use CRC (or similar) algorithm to generate int keys from string - it will help to avoid locks with similar keys for different tables
• lock can be fetched from special Postgres table pg_locks by locktype = 'advisory'
• multiple locks with the same key stacked, so if you lock the resource several times you should unlock it the same number of times

Links

1. https://en.wikipedia.org/wiki/ACID
2. https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS
3. https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
4. https://www.pgbouncer.org/
5. https://www.pgbouncer.org/features.html
6. https://redis.io/docs/manual/patterns/distributed-locks/
7. https://en.wikipedia.org/wiki/Cyclic_redundancy_check