PostgreSQL: How to use with_advisory_lock to prevent race conditions

If you want to prevent that two processes run some code at the same time you can use the gem with_advisory_lock Show archive.org snapshot .

What happens

1. The thread will wait indefinitely until the lock is acquired.
2. While inside the block, you will exclusively own the advisory lock.
3. The lock will be released after your block ends, even if an exception is raised in the block.

This is usually required if there is no suitable database row to lock on.

Example

You want to generate an email address with 2 random numbers in it. The email addresses should be unique. Therefore the random numbers should be regenerated, if the same email address exists already.
To prevent that the same email is generated at the same time, you should lock that code as follows:

Copydef generate_email
  random_numbers = rand(100).to_s.rjust(2, '0')
  email = "#{name}#{random_numbers}@example.de"

  if Customer.where(email: email).present?
    generate_email
  else
    email
  end
end

def set_email
  with_advisory_lock("email for #{name}", transaction: true) do
    self.email = generate_email
  end
end

Note that you need to set the option transaction: true, to remain the lock until the transaction completes. This option is supported by PostgreSQL.

Raising an error if the lock was not able to be acquired in time

When using with_advisory_lock you can also specify a timeout in which the lock has to be acquired via the timeout_seconds option. By default this option is nil which results in waiting for the lock indefinitely.

If you specify a timeout you most likely want to use with_advisory_lock! instead of with_advisory_lock. The difference is that when a lock was not able to be acquired in time with_advisory_lock! will raise an error whereas with_advisory_lock would return false.

Copy# returns false if the lock was not able to be acquired within 10 seconds
ApplicationRecord.with_advisory_lock('my-lock', timeout_seconds: 10) do
  some_method_call
end

# raises WithAdvisoryLock::FailedToAcquireLock if the lock was not able to be acquired within 10 seconds
ApplicationRecord.with_advisory_lock!('my-lock', timeout_seconds: 10) do
  some_method_call
end

Note

with_advisory_lock! was added in version 5.0.0 of the gem.