Database lock does not work properly with Rails & Postgres

Question

I have the following code in a rail model:

foo = Food.find(...) foo.with_lock do if bar = foo.bars.find_by_stuff(stuff) # do something with bar else bar = foo.bars.create! # do something with bar end end 

The goal is to make sure that the Bar of the created type is not created twice.

Testing with_lock works on the console, confirms my expectations. However, in production, apparently, in any case or in all cases, the lock does not work as expected, and a backup bar is created - so using the with_lock function (always?), A code arises waiting to be rotated.

What could be here?

The update is so sorry to everyone who said that “blocking foo will not help you” !! My example did not initially get a search in the bar. this is now fixed.

Answer 1

The reason locks will not work in a Rails application in the query cache.

If you try to obtain an exclusive lock on the same row several times in one request, the request for cached kicks in subsequent subsequent lock requests will never reach the database itself.

A problem was reported on Github.

Answer 2

You are confused by what with_lock does. From the exact guide :

with_lock (lock = true)

Wraps the passed block in a transaction, blocking the object before the assignment. You can pass the SQL lock clause as an argument (see lock! ).

If you check what with_lock does internally, you will see that this is a little more than the thin shell around the lock! :

lock! (lock = true)

Get the row lock in this entry. Reloads the entry to obtain the requested lock.

So, with_lock just does the row lock and the row lock foo .

Do not bother yourself with all this nonsense. The only reasonable way to deal with this situation is to use a unique constraint in the database, no one except the database can ensure uniqueness if you do not want to do absurd things, such as blocking entire tables; then just go ahead and blindly try your INSERT or UPDATE and turn on the trap and ignore the exception that will occur when a unique constraint is violated.

Answer 3

Why don't you use a unique constraint? It is made for uniqueness.

Answer 4

The right way to handle this situation is really right in Rails docs:

http://apidock.com/rails/v4.0.2/ActiveRecord/Relation/find_or_create_by

 begin CreditAccount.find_or_create_by(user_id: user.id) rescue ActiveRecord::RecordNotUnique retry end 

("find_or_create_by" is not atomic, it is actually a find and then created. So replace it with your find and then create. The documents on this page accurately describe this case.)