locking rows on rails update to avoid collisions. (Postgres back end)

user_1023110 0
Viewed 3042

So I have a method on my model object which creates a unique sequence number when a binary field in the row is updated from null to true. Its implemented like this:

class AnswerHeader < ApplicationRecord
  before_save :update_survey_complete_sequence, if: :survey_complete_changed?

  def update_survey_complete_sequence
    maxval =AnswerHeader.maximum('survey_complete_sequence')
    self.survey_complete_sequence=maxval+1
  end
end

My question is what do I need to lock so two rows being updated at the same time don't end up with two rows having the same survey_complete_sequence?

If it is possible to lock a single row rather than whole table that would be good because this is a often accessed table by users.

rails-models rails-activerecord ruby-on-rails
5 Answers

If you want to handle this in application logic itself, instead of letting database handle this. You make make use of rails with_lock function that will create a transaction and acquire a row level db lock on the selected rows(in your case a single row).

user_41930430

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

user_18260580

What you need to lock

In your case you have to lock the row containing the maximum survey_complete_sequence, since this is the row every query will look for while getting the value you require.

maxval =AnswerHeader.maximum('survey_complete_sequence')

Is it possible to lock a single row rather than whole table

There is no such specific lock for your scenario. But you can make use of Postgresql's SELECT FOR UPDATE row-level locking.

To acquire an exclusive row-level lock on a row without actually modifying the row, select the row with SELECT FOR UPDATE.

And you can use pessimistic locking in rails and specify which lock you will use.

Call lock('some locking clause') to use a database-specific locking clause of your own such as 'LOCK IN SHARE MODE' or 'FOR UPDATE NOWAIT'

Here's an example of how to achieve that from rails official guide itself 

Item.transaction do
  i = Item.lock("LOCK IN SHARE MODE").find(1)
  ...
end

Relations using lock are usually wrapped inside a transaction for preventing deadlock conditions.

So what you need doing is -

  1. Apply SELECT FOR UPDATE lock to row consisting maximum('survey_complete_sequence')
  2. Get the value you require from that row
  3. Update your AnswerHeader with the value received
user_87743860

If you are using postgress, maybe Sequenced can help you out without defining a sequence at the DB level.

Is there a reason survey_complete_sequence should be incremental? if not, maybe randomize a bigint?

user_67670600

You probably don't want to lock the table, and even if you lock the row you're currently updating the row you're basing your maxval on will be available for another update to read and generate its sequence #.

Unless you have a huge table and lots of updates every millisecond (in the order of thousands) this shouldn't be an issue in real life. But if the idea bothers you, you can go ahead and add an unique index to the table on "survey_complete_sequence" column. The DB error will propagate to a Rails exception you can deal with within the application.

user_9527400
Related