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

Questions / Help
,
1

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?

2

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.

2 Likes
3

I’m having the exact same problem as @nthock and I tried to understand how to implement the solution suggested by @LostKobrakai , but I can’t seem to get my head around it. Could anyone be kind enough to suggest a direction that may help me solve this ? Example code somewhere ? An explanation with just a little bit more meat to understand the subject ? Documentation ?

4

A coworker came up with this solution to test concurrent transactions:

  def unboxed(f, on_exit \\ nil) do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo, sandbox: false)

    try do
      f.()
    after
      on_exit && on_exit.()
      Ecto.Adapters.SQL.Sandbox.checkin(MyApp.Repo, sandbox: false)
    end
  end

The f callback talks to the “normal” database. So in a test you spawn 2 processes and each process will have a call to unboxed/1.

3 Likes
5

Thank you so much! That really helped!

1 Like