Using PostgreSQL advisory locks to control concurrency

We often need to control concurrent access to databases in Ruby on Rails applications. We need to make sure that a piece of code will run synchronously, to guarantee atomicity of multiple non-atomic operations.

PostgreSQL provides various techniques to control concurrent access to data. For example, we can use the Serializable transaction isolation level. In that case, any concurrent execution of a set of Serializable transactions is guaranteed to produce the same effect as running them one at a time in some order.

There are also explicit row-level locks that prevent two transactions from modifying a resource in conflicting ways. We can choose pessimistic locking if a database transaction conflict is very likely to happen. It locks the record until the transaction is done, and any other transaction trying to acquire the same lock has to wait until the lock is released.

Otherwise, we can use optimistic locking. It uses a version number of the record to check whether another process has made changes to it since it was opened, and throws an exception if that has occurred and the update is ignored.

Another way to control concurrency is advisory locking.

Advisory locking

Advisory locks by PostgreSQL are not tied to database tables or rows. We can use them, for example, to restrict the concurrency of a specific code in our application that works with a record from a specific table, and the other parts of our application that are accessing that table wouldn't notice that.

Advisory locks are application enforced locks. That means PostgreSQL isn't using them implicitly, and it is up to the application to call provided statements to obtain and release these locks, and to give a meaning to these locks.

We can even use advisory locking to control synchronization of a code that isn't managing data from the database - for example, to make API calls, access files on a disk, or anything else.

Advisory locks can be acquired at session level or at transaction level. The PostgreSQL documentation explains the difference:

Once acquired at session level, an advisory lock is held until explicitly released or the session ends. Unlike standard lock requests, session-level advisory lock requests do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback, and likewise an unlock is effective even if the calling transaction fails later. A lock can be acquired multiple times by its owning process; for each completed lock request there must be a corresponding unlock request before the lock is actually released.

Transaction-level lock requests, on the other hand, behave more like regular lock requests: they are automatically released at the end of the transaction, and there is no explicit unlock operation. This behavior is often more convenient than the session-level behavior for short-term usage of an advisory lock.

There are multiple functions provided by PostgreSQL to acquire and release exclusive or shared, session or transactional level advisory locks. Again, check out the official documentation for more information.

Here are some of the provided functions for session level locks:

• pg_advisory_lock(key bigint) obtains exclusive session level advisory lock. If another session already holds a lock on the same resource identifier, this function will wait until the resource becomes available. Multiple lock requests stack, so that if the resource is locked three times it must then be unlocked three times.
• pg_try_advisory_lock(key bigint) obtains exclusive session level advisory lock if available. It's similar to pg_advisory_lock, except it will not wait for the lock to become available - it will either obtain the lock and return true, or return false if the lock cannot be acquired immediately.
• pg_advisory_unlock(key bigint) releases an exclusive session level advisory lock.

And here are some for transaction level locks:

• pg_advisory_xact_lock(key bigint) obtains exclusive transaction level advisory lock. It works the same as pg_advisory_lock, except the lock is automatically released at the end of the current transaction and cannot be released explicitly.
• pg_try_advisory_xact_lock(key bigint) obtains exclusive transaction level advisory lock if available. It works the same as pg_try_advisory_lock, except the lock is automatically released at the end of the transaction and cannot be released explicitly.

A simple LockManager

We can use advisory locks in Ruby on Rails by creating a small class responsible for obtaining and releasing the lock. Let's call it the LockManager.

One small inconvenience is that advisory locks in PostgreSQL use a number as an argument. However, locking a specific number isn't too readable and self-explanatory. It would be better if we could use a string for this purpose.

A possible solution is to use the Zlib.crc32 hash function. It's commonly used for checksums, but we can use it to convert any string into the number required by PostgreSQL.

class LockManager
  def self.with_transaction_lock(lock_name)
    lock_id = Zlib.crc32(lock_name.to_s)
    ActiveRecord::Base.transaction do
      ActiveRecord::Base.connection.execute("SELECT pg_advisory_xact_lock(#{lock_id})")
      yield
    end
  end

  def self.with_session_lock(lock_name)
    lock_id = Zlib.crc32(lock_name.to_s)
    begin
      ActiveRecord::Base.connection.execute("SELECT pg_advisory_lock(#{lock_id})")
      yield
    ensure
      ActiveRecord::Base.connection.execute("SELECT pg_advisory_unlock(#{lock_id})")
    end
  end
end

And, for example, we can use this class in the following way:

LockManager.with_transaction_lock("subscription_usage_#{user_id}") do
  user = User.find(user_id)
  new_usage = user.current_subscription_usage
  user.update_attributes!(subscription_usage: new_usage)
end

Original article: Using PostgreSQL advisory locks to control concurrency