Ecto.Multi: Atomic Multi-Row Writes and Constraint Error Handling

When a single request must create or update several rows and fail as a single unit, partial writes are dangerous. If the second insert fails, you are left with an orphaned row. The useful takeaway: use Ecto.Multi to compile named steps into one database transaction and get a deterministic error tuple that names the failed step.

The mechanism: named steps in one transaction

Ecto.Multi builds a list of named steps. Passing it to Repo.transaction/1 wraps those steps in a single BEGIN/COMMIT (or ROLLBACK) block. Later steps receive the results of earlier steps by name, so you can build dependent rows without manual transaction plumbing.

Run this in a context module or an iex -S mix session:

def create_account(attrs) do
  Ecto.Multi.new()
  |> Ecto.Multi.insert(:user, User.changeset(%User{}, attrs) |> unique_constraint(:email))
  |> Ecto.Multi.insert(:profile, fn %{user: user} ->
    Profile.changeset(%Profile{}, %{user_id: user.id})
  end)
  |> Repo.transaction()
end

The return value is the part developers most often get wrong. On success you get {:ok, %{user: %User{}, profile: %Profile{}}}. On failure you get a four-element tuple: {:error, failed_step_name, value_from_that_step, changes_so_far}. The fourth element is the map of changes already committed inside the transaction before the failure.

Turning constraint violations into changeset errors

A common reason to reach for Ecto.Multi is to handle unique races. If the users table has a unique index on email, adding unique_constraint(:email) to the user changeset tells Ecto to catch the database's unique violation and convert it into a changeset error. Without that call, a duplicate email raises Ecto.ConstraintError and the transaction aborts.

With the constraint declared, a duplicate email returns {:error, :user, changeset, changes_so_far} instead of raising. Your caller can render the changeset error normally. The database index must exist; unique_constraint only maps the error, it does not create the index.

Dependent logic with Ecto.Multi.run

For logic that depends on earlier results but is not a plain insert or update—such as computing a slug or enqueueing a job—use Ecto.Multi.run/3. The function runs inside the transaction, so avoid slow external calls. Return {:error, reason} to stop the whole multi.

|> Ecto.Multi.run(:slug, fn %{user: user} ->
  {:ok, Slug.slugify(user.name)}
end)

Limits: what Multi does not do

• No automatic retry. Multi does not retry serialization failures or deadlocks. Under contention, you may still get isolation-level errors that need an explicit retry policy.
• Not a replacement for validation. Each step still runs its own changeset validations. Multi only guarantees atomicity at the database level.
• External side effects are not rolled back. If you send an email or make an HTTP call inside a Multi.run step, that side effect persists even if a later step fails and the transaction rolls back.
• Not for read-only work. If you only need to fetch associated rows, use joins or preloads in a normal query instead of opening a transaction.

Common mistakes

Matching only {:ok, result} and crashing on the four-element error tuple is the most frequent bug. Code written for Repo.insert expects {:error, changeset}, but Repo.transaction on a Multi returns {:error, step, value, changes}. Update the caller to handle the step name.

Other pitfalls include relying on a unique index without declaring unique_constraint, sending email inside the transaction, and reusing step names. Step names must be unique atoms; a later step with the same name can overwrite an earlier entry in the success map. In async SQL sandbox tests, callbacks that spawn processes need sandbox access; otherwise connection checkout fails.

Verifying the behavior

Check mix.lock for the Ecto version and confirm Ecto.Multi.insert/3, Ecto.Multi.run/3, and Ecto.Changeset.unique_constraint/2 are available. In an iex -S mix session, run the create function twice with the same email. The expected result for the second call is {:error, :user, %Ecto.Changeset{}, _} with no profile row created.

Inspect the migration for a unique index on the email column; without it, unique_constraint has nothing to map. Enable Ecto debug logging in the repo config (for example, log: :debug in config/dev.exs) and confirm the inserts are wrapped by one BEGIN/COMMIT or ROLLBACK. Finally, review the controller or context caller to ensure it handles the four-element Multi error before rendering.