PostgreSQL Advisory Locks

It's a very serious issue, hard to debug and solve, especially in desktop apps. However, web developers sometimes also have to tackle the challenge of making multiple threads work nicely with each other. So, what exactly is the problem here?

Concurrent incrementation

The best example to describe the problem is concurrent incrementation. As they say, one image is worth a thousand words, so here's one:

As you can see, two threads are trying to increment the same variable and the result isn't initial value + 2, but initial value + 1. That happens because Thread B read operation gets in between Thread A read and write operations. What can be done to prevent this behavior?

Locks

The most commonly used tool to solve that issue is a lock. There are two main types of locks, pessimistic and optimistic.

The former can be thought of as a semaphore and works like this:

1. Access is given to thread A, the semaphore arm is lowered.

2. The object is modified.

3. After the thread A is done the arm is raised to give access to thread B.

The latter is a bit more complicated:

1. Version associated with the modified object is checked.

2. The object is modified.

3. The version is checked again to see if it has changed. If it hasn't, it is updated. If it has, the modification is reverted and based on developer's choice can be attempted again, or error can be raised, etc. The whole process must be atomic, i.e. thread safe, so nothing can get between operations of reading the version and updating it. Pessimistic locks are sometimes used to achieve that so you can have a pessimistic lock within an optimistic lock.

Optimistic locks are faster, since other threads don't wait for the whole "The object is modified" part to be done, which sometimes can take some time. However, they are also less reliable, since there's no guarantee that the operation will be successful. Most often they are used when the risk of threads getting in each other's way is low, but still can’t be ignored. Both optimistic and pessimistic locks have their subtypes, in this article we'll focus on pessimistic PostgreSQL advisory locks.

Advisory locks

Those locks are database based, stored in the pg_locks table. This simple SQL query will return currently obtained locks:

SELECT * FROM pg_locks;

Using database also has the advantage of being able to handle not only different threads, but also different processes. Locks are obtained on abstract numbers and what those numbers mean depends entirely on you. They can lock database objects with their associations, one global object or whatever you come up with and put between locking and unlocking functions.

Looking at pure SQL code is the best approach to understand how these locks work, so let's have look at some examples of obtaining and releasing locks with different levels and access types.

1. Session level

Session level means that lock is obtained for connection to the database. The lock requests can stack, so if you request a lock on the same number twice within the same connection, you'll get it twice and then you'll also have to unlock it twice before other connections can access it. Moreover, you can only release the lock within the same connection in which it was obtained. Looking at the examples below should make that clear.

1.1. Non-blocking

Non-blocking means that the function trying to obtain the lock will return immediately with a boolean value. Here are the functions we'll use:

• pg_try_advisory_lock(number1, [optional] number2) - used to obtain one instance of lock on one or two numbers

• pg_advisory_unlock(number1, [optional] number2) - used to release one instance of lock on one or two numbers

• pg_advisory_unlock_all - used to release one instance of every lock

Let's consider the following examples to illustrate how those locks work:

Session 1:

SELECT pg_try_advisory_lock(23); -- returns true, first instance of lock on number 23 was obtained
SELECT pg_try_advisory_lock(23); -- returns true, second instance of lock on number 23 was obtained
SELECT pg_advisory_unlock(23); -- returns true, one instance of lock on number 23 was released

Session 2:

SELECT pg_try_advisory_lock(23); -- returns false, there's still one locked instance left
SELECT pg_advisory_unlock(23); -- returns false, instance can't be released from another session

Session 1:

SELECT pg_advisory_unlock(23); -- returns true, last instance of lock on number 23 was released

Session 2:

SELECT pg_try_advisory_lock(23); -- returns true, one instance of lock on number 23 was obtained
SELECT pg_try_advisory_lock(1, 23); -- returns true, one instance of lock on numbers 1 and 23 was obtained
SELECT pg_advisory_unlock_all; -- returns nothing, but both instances above were released

1.2. Blocking

• pg_advisory_lock(number1, [optional] number2) - similar to pg_try_advisory_lock, but it doesn’t return anything, when a lock cannot be obtained it waits until it can

Session 1:

SELECT pg_advisory_lock(23); -- returns nothing, but finishes immediately which means that one instance of lock on number 23 was obtained

Session 2:

SELECT pg_advisory_lock(23); -- returns nothing and doesn't finish, waits for that one instance of lock on number 23 to be released

Session 1:

SELECT pg_advisory_unlock_all(); -- returns nothing, but the instance is released and the pg_advisory_lock function in Session 2 doesn't wait anymore, one instance of the lock is obtained for Session 2

Session 2:

SELECT pg_advisory_unlock_all(); -- returns nothing, but the instance obtained above for Session 2 is released

2. Transaction level

Transaction level advisory locks work within a scope of a single transaction and are released automatically when the transaction ends.

• pg_try_advisory_xact_lock(number1, [optional] number2) - used to obtain one instance of lock on one or two numbers

Transaction 1:

SELECT pg_try_advisory_xact_lock(23); -- returns true, one instance of lock on number 23 was obtained and released automatically if nothing more happened within the transaction

Transaction 2:

SELECT pg_try_advisory_xact_lock(23); -- returns true, since the release above was automatic, one instance of lock on number 23 was obtained and also released automatically

Transaction 1:

SELECT pg_try_advisory_xact_lock(23), pg_sleep(10); -- returns true, one instance of lock on number 23 was obtained and will be released automatically after 10 seconds

Transaction 2:

SELECT pg_try_advisory_xact_lock(23); -- if 10 seconds haven't passed yet, it will return false

Summary

So there you go, this is probably everything you need to know about PostreSQL advisory locks. If you don't want to implement them manually and you use Ruby, you can use with_advisory_lock gem, which does all the work for you. Have fun locking!