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:

talk to the database to try to find the stuff we want
if we didn't find it, make it ourselves
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:

create sensible uniqueness constraints in your DB ahead of time.
save something if you want to save it
if it worked, good.
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?

What you're describing is an upsert which a lot of databases do support. — Anthony, Apr 21 '17 at 15:49
@Anthony right, in fact a gem we like to use, https://github.com/zdennis/activerecord-import, supports it. But I'm just not clear why the default ruby stuff would be designed that way? It seems more prone to failure in more cases. — Mark Bolusmjak, Apr 21 '17 at 15:53

Mark Bolusmjak · Accepted Answer · 2017-04-24T15:09:40.150

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.

I actually have a generalization of that patched into my ActiveRecord, the default `find_or_create*` methods are pretty lame defaults IMO. — mu is too short, Apr 21 '17 at 16:16
@muistooshort have you considering proposing a fix to rails core? — katzmopolitan, Dec 18 '17 at 21:19

score 0 · Answer 2 · edited May 23 '17 at 10:31

0

Clearly it's not thread-safe by default but they may be designed this way to perform better.

It's way faster to find first and create if necessary than fail creation most of the time and sometimes avoid an exception (that could be handled).

This discussion may be helpful for you.

edited May 23 '17 at 10:31

Community

1
1

answered Apr 21 '17 at 16:14

André Guimarães Sakata

1,189
8
16

Are ActiveRecord's find_or_create* methods fundamentally flawed?

2 Answers2