Using Ecto.Multi to Write Related Database Rows Atomically

When separate database writes leave partial records

Your application needs to create an order, reserve inventory, and write an audit entry. If you call Repo.insert/2 three times in a row and the third call fails, the first two rows stay in the database. You now have an order with no inventory reservation and no audit trail. That partial state is harder to clean up than a failed write.

The useful takeaway: wrap the related writes in an Ecto.Multi and execute it with Repo.transaction/2. Either every operation commits, or the database rolls back to the state before the transaction started.

Recognizable condition and likely causes

Use this guide when you see any of the following after a multi-step write:

Symptom	Likely cause	Diagnostic check
Some rows exist after a failed multi-step write	Separate Repo.insert/2, Repo.update/2, or Repo.delete/2 calls without a transaction	Search the context module for consecutive Repo calls that share the same business outcome
Transaction fails but you cannot tell which step failed	Error tuple from Repo.transaction/2 is not pattern matched	Inspect the return value of the failing Repo.transaction call
Database connections pool exhausts under load	Long-running function inside Multi.run/5 holds the transaction open	Review custom functions passed to Multi.run/5 for external API calls or heavy computation

Ordered checks before changing code

1. Confirm the Ecto version. Run mix deps in the project root. Look for ecto in the dependency list. Ecto.Multi is stable in Ecto 3.x and 4.x. No special permissions are required; the risk is only reading outdated output if dependencies are not compiled.
2. Define the transaction boundary. Identify which operations must succeed or fail together. If one step can safely succeed while another fails, do not put both in the same Multi.
3. Check whether any step needs custom logic. If a step requires a database function or side effect inside the transaction, plan to use Multi.run/5. Keep that function short; it holds a connection and any row locks for the entire transaction.
4. Verify the adapter supports savepoints if you plan nested Multi structures. PostgreSQL supports savepoints; MySQL and SQLite behavior varies by adapter and version.

Build and execute the Multi

This example creates an order, updates inventory, and inserts an audit log as one unit. Replace the schema modules and changeset functions with your own.

defmodule MyApp.Orders do
  import Ecto.Multi

  def place_order(order_attrs, product, audit_attrs) do
    multi =
      Multi.new()
      |> Multi.insert(:order, %Order{} |> Order.changeset(order_attrs))
      |> Multi.update(:inventory, product, %Product.changeset(product, %{quantity: product.quantity - 1}))
      |> Multi.insert(:audit, %AuditLog{} |> AuditLog.changeset(audit_attrs))

    Repo.transaction(multi)
  end
end

Run this function inside your application context, typically from a Phoenix controller or a background job. No database user permissions beyond those already configured for your Ecto Repo are required. The risk is that a constraint violation in any step aborts the entire transaction.

Handle the result

Repo.transaction/2 returns one of two shapes:

• {:ok, multi} when every operation succeeds. The database has committed all changes.
• {:error, failed_operation, failed_value, changes_so_far} when any step fails. failed_operation is the name you gave the step (for example, :inventory). changes_so_far holds the in-memory results of steps that ran before the failure. None of the changes are persisted.

case place_order(attrs, product, audit_attrs) do
  {:ok, _multi} ->
    {:ok, :order_placed}

  {:error, :inventory, _value, changes_so_far} ->
    Logger.warn("Inventory update failed; prior steps: %{inspect(changes_so_far)}")
    {:error, :out_of_stock}

  {:error, failed_op, _value, _changes} ->
    Logger.error("Unexpected failure in operation #{inspect(failed_op)}")
    {:error, :unexpected}
end

Do not assume changes_so_far contains database rows. It contains the values returned by earlier Multi steps, usually changesets or structs, and exists only in memory.

Nested Multi structures

You can pass one Multi inside another. This lets separate context modules contribute operations without merging all logic into one function. The outer transaction uses database savepoints where the adapter supports them. If an inner Multi fails, the outer transaction can still roll back everything, but savepoint behavior differs across PostgreSQL, MySQL, and SQLite.

Limitations

• Ecto.Multi does not prevent deadlocks or unique constraint violations. Those errors still surface as transaction failures and must be handled explicitly.
• Long-running functions in Multi.run/5 hold the database connection and any locks for the full transaction duration, increasing pool pressure.
• A failed Multi rolls back database changes, but it does not automatically undo side effects outside the database, such as emails sent or external API calls made inside Multi.run/5.

Practical verification

1. In a test or development environment, build a Multi where the second operation violates a database constraint, such as a unique index.
2. Execute it with Repo.transaction/2.
3. Confirm the return value matches {:error, :failed_op, value, changes_so_far} and that no rows from the first operation appear in the database.
4. Run mix deps and confirm the installed Ecto major version supports Ecto.Multi before adopting nested Multi patterns in production.

Escalation criteria

Escalate to a database administrator or senior engineer when:

• Transactions regularly time out or deadlock under concurrent load.
• You need distributed transactions across multiple databases or external services; Ecto.Multi only coordinates operations within one Repo transaction.
• Nested Multi savepoints behave inconsistently after migrating database adapters or upgrading the database server.