CODEWITHSUNDEEP
ruby-on-railsrubypostgresqltransactionslocking
John Bachir
John Bachir

Reputation: 22711

Database lock not working as expected with Rails & Postgres

I have the following code in a rails 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 a Bar of the type being created is not being created twice.

Testing with_lock works at the console confirms my expectations. However, in production, it seems that in either some or all cases the lock is not working as expected, and the redundant Bar is being attempted -- so, the with_lock doesn't (always?) result in the code waiting for its turn.

What could be happening here?

update so sorry to everyone who was saying "locking foo won't help you"!! my example initially didin't have the bar lookup. this is fixed now.

Upvotes: 9

Views: 5789

Answers (4)

Gal
Gal

Reputation: 5907

The correct way to handle this situation is actually right in the 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, its actually a find and then a create. So replace that with your find and then create. The docs on this page describe this case exactly.)

Upvotes: 3

Damir Zekić
Damir Zekić

Reputation: 15940

A reason why a lock wouldn't be working in a Rails app in query cache.

If you try to obtain an exclusive lock on the same row multiple times in a single request, query cached kicks in so subsequent locking queries never reach the DB itself.

The issue has been reported on Github.

Upvotes: 1

mu is too short
mu is too short

Reputation: 434616

You're confused about what with_lock does. From the fine manual:

with_lock(lock = true)

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

If you check what with_lock does internally, you'll see that it is little more than a thin wrapper around lock!:

lock!(lock = true)

Obtain a row lock on this record. Reloads the record to obtain the requested lock.

So with_lock is simply doing a row lock and locking foo's row.

Don't bother with all this locking nonsense. The only sane way to handle this sort of situation is to use a unique constraint in the database, no one but the database can ensure uniqueness unless you want to do absurd things like locking whole tables; then just go ahead and blindly try your INSERT or UPDATE and trap and ignore the exception that will be raised when the unique constraint is violated.

Upvotes: 7

Frank Heikens
Frank Heikens

Reputation: 126991

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

Upvotes: 2

Related Questions