Lock lines on rails to avoid collisions. (Rear End Postgres)

Question

So, I have a method on my model object that creates a unique serial number when the binary field in the string is updated from null to true. This is implemented as follows:

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, do I need to block so that two rows are updated at the same time, and not two rows with the same survey_complete_sequence function?

If you could lock a single row, rather than an entire table, that would be good, because it is a frequently viewed table by users.

Answer 1

If you use postgress, perhaps Sequenced can help you without defining a sequence at the DB level.

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

Answer 2

If you want to deal with this in the application logic itself, instead of letting the database handle it. You use the rails with_lock function, which will create a transaction and get a row level db lock on the selected rows (in your case, one row).

Answer 3

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

If you do not have a huge table and many updates every milliseconds (in thousands), this should not be a problem in real life. But if the idea bothers you, you can continue and add a unique index to the table in the column "survey_complete_sequence". The DB error will extend to the Rails exception, which you can handle in the application.

Answer 4

I believe that you should give advisory locks. It ensures that the same block of code will not be executed on two computers at the same time, while keeping the table open to another business.

It uses a database, but does not lock your tables.

You can use a gem named "with_advisory_lock" as follows:

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

https://github.com/ClosureTree/with_advisory_lock

It does not work with SQLite.

Answer 5

What you need to block

In your case, you need to lock the row containing the maximum survey_complete_sequence , as this is the row that each query will look for when it gets the required value.

 maxval =AnswerHeader.maximum('survey_complete_sequence') 

Is it possible to lock one row, not an entire table

Such a scenario does not exist for your scenario. But you can use Postgresql SELECT FOR UPDATE row level locking .

To get exclusive row-level locking in a row without changing the row, select the row using SELECT FOR UPDATE.

And you can use a pessimistic lock on the rails and specify which lock you will use.

Call blocking (“some kind of blocking clause”) to use a database-specific lock, such as “LOCK IN SHARE MODE” or “FOR UPDATE NOWAIT”

Here is an example of how to achieve this from the official rails guide

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

Relations using locks are usually wrapped inside a transaction to prevent deadlock conditions.

So what you need to do -

• Apply SELECT FOR UPDATE lock to row consisting of maximum('survey_complete_sequence')
• Get the required value from this line
• Update AnswerHeader with accepted value