How to update a record in table using Repo without race conditions?

According to the stock example cited in elixir Ecto 3.4.6 docs …

post = MyRepo.get!(Post, 42)
post = Ecto.Changeset.change post, title: "New title"
case MyRepo.update post do
  {:ok, struct}       -> # Updated with success
  {:error, changeset} -> # Something went wrong

This code appears to be telling me that I must first attempt to get a record from the “Repo”, and only then if it deems my request acceptable, consider updating it with the proposed changes (this because Elixir insists that all changes/updates go via a changeset struct, and since the request may not have this struct in place while making its request, must first go about inventing it before the actual request).

So 2 steps.

Does Elixir have a single step way of doing this?

If you want to use changesets to validate your changes before firing the database update, then yes, you need to load it first.

If you are willing to skip changesets, then you can use Repo.update_all/3.

{1, [updated_post]} =
  from(p in Post, where: == 42, select: p)
  |> MyRepo.update_all(set: [title: "New title"])

This will fire a query that looks something like this

UPDATE "posts" AS p0 SET "title" = $1 WHERE (p0."id" = 42) RETURNING p0."id" ... ['New title']

Not all databases support the returning option, I know for a fact that postgresql does, but not sure about other databases.

EDIT: @mbuhot has a nicer solution that would not skip the changeset. Running the update in one database query might have its applications, but in this particular use-case I’d suggest his solution instead of mine.

In addition to @Ninigi’s suggestion, you can avoid race conditions using locking:

Query.lock to lock a record during a transaction:

Repo.transaction(fn -> 
  post = 
    |> where(id: 42)
    |> lock("FOR UPDATE")

  changeset = Ecto.Changeset.change post, title: “New title”

Or Changeset.optimistic_lock to use a version field to detect races:

post = MyRepo.get!(Post, 42)
post = 
  |> Changeset.change(title: “New title”)
  |> Changeset.optimistic_lock(:version)

case MyRepo.update post do
  {:ok, struct} -> # Updated with success
  {:error, changeset} -> # Something went wrong

Ah, this is actually a lot nicer than running the update w/o changesets.

you can also try to use

1 Like

Ok. Thanks to all for your contributions.

So, to summarise, one can get close to the database engine with classic row/table locking transactions (which will raise and therefore require handling of failed competing requests … ?), or use the Repo.all goto or use changesets (requiring the aforementioned previous priming step).

So, there is flexibility.

Many thanks for the information.

I wanted to do the same but for performance reasons, Loading the model for an update isn’t the most efficient. Ecto provides Repo.update_all to help with these cases. Here is a query to update a record atomically

from(f in Form, where: == ^form_id)
|> Repo.update_all(set: [deleted_at: DateTime.utc_now] )
# ^this returns `{1, nil}` because 1 row was updated.