Is `Repo.insert_or_update` safe for race condition?

Questions / Help
,
1

I have already used upsert feature via Repo.insert/2 for a long time.

Today, when I read other’s code, I found something like this:

case MyRepo.get(Subscription, id) do
  nil  -> %Subscription{id: id}
  subscription -> subscription
end
|> Subscription.changeset(changes)
|> MyRepo.insert_or_update()

I’m wondering:

  1. is this usage safe for race condition?
  2. Should I use Ecto.Mulit.insert_or_update instead?
  3. If it’s not safe, why MyRepo.insert_or_update exists?

An example for race condition:

  1. A try to get subscription with id reminder, get nil, then create a new subscription struct.
  2. B try to get subscription with id reminder , and get nil, then create a new subscription struct.
  3. A try to insert, done.
  4. B try to insert, rejected.
2

Surely B’s insert would be rejected only if there’s a uniqueness constraint on the fields being updated? Otherwise you’ll get two rows with identical data but different ids.

If there’s a constraint then you can catch the error after the insert_or_update, find the existing id, and then re-run the function, which will update.

1 Like
3

https://hexdocs.pm/ecto/constraints-and-upserts.html#upserts

I think you need this. Single sql query, no concurrency issues

2 Likes
4
  1. No, this is a classic example of a race condition, which you identified in your example (A and B trying to insert a record with the same id)
  2. Multi is kind of irrelevant with respect to race conditions, I assume what you’re asking is “would wrapping this code in a transaction help?”. The answer is: no, it won’t help in the general case. It would only help if the transaction isolation level of your DB is set to “serializable”, which is typically not the case for performance reasons.
  3. insert_or_update is not there to avoid race conditions, but as a convenience function that allows you to persist a changeset regardless of whether the underlying data is persisted in the DB or not (otherwise you’d have to choose insert or update)

You should check the result of MyRepo.insert_or_update(): if the result is {:error, changeset} and the changeset contains a primary key constraint violation, then you know that you hit the race condition and you can react appropriately.

3 Likes
5

@trisolaran Thank you for the explanation.

To summarize:

Repo.insert_or_update is just a helper like if condition, do: insert, else: update, nothing special, just more compact.

6

Yes pretty much: ecto/lib/ecto/repo/schema.ex at 960725b9e0719c79d8296c4852072780e8d5cc3e · elixir-ecto/ecto · GitHub

Also check @thousandsofthem’s suggestion. That’s a good point and might make your code simpler.

1 Like