How do you do database concurrency testing in Elixir?

Imagine a piece of code that accesses a database. This code makes several database requests, and follows different execution paths depending on the database state by the time each request is made. This is typical.

How do you automate the testing of this code so that its behavior is checked for correcteness in the presence of other concurrent database accesses that may interfere with it?

For example, one may say that load testing by issuing many concurrent requests to a web application, will generate lots of database concurrency, so if the test suite continues to pass after many runs, our confidence on code correctness increases. However, this approach is limited, because the inherent randomness does not ensure that all possible database interferences that may affect the executions paths of our code have been exercised.

So, how can database concurrency testing be done with the tools we have available in Elixir?

Any suggestions and ideas are welcome.


1 Like

this smells like bad data handling - can you give an example use case?

either way I would suggest using Ecto.Multi so that you make all your DB changes in a single transaction… and can rollback on error…

For more complex things you will want to start using locks, say you have item on sale and it has a quantity_left of 20 - if you do “orders” against that you’ll want to lock that row, make the order and update quantity_left - subsequently the next “order” can get a lock etc etc. making sure you never sell more than is available…

(you can of course also leverage OTP/GenServers etc)

The question is not how to synchronize across concurrent database accesses, whether done with locks, transactions, or something alike. The question is about how to test that the code behaves as expected in the presence of concurrent database accesses, and independently of the synchronization approach.

More simply, how do you test that your synchronization approach is working fine?

Right, the reason this is a challenge is that the default sandbox approach gives just 1 database connection per test case and runs the whole test case in a transaction that gets rolled back. Definitely not viable for testing what you want to test.

If I were doing it I’d probably have two test suits, the set designed to run normally, and the set designed not to run in the sandbox. You could alias mix test to be test: ["test", "run test/concurrent.exs"] and have concurrent.exs set the repo up with a normal pool, delete previous test data, and so on.

So, because this is something not supported in ExUnit, I suppose that most people don’t do this kind of testing in their Elixir programs …

What you suggest, correct me if I am wrong, is to run the usual ExUnit tests, and then, have a concurrent.exs script that will do all the database concurrency testing work. That’s one way to go, indeed, which fits quite nice with the mix alias feature.

Now I wonder how complex that concurrent.exs might be, but that’s another story :smiley:

Let’s see if someone else reading this suggests a different approach or knows about a tool for this kind of testing.

Thanks Ben :+1:

I guess it’s less that ExUnit does not support it, but rather that people tent do not test if their db (or ecto) works. That’s to be tested by the dbs/ecto team. You rather want to test if your own business logic is correct. Ecto’s sandbox is a tool to make tests run in isolation, while you seem to want to specifically not want to isolate db operations.

Maybe look at (stateful) property testing if you want to find defects in concurrent database access. I don’t have much experience with it, but it seems like a useful way to tackle testing for your case.

1 Like

I think the main thing that ExUnit is missing here is the idea of testing your application in multiple modes. The challenge with the Repo is that while technically the sandbox is a Repo configuration thing and not an ExUnit thing, if you want to change that configuration, you have to stop and restart the whole application basically. It isn’t particularly surprising that it doesn’t have tools for this, but that I think is why it feels like an ExUnit limitation.

@mguimas However I think I have a simpler solution, tags! In test modules or on individual test cases that should work this way, add the tag :no_sandbox. Then do this alias

test: ["test --exclude no_sandbox", "test --only no_sandbox"]

Then in test_helper.exs:

if :no_sandbox in ExUnit.configuration[:include] do
  # Use Application.put_env to change the ecto config to use the normal adapter

That’s to be tested by the dbs/ecto team.

I disagree because I am talking about testing if the application code behaves correctly in the presence of concurrent database accesses. This is testing application logic, not Ecto or the database itself.

Maybe look at (stateful) property testing …

Are you talking about Property-Based Testing with PropEr, Erlang, and Elixir ?