Ensuring Atomicity in Phoenix with Ecto.Multi – A Practical Guide

Concrete Problem: Two Tables, One Atomic Operation

When building a Phoenix app you often need to write to more than one table in a single logical step. For example, creating a users row and a correlated profiles row. If the second insert fails, you don’t want the first one left dangling. Relying on two separate Repo.transaction calls is error‑prone: you must manually roll back, race conditions can sneak in, and you pay two round‑trips to the database.

Thesis: Use Ecto.Multi to Bundle Operations into One Transaction

Ecto.Multi is a DSL that lets you queue a sequence of database commands and execute them in a single transaction. If any step fails, the entire batch is rolled back automatically. The return value is a map of results keyed by the operation names, or an {:error, op, changeset, changes_so_far} tuple that tells you exactly where the failure happened.

Building a Multi: Step‑by‑Step

1. Define the operations – Each call to Ecto.Multi.insert/3, Ecto.Multi.update/3, or Ecto.Multi.run/3 registers an operation. The first argument is a unique atom key.
2. Chain operations – The order matters; later ops can reference the results of earlier ones via the changes_so_far map.
3. Execute the batch – Call Repo.transaction/1 with the Ecto.Multi object. The call must run in a context that has DB access (e.g., a controller action or a mix phx.gen.context worker).

Below is a typical signup flow that creates a user and a profile atomically.

# lib/my_app/accounts/accounts.ex
defmodule MyApp.Accounts do
  alias MyApp.{Repo, User, Profile}
  import Ecto.Multi

  def register_user(attrs) do
    multi = Multi.new()
    |> Multi.insert(:user, User.changeset(%User{}, attrs))
    |> Multi.run(:profile, fn _repo, %{user: user} ->
         Profile.changeset(%Profile{}, %{user_id: user.id, bio: ""})
         |> Repo.insert()
       end)

    case Repo.transaction(multi) do
      {:ok, %{user: user, profile: profile}} -> {:ok, %{user: user, profile: profile}}
      {:error, op, changeset, _} -> {:error, op, changeset}
    end
  end
end

Run this from a Phoenix controller or a iex -S mix shell. The Repo.transaction/1 call requires a DB connection; make sure your config/dev.exs points to a running PostgreSQL instance.

Debugging: What Does the Error Tuple Tell You?

If the profile insert fails (e.g., due to a foreign key violation), Repo.transaction returns:

{:error, :profile, %Ecto.Changeset{...}, %{user: %User{...}}}

The key :profile shows which op failed, the changeset contains validation errors, and the map shows the data that was successfully applied before the failure. Inspecting this tuple lets you log precise diagnostics without guessing.

Performance & Overhead Considerations

• Single round‑trip – All ops are sent to the DB in one request. With PostgreSQL, this can be 10–20% faster than two separate Repo.transaction calls, especially under high load.
• Transaction size limits – Large Ecto.Multi chains can hit database limits (e.g., PostgreSQL’s max_prepared_transactions or the maximum byte size of a single command). Keep multi chains under a few thousand operations.
• Callbacks – Ecto’s after_insert / after_update callbacks are not automatically invoked inside a Multi. If you rely on side‑effects, call them explicitly within a Multi.run/3 step.

Trade‑Off: Synchronous vs Asynchronous Workloads

Because all operations in a Multi run sequentially in the same DB session, you cannot mix async jobs inside a Multi. If you need to enqueue a background job after a successful insert, do that in the controller or service layer after the transaction returns {:ok, _}. This keeps the transaction short and reduces lock contention.

Actionable Checklist

1. Wrap related DB ops in an Ecto.Multi and call Repo.transaction/1.
2. Inspect the returned map or error tuple to confirm atomicity.
3. Keep the chain short; if you need more than ~500 ops, consider splitting or using a stored procedure.
4. Remember to trigger any required callbacks manually inside the Multi.
5. Profile your app: compare the number of round‑trips with and without Multi using iex logs or a SQL profiler.

By following this pattern you guarantee that either all related rows are written or none are, eliminating orphaned data and simplifying error handling in Phoenix applications.