20

I'm trying to enforce uniqueness of values in one of my table fields. Changing the table isn't an option. I need to use ActiveRecord to conditionally insert a row into the table but I'm concerned about synchronization.

Does first_or_create in Rails ActiveRecord prevent race conditions?

This is the source code for first_or_create from GitHub:

def first_or_create(attributes = nil, options = {}, &block)
  first || create(attributes, options, &block)
end

Is it possible that a duplicate entry will result in the database due to synchronization issues with multiple processes?

Andrew Marshall
  • 95,083
  • 20
  • 220
  • 214
Maros
  • 1,825
  • 4
  • 25
  • 56
  • 2
    AR is full of race conditions like this. – dbenhur May 17 '12 at 18:45
  • See (SO dup) [How do I avoid a race condition in my Rails app?](http://stackoverflow.com/questions/3037029/how-do-i-avoid-a-race-condition-in-my-rails-app) and Rails Cookbook [Avoiding Race Conditions with Optimistic Locking](http://underpop.free.fr/r/ruby-on-rails/cookbook/I_0596527314_CHP_3_SECT_19.html) – dbenhur May 17 '12 at 18:52
  • @dbenhur - I can't use optimistic locking because it involves adding a field to the table. One of my conditions was that I can't add a field so its not a duplicate. – Maros May 18 '12 at 16:49
  • If you can't use optimistic locking, you can try explicit pessimistic locks (though that will hurt performance), or you can add constraints in the db, and catch constraint violations, and retry with appropriate semantics. – dbenhur May 18 '12 at 17:00

4 Answers4

28

The Rails 4 documentation for find_or_create_by provides a tip that may be useful for this situation:

Please note this method is not atomic, it runs first a SELECT, and if there are no results an INSERT is attempted. If there are other threads or processes there is a race condition between both calls and it could be the case that you end up with two similar records.

Whether that is a problem or not depends on the logic of the application, but in the particular case in which rows have a UNIQUE constraint an exception may be raised, just retry:

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

Similar error catching may be useful for Rails 3. (Not sure if the same ActiveRecord::RecordNotUnique error is thrown in Rails 3, so your implementation may need to be different.)

Chris Peters
  • 17,918
  • 6
  • 49
  • 65
  • Just for the record: Rails 3 throws an `ActiveRecord::StatementInvalid` (what is not very specific). – spickermann Dec 27 '13 at 04:26
  • Bummer. So you could still potentially do a `rescue ActiveRecord::StatementInvalid` in order to retry, even if it doesn't read as well as in Rails 4? – Chris Peters Dec 27 '13 at 17:41
  • You could. But the problem is that `StatementInvalid` include also invalid SQL, `NULL` in non-null columns and so on. You might retry thing that will never work. Perhaps you want to use something like https://github.com/nfedyashev/retryable#readme to avoid running into a loop. – spickermann Dec 27 '13 at 22:45
  • More of a question than an argument... The `retry` would only run once though, wouldn't it? So then if it's bad SQL or whatever, it would hard-fail on the 2nd run. – Chris Peters Dec 29 '13 at 18:52
  • 6
    No, `retry` would retry the block every time when there was an error in the block before. If you want to avoid running into a loop, you need to count how offen you retried and you must not retry (but raise) if `tries > max_tries`. Look into the `retryable` gem. – spickermann Dec 30 '13 at 09:42
  • For me `retry` was not an option due an [ActiveRecord QueryCache problem](http://stackoverflow.com/questions/37183537/rails-activerecordrecordnotunique-with-first-or-create) – fguillen May 25 '16 at 07:34
5

Yes, it's possible.

You can significantly reduce the chance of conflict with either optimistic or pessimistic locking. Of course optimistic locking requires adding a field to the table, and pessimistic locking doesn't scale as well--plus, it depends on your data store's capabilities.

I'm not sure whether you need the extra protection, but it's available.

David
  • 1,143
  • 8
  • 12
  • I couldn't use optimistic locking because modifying the table wasn't an option. The issue with pessimistic locking was that I couldn't specify the lock isolation level I needed through Rails without inserting InnoDB-specific syntax. I ended up just using `first_or_create` despite the possibility of duplicate rows. Though I think I could have used the validation helpers in ActiveRecord to ensure uniqueness. – Maros May 18 '12 at 17:00
  • 3
    They suffer from the same race condition – Frederick Cheung May 18 '12 at 21:57
4

Rails 6 (released in 2019) introduced a new method, create_or_find_by, specifically to deal with the potential race condition / synchronization issue that exists with first_or_create and find_or_create_by, where two different threads could simultaneously SELECT to see if the target record exists, both see that it doesn't, and then both try to INSERT it.

Example usage:

company = Company.create_or_find_by(name: 'Stack Overflow')

(Where Company is an existing ActiveRecord model, and name is a unique-constrained column on the company table.)

Also available: create_or_find_by!, which raises an exception if a validation error occurs.

Jon Schneider
  • 25,758
  • 23
  • 142
  • 170
0

I dont think first_or_create is atomic. from console i see a select operation, and then, create. so locking is required. if u use pasimistic lock, no extra col is required. use advisory_lock.

SomeModel.with_advisory_lock("get_or_create_#{some_key}") do
    SomeModel.where(external_id: external_id).first_or_create
end
Ehud
  • 105
  • 5