Mastering Ecto.Multi: Atomic Transactions in Elixir

Why Ecto.Multi matters

When an Elixir application needs to perform several database operations that must either all succeed or all fail, a single Repo.transaction/1 call is not enough. The Ecto.Multi struct lets you compose those operations, capture validation errors, and keep the code readable. The key idea is that every step in the transaction is named, and the final result is a map keyed by those names. This gives you deterministic access to each operation’s return value and a clean error path.

Core API walk‑through

The API is intentionally small:

• Multi.new/0 – creates an empty struct.
• Multi.insert/4, Multi.update/4, Multi.delete/4 – queue a DML operation. The fourth argument is a Changeset or a struct; if the changeset is invalid the operation is skipped and the error is recorded.
• Multi.run/3 – injects arbitrary logic. It receives a function that must return either {:ok, result} or {:error, reason}. The :return option (since 3.5) lets you return the raw value instead of wrapping it in a tuple.
• Multi.merge/2 – combine two Multi structs. Duplicate names in the second argument overwrite the first.

After building the Multi, call Repo.transaction(multi). The return value is a two‑tuple:

{:ok, %{op_name1: result1, op_name2: result2, ...}}
{:error, failed_op, failed_result, changes_so_far}

The changes_so_far map contains the results of all operations that ran before the failure. This is useful for logging or compensating actions.

Worked example: user sign‑up with notification

Below is a typical pattern you might put in a context module. Replace MyApp.Repo, MyApp.Accounts.User, and MyApp.Accounts.Profile with your own modules.

# lib/my_app/accounts.ex
alias MyApp.Repo
alias MyApp.Accounts.{User, Profile}

def register_user(attrs) do
  user_changeset = User.changeset(%User{}, attrs)
  profile_changeset = Profile.changeset(%Profile{}, %{user_id: nil})

  multi =
    Multi.new()
    |> Multi.insert(:create_user, user_changeset)
    |> Multi.update(:attach_profile, fn %{create_user: user} ->
         profile_changeset
           |> Ecto.Changeset.put_change(:user_id, user.id)
       end)
    |> Multi.run(:notify, fn %{create_user: user} ->
         case MyApp.Notifier.send_welcome(user.email) do
           :ok -> {:ok, :sent}
           {:error, reason} -> {:error, reason}
         end
       end, return: :ok)

  case Repo.transaction(multi) do
    {:ok, %{create_user: user, attach_profile: _profile, notify: _}} ->
      {:ok, user}
    {:error, failed_op, failed_result, changes} ->
      # Log or handle the failure
      {:error, failed_op, failed_result, changes}
  end
end

Key points:

• The attach_profile step receives the result of create_user via the fn argument. This lets you reference the newly inserted user ID.
• The notify step is a side‑effect. By using return: :ok you keep the result map clean: the value will be true instead of {:ok, :sent}.
• If the user changeset is invalid, the entire transaction rolls back and you get an {:error, :create_user, changeset, %{}} result.

Trade‑offs and limitations

• Connection pool exhaustion: All operations in a Multi run on the same DB connection. Long‑running :run functions (HTTP calls, heavy math) hold the connection and can deplete the pool. Move heavy I/O outside the transaction or use Task.async with explicit compensation logic.
• No automatic retry: PostgreSQL deadlocks or serialization failures are not retried by Repo.transaction/1. Wrap the call in a retry loop or use a library like db_connection’s retry options.
• Manual error aggregation: Errors from :run steps are not merged into the changeset’s errors map. If you need unified validation feedback, call Ecto.Changeset.add_error/3 manually.
• No nested transactions: Only the outermost Repo.transaction creates a real DB transaction. Calls to Multi.merge simply flatten the operations.
• Custom SQL inside :run: Using Repo.query/3 bypasses Ecto’s type safety and logging. Keep such calls minimal and document them clearly.

Practical checklist before deploying

1. Test the flow: In iex -S mix, build a Multi that inserts a user, fails on the notification step, and verify the rollback and changes_so_far map.
2. Monitor pool usage: In production, enable pool_size metrics (e.g., telemetry) to detect exhaustion spikes when :run steps are heavy.
3. Add retry logic: Wrap Repo.transaction in a retry loop that catches {:error, :deadlock_detected, _} and retries a limited number of times.
4. Document side‑effects: For any :run that performs external calls, keep a separate log and consider idempotency keys so that a retry does not create duplicates.
5. Keep operations atomic: If you need to call an external API that must succeed before the DB commit, consider moving the call outside the transaction and using a compensating transaction on failure.

Takeaway

Ecto.Multi gives you a clean, declarative way to bundle multiple Repo calls into a single atomic transaction, with structured error handling and a deterministic result map. By respecting its limitations—especially connection pool usage and the lack of automatic retries—you can build robust, maintainable workflows in any Elixir application.