Deadlock detected when inserting multiple (Postgrex.Error 40P01)

Hello everyone, when I run my unit test, I sometimes (not all the time) run into this error:

(Postgrex.Error) ERROR 40P01 (deadlock_detected) deadlock detected

     hint: See server log for query details.
 
 Process 11437 waits for ShareLock on transaction 56033; blocked by process 11436.
 Process 11436 waits for ShareLock on transaction 56040; blocked by process 11437.

The test attempts to insert 3 records:

work_orders =
  for _ <- 1..3 do
    {:ok, work_order} = Documents.create_work_order(attrs)
    work_order
  end

expected_counters = [1, 2, 3]
assert expected_counters == work_orders |> Enum.map(& &1.counter)

create_work_order is an Ecto.Multi that:

  1. Inserts counter to counters DB (business requirement dictates that counter restarts from 1 every year)
  2. Grabs the returned counter to insert together to work_orders DB
  3. Repo.transaction()

Is the above loop a bad way to test? Or is my transaction function incorrect even though it works (pasted below)?

def create_work_order(attrs \\ %{}) do
  counter_changeset = Counter.changeset(%Counter{}, attrs)
  work_order_changeset = WorkOrder.creation_changeset(%WorkOrder{}, attrs)

  Multi.new()
  # Get the next counter of the year
  # If year doesn’t yet exist, create one and set counter to 1 (database default)
  |> Multi.insert(:next_counter, counter_changeset,
    # If year exists, increment the counter by 1
    conflict_target: :year,
    on_conflict: [inc: [counter: 1]]
  )
  # Insert work order, with counter added to the changeset
  |> Multi.insert(
    :work_order,
    fn %{next_counter: %{counter: counter}} ->
      work_order_changeset
      |> Ecto.Changeset.put_change(:counter, counter)
    end
  )
  |> Repo.transaction()
  |> case do
    {:ok, %{work_order: work_order}} ->
      {:ok, work_order}

    {:error, :next_counter, _changeset, _changes} ->
      # Return work_order_changeset for more thorough field checks
      {:error, %{work_order_changeset | action: :insert}}

    {:error, :work_order, changeset, _changes} ->
      {:error, changeset}
  end
end

Have you configured the Ecto Sandbox?

It will isolate each test in a transaction and rollback the transaction so each test has a consistent view of the data. It will also make it much less likely you will encounter deadlocks.

Thanks kip. Yes, I believe this is Phoenix’s default, but I rechecked just in case. I also run into a unique index constraint error with another test but that’s rarer, I’d say 5% of the time, while deadlock is 10%.

Not a dealbreaker by any stretch of the imagination, and if it gets too annoying I can simply set async to false.

If you think there could be something wrong or missing with the way my Ecto.Multi is set up, please let me know, I’m new to Elixir and Phoenix and am keen to dive deeper.

Thanks again :slight_smile: