Ecto test multiple process transaction in sandbox with shared connection

I basically have a similar setup like the one described in the sandbox docs for a shared mode connection, which looks like this:

setup do
  :ok = Ecto.Adapters.SQL.Sandbox.checkout(Repo)
  Ecto.Adapters.SQL.Sandbox.mode(Repo, {:shared, self()})
end

test "create two posts, one sync, another async" do
  task = Task.async(fn ->
    Repo.insert!(%Post{title: "async"})
  end)
  assert %Post{} = Repo.insert!(%Post{title: "sync"})
  assert %Post{} = Task.await(task)
end

Which runs fine.
Now when I change this example slightly and wrap both inserts in a transaction:

test "create two posts, one sync, another async" do
  Repo.transaction(fn ->
    task = Task.async(fn ->
      Repo.insert!(%Post{title: "async"})
    end)
    assert %Post{} = Repo.insert!(%Post{title: "sync"})
    assert %Post{} = Task.await(task)
  end)
end

This leads to a timeout:

 ** (EXIT from #PID<0.478.0>) exited in: GenServer.call(#PID<0.479.0>, {:checkout, #Reference<0.0.3.2012>, true, 15000}, 5000)
     ** (EXIT) time out

.10:04:12.191 [error] Postgrex.Protocol (#PID<0.383.0>) disconnected: ** (DBConnection.ConnectionError) owner #PID<0.478.0> exited while client #PID<0.478.0> is still running with: exited in: GenServer.call(#PID<0.479.0>, {:checkout, #Reference<0.0.3.2012>, true, 15000}, 5000)
** (EXIT) time out

I am wondering if this is because I can’t use transactions with the shared mode or if I am missing something else. I am using PostgreSQL 9.6.2, Ecto 2.1.4 and Elixir 1.4.2

2 Likes

The answer is that Ecto transactions, in general, can’t span multiple processes.

Sorry to reply to an old thread … but does this mean that it is not possible to use Ecto.Adapters.SQL.Sandbox to test any functions that create their own transactions?

Or just functions that spawn processes that create their own transactions?

You probably want to test the spawned functions separately in that case. But you can create nested transactions form the same process, this should not be the issue at all. As long as you stay within the same process you should have no issues.

For the cases you have to spawn processes that do database operations, I would go with combination of async: false in your test cases, and switching to shared mode: https://hexdocs.pm/ecto/2.2.8/Ecto.Adapters.SQL.Sandbox.html#module-shared-mode

The end result should be that these tests will not run in parallel with other tests, so possibly would be slower than doing it in parallel, but there is nothing stopping you from havihng some tests running in parallel, and others not.

2 Likes

Thanks for the claification!

You can definitely do both things with Ecto.Adapters.SQL.Sandbox, but depending on exactly how the process is spawned you may need to manually ensure the connection gets shared using Ecto.Adapters.SQL.Sandbox.allow.

IIRC, on Elixir 1.8+ the original poster’s use of a Task spawned from the test process will automatically share the DB connection.

1 Like