CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordlocking
Niels B.
Niels B.

Reputation: 6310

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

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.

Upvotes: 20

Views: 27894

Answers (5)

Max Wallace
Max Wallace

Reputation: 3758

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.

Upvotes: 25

Ehud
Ehud

Reputation: 105

you don't have to lock the hall table to lock a piece of code for a single process at a time. locking a full table causes performence problems.you can lock a single same row all the time with "with_lock" method.this way code is fully protected. no extra gem is needed. it also creates a transaction. like this: m = Model.order(:id).first m.with_lock do #aquire lock #some code here for a single process at a time
end #release lock

Upvotes: 2

TerryYin
TerryYin

Reputation: 172

Well, technically it's the same to lock a table or to always lock a record of another table before accessing the table.

So you may have another table with max one record, alway lock that record with http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html before read/write from the table you want to lock:

LockTable.last.with_lock do
  // the things that needed for your table
end

Upvotes: -3

kaspernj
kaspernj

Reputation: 1263

There are already answers on how to lock the entire table, but I believe you should try to avoid that. Instead I believe you should give advisory locks a look. It makes sure the same block of code isn't executed on two machines simultaneously, while still keeping the table open for other business.

It still uses the database, but it doesn't lock your tables.

You can use the gem called "with_advisory_lock" like this:

Model.with_advisory_lock("ADVISORY_LOCK_NAME") do
  # Your code
end

https://github.com/ClosureTree/with_advisory_lock

It doesn't work with SQLite.

Upvotes: 13

Nikita Shilnikov
Nikita Shilnikov

Reputation: 1204

Setting unique constraint IS NOT a hack. It is thing that makes your data consistent. By the way you have a few more options here:

  1. Lock some DB resource (e.g. it could be a unique record) using SELECT FOR UPDATE or PostreSQL's Advisory Locks (see docs).

  2. Use a sequence (docs).

The main difference between two approaches is #1 does not allow to have gaps in your numbers because other session will wait for transaction commit and #2 allows.

Upvotes: 6

Related Questions