Database operations within a task within a transaction in test mode hang indefinitely

[elixir-ecto]

Database operations within a task within a transaction in test mode hang indefinitely

[michalmuskala]

In general, transactions cannot span across processes - either during tests with sandbox or regular operation. We could potentially improve the error here, though.

[dkulchenko]

That makes sense, but in this case I'm not intending for the outer transaction to be reused in the task process - it's meant to be its own stream of work (and if I understand correctly, this is what happens running the same code in a non-test environment, the task would check out a separate connection).

But spawning that task from within an open transaction during a test prevents it from executing at all, which doesn't seem right.

The example above is simplified to the minimum to trigger the error - in my real project, the outer transaction is doing its own work and happens to spawn a few tasks as well. This works fine in dev, but in test, the above happens.

[josevalim]

@dkulchenko when using the sandbox, the connections are either explicitly assigned or shared. So one of two things will happen in your tasks:

1. You have a single connection, which you are trying to use across all processes, which causes it to fail

2. The task does not have a connection assigned to it, which causes it to fail

We added automatically sharing for tasks in a sandbox in recent versions, which means you are triggering 1. But without 1, you would just trigger 2.

[josevalim]

In any case, here is a PR that improves the error message in this case: elixir-ecto/db_connection#200

[josevalim]

Closing in favor of the PR above. :)

[dkulchenko]

@josevalim Understood - thank you for the explanation :) Will rewrite things a bit so that the one shared connection can be used.

[igsp7]

I am facing the exact same situation. @dkulchenko how did you manage to make your Tasks use the shared connection?

[dkulchenko]

I pulled up the old project that I ran into this with and it looks like my solution was to untangle the cyclic dependency.

Basically, the test case above hangs because the async task is trying to use the sandbox connection, but the parent task has it checked out for the transaction and can't release it (because the transaction call itself is blocking on the async task completing).

The real world case I was encountering this in was calling an audit function that needed to (in the background) add some auditing rows to the DB (and this function was itself called from within a parent transaction). I ended up moving the auditing code to run in a Task.Supervisor and not blocking on the call inside the parent transaction.

I also added some "real" DB tests that run against a test DB with sandbox mode disabled and exercise the code using the normal path.

Looking through the old code, it looks like my approaches were essentially to have a wrapper MyProject.Tasks module that selectively inlined async tasks in test (to not spawn a separate task at all) or to untangle dependencies using non-blocking/non-immediately-awaited tasks, keeping in mind there's only one connection available at a time in the test sandbox. This can be supplemented with non-sandboxed tests as needed to fill in any coverage gaps.

This was really early in my Elixir learning so it's possible that newer releases of Ecto have mitigated this in other ways, or perhaps there's better approaches.

[igsp7]

Thank you very much for the detailed explanation @dkulchenko