Ah yes I see, this is different. It happens because the transaction is hogging the connection process, so there aren’t any other available connections. I’m not entirely sure how to solve that 
Is it possible to do all of that lookup information before you open the transaction? This will help minimize the amount of time the transaction is open which is itself a perk, and then your transaction can focus on just doing the changes to the database.
It would be complex to do the lookup beforehand of the transaction, since it is some logic not directly related to the code in question that does the inner db lookup in another process.
Anyway, I solved this particular issue with a mock in my tests (and it runs fine in dev and prod)
Hi,
First thanks to everyone in this thread! I’ve been faced with a similar situation (using Cachex with Ecto calls), and faced the same troubles with regard to ownership - all your input has been very useful.
I’m stacking up a bit more links in case it helps someone later, because Cachex has worked reliably for us.
In my cases, it isn’t pratical to pass caller: xyz to Ecto, because it would involve changing a lot of code calls. Maybe there is a trick to set the caller for all the subsequent calls in a given block maybe, but I haven’t searched for that yet.
As described in Ecto.Adapters.SQL.Sandbox — Ecto SQL v3.14.0, if one wants to keep their tests async, then the shared mode cannot be used, and allowance must be used instead.
It can be tricky because Cachex creates a number of nested processes per cache, which one can see with:
import Cachex.Spec, only: [cache: 1]
# ...
Cachex.Services.services(Cachex.Spec.cache(name: :my_cache))
which gives:
[
{Cachex.Services.Janitor, #PID<0.734.0>, :worker, [Cachex.Services.Janitor]},
{Cachex.Services.Courier, #PID<0.733.0>, :worker, [Cachex.Services.Courier]},
{Cachex.Services.Locksmith.Queue, #PID<0.732.0>, :worker,
[Cachex.Services.Locksmith.Queue]}
]
The function passed to Cachex.fetch to lazily-compute the value also (apparently) runs in a different process.
So far the trick I’ve used is to wrap the call to “fetch” in a helper which enables the allowance during tests:
defmodule MyApp.Cache do
require Logger
def fetch(cache_key, value_fn) do
parent = self()
comp_fn = fn key ->
if Mix.env() == :test do
Ecto.Adapters.SQL.Sandbox.allow(DB.Repo, parent, self())
end
Logger.info("Generating cached value for key #{key}")
{:commit, value_fn.()}
end
{operation, result} = Cachex.fetch(:my_app_cache, cache_key, comp_fn, ttl: :timer.seconds(60))
# SNIP
Ultimately I will extract a behaviour out of this module, and use a stub in most of the tests, only keeping a real Cachex-interacting test for the test of MyApp.Cache itself.
If anyone has better solutions or more information, I’ll happily read you!
One gotcha with this - AFAIK there’s no way to “un-allow” a connection, and Ecto gets grumpy if you call allow with a second PID. This makes for intermittently-failing tests when there’s a worker pool, as you’ll only get an error when a worker gets reused.
We ran into this issue with :poolboy; our fix was to have workers return :stop in tests so they don’t get re-used. This works, EXCEPT for very occasionally when there’s already a process waiting for a pool worker when the call returns; a fast-path optimization in Poolboy hands the now-exiting worker PID to the waiting process, which then fails 