Diagnosing Ecto.Multi Transaction Failures in Elixir Applications

Recognizable condition

When an Ecto.Multi transaction does not succeed, the function returns an error tuple of the form {:error, failed_step, reason} instead of {:ok, multi}. The failed_step is the atom you gave to the step (e.g., :insert_user), and reason tells you why that step failed.

Cause/diagnostic table

Error reason shape	Typical cause
{:error, :changeset, changeset}	Validation failed on the changeset (e.g., missing required field, format mismatch)
{:error, :constraint_error, constraint_name}	Database constraint violation (unique index, foreign key, check)
{:error, :connection_error, reason}	Could not acquire a connection or the DB timed‑out
{:error, :exception, exception}	An unexpected Elixir exception raised inside the step function

Ordered checks

1. Identify the failed step and error type – pattern‑match on the result to capture failed_step and reason.
2. For changeset errors – run the changeset in isolation to see field‑specific messages: Ecto.Changeset.traverse_errors(changeset, fn {msg, opts} -> Enum.join([msg, inspect(opts)], " ") end).
3. For constraint errors – query the schema to confirm the constrained column/index: Repo.query!("SELECT column_name FROM information_schema.key_column_usage WHERE constraint_name = $1", [constraint_name]).
4. For connection errors – check pool usage (:pool_size and :overflow in your Repo config) and look at DB server logs for timeout messages.
5. For exceptions – enable stack‑trace logging with Ecto.Loggers (e.g., config :my_app, MyApp.Repo, loggers: [{Ecto.Loggers, :console, [:debug]}]) and inspect the logged exception.

Fixes tied to findings

• Changeset validation – adjust validation rules (validate_required/3, format/3, etc.) or correct the input data, then re‑run the Multi with the updated params.
• Constraint violation – modify the data to satisfy the constraint (e.g., choose a unique value) or catch the error and convert it to a changeset error via Ecto.Changeset.add_error/changeset, field, message.
• Connection timeout – increase :pool_size or :overflow, raise :connection_timeout, verify network latency, and tune the DB’s max connections if needed.
• Unexpected exception – wrap the faulty function in a try/rescue, log the stacktrace, fix the bug, or convert the exception to a changeset error so the Multi can handle it uniformly.

Escalation criteria

If after applying the fix the same step fails in more than 5 % of transactions over a rolling 10‑minute window, or if :connection_error persists despite pool tuning, escalate to the infrastructure team for a DB performance review. Consider adding a circuit‑breaker (e.g., using honeybadger or a custom :timeout wrapper) to protect the service while the underlying issue is investigated.

Verification example

To confirm the :constraint_error path, set up a SQLite sandbox:

# test_helper.exs
Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, :manual)

# schema with unique index
defmodule MyApp.Accounts.User do
  use Ecto.Schema
  schema "users" do
    field :email, :string
    timestamps()
  end

  def changeset(user, attrs) do
    user
    |> cast(attrs, [:email])
    |> validate_required([:email])
    |> unique_constraint(:email, name: :users_email_index)
  end
end

# test
{:ok, _} = MyApp.Repo.insert(%MyApp.Accounts.User{email: "[contact removed]"})

multi =
  Ecto.Multi.new()
  |> Ecto.Multi.insert(:dup, %MyApp.Accounts.User{email: "[contact removed]"})

case MyApp.Repo.transaction(multi) do
  {:error, :dup, {:error, :constraint_error, :users_email_index}} ->
    IO.puts("Expected constraint error received")
  other ->
    IO.inspect(other, label: "unexpected result")
end

Running the test should print the expected constraint error message, confirming that the error tuple shape matches the diagnostic table.

Limitations and practical verification

This guide assumes Ecto 3.0 or later; older 2.x releases may return {:error, :generic, reason} for constraint violations. Always pattern‑match on the full error tuple to avoid swallowing partial results, which could leave the database in an inconsistent state. After applying a fix, monitor the error rate via telemetry (e.g., :ecto.query events) and verify that the failure percentage drops below the escalation threshold.