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

Question

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.

Renferd

Is there a reason you can't create a Postgres sequence and let the DB handle it? – Synn

I could I guess, but I don't like spreading the application logic all over the place (i.e. some in the rails app, some in the DB itself, etc.). Seems like a maintenance headache. – Renferd

PS to anybody who has an answer I don't need really need a "credible source" I just need a solution that works. – Renferd

Answer 1

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

Bordiuk

Answer 2

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.

Geri

Answer 3

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

Possessory

The row lock will not help him. Even if the row is locked his application will read the maxval from that row or any other. – Inez

The application will only get the value if the lock is successful, when used like this AnswerHeader.lock('SELECT FOR UPDATE').maximum('survey_complete_sequence'). Are you saying something else? Or what I said is not what's happening? – Possessory

So if he locks row Y that contains the current maximum_value, how does that prevent two parallel threads from reading this maximum_value from X or Y and attempting to create two different rows with the same maximum_value or maximum_value-1 or maximum_value +1? Sorry if I don't understand correctly. – Inez

Suppose two concurrent threads X and Y are searching the database for a maximum survey_complete_sequence. Now at that instant if they find a maximum value, it will be the same row for both the threads. So when they both try to read the value, first they are attempting to lock the row with the SELECT_FOR_UPDATE mode. Now say X got the locking first, that would mean Y to wait, until the lock is released, to apply the same lock, which would prevent it from reading it. So now X will do its remaining job. When the transaction completes, X releases the lock, then Y will do its job. – Possessory

Answer 4

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?

Elysha

He needs the maximum value which is incremented on each update to the table. A random bigint won't help, nor locking rows or the whole table. – Inez

Answer 5

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.

Inez