3 ms·
I knew of this issue for some time (default DB isolation is less than SERIALIZABLE on most DB). But assuming you do not want to change the default isolation fo
by skyde 3y ago
I knew of this issue for some time (default DB isolation is less than SERIALIZABLE on most DB).
But assuming you do not want to change the default isolation for your transaction or for the whole DB.
How do you write unit-test that check your code will work correctly because it's using "select balance from accounts FOR UPDATE"?
It seem to me the only way to really validate this is using tool like TLA+.
Or something like https://github.com/microsoft/coyote https://github.com/microsoft/coyote
- skyde 3y agoEven using tool like this it seem to be a combinatorial explosion issue. If your app contain 10 distinct transactions. you have to test what happen if 2 instances of tx #1 run concurrently. But also if tx #1 and tx #2 run concurrently. And also what if tx #1 and tx #3 run concurrently ....
- amw-zero 3y agoThe issue is that the race condition exists in the database, not in any application code. So you can either simulate the race, or you need to integration test, and that's outside the scope of TLA+ and model checking. I haven't heard of Coyote (it's still amazing how many tools are out there). It's good that it operates on the actual code level, but I'm still not sure that would reproduce concurrency non-determinism at the database level.
- skyde 3y agoyes if your test are using a mock of the db ex: https://github.com/microsoft/coyote/blob/main/Samples/AccountManager/AccountManager/InMemoryDbCollection.cs https://github.com/microsoft/coyote/blob/main/Samples/Accoun... you can simulate the races. The problem is your mock need to implement the same isolation level as your real DB and support transaction ... You could use SQLITE in memory DB to run your test but that would make your test a lot slower I assume.
- amw-zero 3y agoExactly, it's a tricky problem. Implementing all transaction isolation levels in a mock is quite an ambitious endeavor.
- sophiabits 3y agoThe problem is that you don’t really know if your mock accurately implements the behavior of the database. The only way you could verify the mock behaves correctly would be to run the mock and a real database instance through a set of tests to verify they both implement different isolation levels identically. At that point—why bother with the mock at all? Cutting out the intermediary and running integration tests of your application against a real database will be faster. You can’t use SQLite here either because all SQLite isolation levels are serializable anyway, which is _very_ different from how PostgreSQL works. Testing against SQLite could end up giving you a false sense of security that your code is safe against race conditions, whereas in reality it’s vulnerable when connected to a PostgreSQL server because of the difference in default isolation levels At this level of testing detail the only real option is to test against a real database that matches what you’re running in production. Otherwise you’re just testing a mock.
- skyde 3y agoI didn't know that Thanks for your comment. Except in the case of shared cache database connections with PRAGMA read_uncommitted turned on, all transactions in SQLite show "serializable" isolation
- mrloba 3y agoIt's not perfect, but I have had success running postgres in Docker and running integration tests against that. Usually you can trigger the problem by running a handful of queries in parallel.