Testing race condition fix (using SELECT ... FOR UPDATE) with Ecto Sandbox

Currently we identify a possible race condition where 2 processes could possibly update the same records with 2 different database transactions. We manage to fix this by adding the following in our query:

lock: fragment("FOR UPDATE")

After adding the above, the DB transaction in the second process will only be executed after the first DB transaction is done. So it sort of works.

The problem is I can’t really write a valid test for this scenario. I believe this is due to Ecto Sandbox only accepts 1 connection. So with or without the lock: fragment("FOR UPDATE"), my test will always pass.

I have been searching for a solution, and trying all sorts of ways. Would hope to see if there are anyone else facing the same issue, and how do you overcome it?

You cannot test this within the sandbox, but you can use Repo.put_dynamic_repo + some config changes to start a standalone repo without the sandbox and cleanup manually.

1 Like