Managing Atomic Database Operations with Ecto.Multi

The Problem: Coordinating Multiple Database Writes

When a business process requires updating multiple tables—such as creating a user account and simultaneously initializing their profile and billing record—a failure in the final step can leave the database in a "half-baked" state. Standard Repo.transaction blocks using anonymous functions can become deeply nested and difficult to test as complexity grows.

The takeaway is to use Ecto.Multi to define a data structure representing a series of operations. This allows you to compose the transaction logic separately from its execution, making the sequence of events predictable, testable, and atomic.

Smallest Suitable Design

The minimal implementation of Ecto.Multi involves creating a new Multi object, chaining operations via specific helper functions, and passing the final object to Repo.transaction/1. This separates the definition of the work from the execution of the work.

Implementation Example

Assume a scenario where we must create a User and a Profile. If the profile creation fails, the user must not be persisted.

# Run this in your context module (e.g., Accounts)
# Required permissions: Database write access via Ecto.Repo

alias Ecto.Multi

def register_user(user_params, profile_params) do
  Multi.new()
  # Step 1: Insert the user
  |> Multi.insert(:user, User.changeset(%User{}, user_params))
  # Step 2: Use the result of :user to insert the profile
  |> Multi.insert(:profile, fn %{user: user} ->
       Profile.changeset(%Profile{}, Map.put(profile_params, :user_id, user.id))
     end)
  # Execute the transaction
  |> Repo.transaction()
end

Expected Result: The function returns {:ok, %{user: user, profile: profile}} on success, or {:error, step_name, error_value, changes_so_far} on failure.

Trust and Data Boundaries

A critical boundary in this design is the transition from untrusted user input to the Ecto.Multi pipeline. You should never pass raw parameters directly into Multi.run or Multi.insert without a changeset.

• Validation Boundary: Use Ecto.Changeset to cast and validate data before it enters the Multi. This prevents SQL injection and ensures data integrity.
• Internal State: Once inside the Multi, data is passed between steps via a map (the changes_so_far). Trust this map as it contains records already processed by the database in the current transaction.

Operational Checks and Verification

To verify the atomicity of a Multi, you must check both the return value and the database state after a failure.

Diagnostic Decision Table

Result Tuple	Meaning	Required Action
{:ok, results}	All steps succeeded.	Proceed to success response.
{:error, :user, changeset, _}	User insertion failed.	Return validation errors to client.
{:error, :profile, changeset, _}	Profile failed; User rolled back.	Log failure; no cleanup needed (atomic).

Verification Method: To test this, force a failure in the second step (e.g., by passing an invalid profile changeset). Run Repo.all(User) after the failure; the list should be empty, confirming that the first step was rolled back.

Failure Modes and Limitations

While Ecto.Multi ensures database atomicity, it does not provide "distributed atomicity."

Non-Transactional Side Effects

If you use Multi.run to trigger an external API call (e.g., sending a welcome email), that action cannot be rolled back by the database. If a subsequent step in the Multi fails, the email has already been sent, but the user record is gone.

Lock Contention

Large Multis with many steps increase the duration of the database transaction. This holds locks on the affected rows longer, which can lead to lock_timeout errors in high-concurrency environments. If you notice latency spikes, split the Multi into smaller transactions or move non-critical updates to a background job.

When to Change the Design

The Ecto.Multi approach is suitable for synchronous, single-database operations. You should move to a different architecture (such as the Saga Pattern or an event-driven approach) if:

• Cross-Service Consistency: You need to coordinate writes across multiple different databases or microservices.
• Long-Running Processes: The transaction takes seconds or minutes to complete, which would exhaust the connection pool.
• Eventual Consistency: The business requirement allows the profile to be created a few seconds after the user account.