Isolation guarantees when testing with Ecto.Adapters.SQL.Sandbox

[miguel]

If I understand correctly, support for concurrent tests with Ecto relies on the fact that transactions are started for each test and then rolled back after each test. If the test itself has transactions (i.e., multi), then Ecto will use nested transactions via savepoints to provide similar behavior. My question is about the word “similar” here.

If there is a function being tested that relies on the isolation properties of a SQL transaction, won’t that be lost when running the test? There isn’t any isolation between concurrent queries within a transaction and when running a test, because the sandbox has an “outer” transaction, the “inner” transactions aren’t actually transactions—they are just queries with savepoints.

Now, I understand that this might not be an issue as usually functions rely on the atomicity of transactions and not the isolation, but what if a function did? Wouldn’t this result in different behavior in the test and outside? I’m trying to understand the trade-offs made by Ecto’s sandbox.

Thanks in advance for reading this!

[potz]

I find myself asking the same question over and over. Interested to see if anyone will shed some light here.

[al2o3cr]

@miguel

Now, I understand that this might not be an issue as usually functions rely on the atomicity of transactions and not the isolation, but what if a function did? Wouldn’t this result in different behavior in the test and outside?

Absolutely - if you’re depending on that part of transaction behavior, you’d want to make your test case async: false and then check out a DB connection with Ecto.Adapters.SQL.Sandbox.checkout(SomeRepo, sandbox: false)

[miguel]

@al2o3cr, great, thanks for the clarification! This is what I assumed but I wanted to make sure it was the intended way of using it.