Are ActiveRecord's find_or_create* methods fundamentally flawed?

Question

There are a few methods: first_or_create_by, find_or_create_by, etc which work on the principle:

1. talk to the database to try to find the stuff we want
2. if we didn't find it, make it ourselves
3. save it to the db

Clearly, concurrent calls of these methods could have both threads not find what they want, and at step 3 one will unexpectedly fail.

Seems like a better solution is, create_or_find

That is:

1. create sensible uniqueness constraints in your DB ahead of time.
2. save something if you want to save it
3. if it worked, good.
4. if it didn't work because of a RecordNotUnique exception, it's already there, great, load it

So in what circumstances would I want to use the Rails built-in stuff and not my own (seemingly more reliable) create_or_find?

Answer 1

After digging in, I'm going to answer my own question.

The document for find or create by says:

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

This, in general, will have better performance than create_or_find.

Consider that create_or_find will require 1 DB trip in the case of success, which will only happen once per unique record. Every other time it will require 2 DB trips (a failed create and a search).

A retried find_or_create will require 3 trips in the case of failure (search, failed create, search again), but that can only happen so many times in a very small window. Beyond that, every other call will to find_or_create a record, will require 1 DB trip.

Therefore the amortized cost of retried find_or_create is better, and reached quickly.