Advisory Locks Part 2

Previously, I wrote about PostgreSQL Advisory Locks and how important and useful they are. One thing I forgot to mention is how in very specifc cases they can improve concurrency in your application. This is because they are both long-term (lock duration can exceed a single transaction) and short-term (lock duration can be shorter than a single transaction). This entry will describe the short-term aspect and what it can be used for.

PostgreSQL uses a high performance, high concurrency MVCC engine to manage mutiple sessions reading and writing to the database. All locks in PostgreSQL use this engine and must obey transaction sementics -- except for advisory locks. While advisory locks can be used for various tricks, their most important role is to provide a database managed mutex of precisely controlled duration irresepctive of what else is going on in the system at the time. The obvious use for this is for long term pessimistic locks, but how else might they be used?

One scenario is to manage contention to resources that also do not follow transactional semantics. One such resource is a sequence -- even if in a serializable transaction other sessions can access that resource and alter your expected results. For example, if you pulled 10 values from a sequence in a pl/pgsql FOR loop, there is no guarantee that those values are contiguous, except to lock a resource before the first value is fetched and hold that lock for the duration of the transaction. Advisory locks provide the tools to give that guarantee with a shorter term lock.

Imagine you had a pl/pgsql function that was long or was part of a transaction that was long and had the following requirements:

• the sequence s must be incremented
• after incrementing s, execute an operation with a possible (handled) failure condition. If that fails, return s to the value before incrementing
• until the failure state of the operation is checked, no other sessions must increment s

The third requirement is basically saying s must be locked inside this transaction. The standard way to do this is to lock something, either a table or a record. However, since all locks, once acquired, must be held until transaction terminatation (via COMMIT/ROLLBACK), our concurrency is poor especially if the lock is acuired early on and the transaction is long running, as there is no facility to unlock resources in-transaction.

Our transaction time line is:
B-TS----------C
B=Begin, S=increment S, C=Commit, T=Standard SQL transaction lock
using the lock, we have a mutex guarding S to C.

With advisory locks, a better solution emerges: lock some number that represents s and unlock that number once the failure state is checked and handled. Now our transaction timeline is:
B-LSU---------C
L=Advisory lock, U=Advisory unlock

Obviously, this lowers contention and improves concurrency. In certain rare cases, this can provide enormous benefits.