Database deadlock during controller tests

Phoenix Forum Questions / Help
,
1

I’ve been experiencing intermittent deadlocks when running controller tests. I get the following error sometimes, for certain tests. I’m doing pretty generic CRUD operations. The only time it’s deadlocking is on this specific insert!(), but it’s doing it in a few different tests or sometimes not at all. How else could I start trying to debug this? Thanks!

(Mariaex.Error) (1213): Deadlock found when trying to get lock; try restarting transaction
 code: group = Repo.insert!(%Group{name: "My Group"})
 stacktrace:
   (ecto_sql) lib/ecto/adapters/mysql.ex:219: Ecto.Adapters.MySQL.insert/6
   (ecto) lib/ecto/repo/schema.ex:603: Ecto.Repo.Schema.apply/4
   (ecto) lib/ecto/repo/schema.ex:226: anonymous fn/15 in Ecto.Repo.Schema.do_insert/3
   (ecto) lib/ecto/repo/schema.ex:128: Ecto.Repo.Schema.insert!/3

My test file is fairly simple and looks something like this:

defmodule MyApp.V1.Admin.MyControllerTest do
  use MyApp.ConnCase, async: true

  test "POST /some-path", %{conn: conn} do
    # insert some data, this line triggers the deadlock
    group = Repo.insert!(%Group{name: "My Group"})
    
    # make a request and assert
  end
end

defmodule MyApp.ConnCase do
  use ExUnit.CaseTemplate

  using do
    quote do
      # Import conveniences for testing with connections
      use Phoenix.ConnTest
      import MyApp.Router.Helpers

      alias MyApp.Repo

      # The default endpoint for testing
      @endpoint MyApp.Endpoint
    end
  end

  setup tags do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo)

    unless tags[:async] do
      Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, {:shared, self()})
    end

    {:ok, conn: Phoenix.ConnTest.build_conn()}
  end
end
2

I was reviewing the Ecto book again and in the section on Sandbox mode it recommends not using async: true with MySQL as you may run into deadlocks. Unlike PostgreSQL, MySQL doesn’t seem to support concurrent test with SQL Sandbox.

2 Likes