Posted about 1 year ago. Visible to the public. Repeats.

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 Archive .

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:

Copy
def 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.

Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.

Owner of this card:

Avatar
Johanna Hartmann
Last edit:
about 1 month ago
by Henning Koch
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Johanna Hartmann to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more