Rails ActiveRecord - how can I lock a table for reading?

Question

I have some Rails ActiveRecord code that looks like this:

new_account_number = Model.maximum(:account_number)
# Some processing that usually involves incrementing
# the new account number by one.
Model.create(foo: 12, bar: 34, account_number: new_account_number)

This code works fine on its own, but I have some background jobs that are processed by DelayedJob workers. There are two workers and if they both start processing a batch of jobs that deal with this code, they end up creating new Model records that has the same account_number, because of the delay between finding the maximum and creating a new record with an even higher account number.

For now, I have solved it by adding a uniqueness constraint at database level to the models table and then retry by re-selecting the maximum in case this constraint triggers an exception.

However it feels like a hack.

Adding auto incrementing at database level to the account_number column is not an option, because the account_number assigning entails more than just incrementing.

Ideally I would like to lock the table in question for reading, so no other can execute the maximum select query against the table until I am done. However, I'm not sure how to go about that. I'm using Postgresql.

Answer 1

Based on the ActiveRecord::Locking docs it looks like Rails doesn't provide a built-in API for table-level locks.

But you can still do this with raw SQL. For Postgres, this looks like

ActiveRecord::Base.transaction do
  ActiveRecord::Base.connection.execute('LOCK table_name IN ACCESS EXCLUSIVE MODE')
  ...
end

The lock must be acquired within a transaction, and is automatically freed once the transaction ends.

Note that the SQL you use here will be different depending on your database.

---

Obviously locking the entire table is not elegant or efficient, but for small apps, for some time, it may indeed be the best solution. It's simple and easy to reason about. In general, an advisory lock is a better fit for this kind of data race.