Question about ecto transaction, multiple associations, and rollbacks

I have a transaction that updates upto 3 tables and it became long and ugly. I’d love to learn more to be able to write cleaner and more efficient code.

I have a form that takes inputs to update upto 3 tables.

a. Uses external API to cache the data if it expired or doesn’t already exist.
b. build_assoc with (a) for a new data point
c. build_assoc with (a) and (b) for a new data point

Some specific questions:

  1. Since transactions should be kept as short as possible, would you recommend fetching from the external API outside the transaction? (This is what I’ve done but it adds complexity to the code.)
  2. What would be to best practice to manage layers of associations? I found and I’m leaning toward the pattern matching example.
  3. How would you rollback multiple changesets?

It seems Ecto.Multi may be exactly what you need:

It answers questions 1 and 3 and it may as well answer question 2 too.


Maybe. I’ve looked into it but haven’t found a way to apply associations yet.

Since insert/update in Ecto.Multi accepts changesets, you can still build the associations in the changesets if you want to. If you can build everything with associations, then you don’t need multi because the transaction part is taken care for you when you try to introduce the changeset.

Thanks for the reply @jose.

Do you think we can go through an example? I couldn’t find anything online :sweat:

  transaction = Repo.transaction fn ->
    location = location || Repo.insert!(location_changeset)

    item_name = photo_params["item_name"]
    item = Repo.get_by(App.Item, [name: item_name, location_id:])

    item_changeset = 
      |> build_assoc(:items)
      |> App.Item.changeset(Map.put(photo_params, "name", item_name))
    item = item || Repo.insert!(item_changeset)

    photo_changeset =
      |> build_assoc(:photos, item_id:, user_id:
      |> Photo.changeset(photo_params)
  case transaction do
    {:ok, _photo} ->
      |> redirect(to: phto_path(conn, :index))
    {:error, changeset} ->
      render(conn, "new.html", changeset: changeset)

Would ecto rollback everything when the second or third Repo.insert fails? Also, is there a way to cascade a changeset with multiple schemas?

1 Like

Almost two years later: yes, according to the official docs any exception raised inside the function given to Repo.transaction will result in a rollback.

Please note though, the third insert in your code is not using the bang variant of the function (namely it’s not insert!) and will thus not raise an exception so the transaction will likely still succeed with only partial success – not good.

EDIT: The above is NOT true: Ecto.Multi docs

To use Repo.transaction, do one of these:

  1. Either use bang functions everywhere in the transaction function (Repo.insert!, Repo.update! etc.), or…
  2. Use the with keyword and chain all the operations through non-bang functions and call Repo.rollback in the else clause – which would mean that the first failed operation will return {:error, reason} and the transaction will return that exact error so you can troubleshoot further afterwards. Or…
  3. Just use Ecto.Multi which will give you even more info if an operation fails. It’s really the best way of doing such composite operations ever since it was introduced. Just have one Ecto.Multi variable and append all your operations to it, then just execute it at once: Repo.transaction(your_multi).

It’s best if you don’t mix these styles. Just pick one and stick with it.

I started off using more hacky solutions and trying to be clever but nowadays I am always using Ecto.Multi and I am very pleased with the results. The code is much easier for a human to understand as well, which is a huge bonus win.


This is not possible - the transaction will always either fully succeed or fully fail. If there’s a query inside a transaction that failed, but not raised the Repo.transaction block will return a generic error {:error, :rollback}.

Apologies, maybe I was misled by the documentation. I cannot see there explicitly stated that if a function returns {:error, reason} tuple then the transaction will be rolled back.

Maybe this part?

A successful transaction returns the value returned by the function wrapped in a tuple as {:ok, value}.

Or if not, can you point me at the docs that show my mistake?

1 Like

Thank you. I only looked at the Repo.transaction docs. Edited my post above, don’t want to mislead people.