5 ms·
Does your test suite account for weak transaction isolation?
- kccqzy 3y agoI'd argue that thinking about testing this isn't really worthwhile. Just change isolation level. It's not terribly slow. Your work is now much easier.
- emergie 3y agoSerialized usually is prohibitively slow in case where there are more than few concurrent users.
- skyde 3y agoI 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.
- 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.
- nick__m 3y agoWhy optimistic locking is never considered in those articles ? Adding a version column and checking the affected numbers of row scales a lot better that a select for update...
- amw-zero 3y agoProbably because it’s more work, and it’s logic that you have to write and get correct. Whereas “for update” pretty much just works. But there are definitely many valid solutions, including just setting the specific transaction to serializable and handling transaction retries.
- GauntletWizard 3y agoand handling retries is something I almost never see, and more infrequently see done right.
- gav 3y agoOptimistic locking is a great solution to avoid holding a lock for an indeterminate period of time as you wait for a user or external system to provide updates.
- hinkley 3y agoI don’t know about databases but I’ve done similar with testing interactive web apps and it works quite well. When your architecture supports it, at least. Essentially a hidden field on the page that tracks interactions and outstanding Ajax calls. When you assert that a field on the page hasn’t changed, you need to know that it’s not still in the process of painting. And just waiting “long enough” makes for very slow testing overall.
- skyde 3y agobecause row version does not make it possible to update more than 1 row atomically.
- giovannibonetti 3y ago
- trhway 3y ago>Hopefully it’s clear why this is an issue. If you have an important column value, say a user’s account balance, you might query multiple different values in the same transaction and that is a feature, not a bug. During your transaction user withdrew money from his account. If you wanna see the same value, i.e. block that user from withdrawals while you process his current balance, you take a lock on that row. Similar in many ways to the concurrent programming with threads.
- aphyr 3y agoThe point of isolation levels is that the database manages those locks (or other concurrency control mechanisms) for you.
- wrs 3y agoBearing in mind that “handling” the locks may mean a deadlock is detected and the transaction is aborted by the DB, which the application has to handle by retrying in a correct manner. (Which is another point often missed in naive application code.)
- trhway 3y agoNot exactly - the database doesn't know whether you want to block that account update or not. So, speaking very roughly, it allows you to choose general level of such a blocking behavior. It manages those locks, very coarse, at serializable and some of those locks at repeatable read. At read committed it manages basically only small set of locks just to avoid dirty reads (plus of course all the WAL/rollback/etc. which is the main features and the point of the database with the isolation levels being just the icing on that cake). Isolation levels is a tool, and you choose what is needed and suitable. The serializable actually is a pain in the neck for any meaningfully serious large enterprise application. The decreased concurrency kills overall performance, and I haven't seen any such application running in serializable (and for example at my current job our customers are running our application on servers with low tens of TB RAM (largest so far was 40TB RAM) per single machine with high hundreds of cores with thousands of users - in serializable that would slow to a crawl as it wouldn't allow to achieve the concurrency the database on those machines is otherwise capable of). While not precise illustration it still communicates my point - the serializable against read committed is like the global Python lock against the fine grained locks in normal languages.
- agentultra 3y agoGenerally, for a lot of application usage patterns, read committed is the default for a reason. From the Postgres docs: > The partial transaction isolation provided by Read Committed level is adequate for many applications, and this level is fast and simple to use. However, for applications that do complex queries and updates, it may be necessary to guarantee a more rigorously consistent view of the database than the Read Committed level provides. Also, consider storing the transactions that change the balances (credits, debits, etc) in a ledger and calculate the balance. You can avoid the complex update logic and keep your accountants and auditors happy. Stricter isolation levels are useful but have a lot of trade-offs to be aware of. Sometimes restructuring your data is a lot more effective than trying to reason about locks and concurrency. The more locks you have to more points of contention, dead locks, etc you have to be concerned about. Humans and general rules/folklore are alright at avoiding some mistakes but these systems get complicated quickly and can hide a surprising number of errors. As for testing these things, I've gotten a lot of good mileage out of model checking. TLA+ and Alloy are better at finding concurrency errors than I am at avoiding them.
- karamanolev 3y agoCan you recommended a good article that goes over the ledger pattern (the easy part) and the performant calculation of balances as the ledger grows? I'm going to start working on a warehouse management system in the coming months and that would come in handy.
- SoftTalker 3y agoI don't have a link but when I've implemented systems with a "ledger" of changes I would have a field that indicated whether the ledger item had been "posted" to the the affected account(s) and therefore the balance was just the account balance +/- any unposted ledger items. I've had ledgers that were posted once a day, or more often, up to nearly real-time. But once a ledger item is posted, it should never be changed. If an error is discovered, make a new ledger entry to correct it.
- moring 3y agoThe pattern in general is called "event sourcing", but unfortunately it's popular enough to make a lot of bad articles appear, and I can't recommend a good one.
- defaultcompany 3y agoI made a little project called Concurrency Runner a while ago to attempt to test these kinds of scenarios: https://github.com/weinberg/concurrencyRunner https://github.com/weinberg/concurrencyRunner. It does so by running multiple copies of your process in debug mode with breakpoints set to allow specific interleaved execution paths to be explored. This allows you to trigger concurrency scenarios which are difficult to analyze because they otherwise would rely on random timing. That repo has examples which demonstrate "read skew", "write skew" and "read modify write" concurrency scenarios. I was hoping I could make it into something you could run in CI to actively test for these things. Ultimately it was more of a research project than anything else but maybe someone will find it useful or interesting.
- amw-zero 3y agoThat’s really cool. It reminds me of Meta’s hermit, which intercepts system calls and records them so that they can be replayed back deterministically. Non-determinism is the name of all testing. Anything that we can do to improve it is extremely important.
- hmeh 3y agoThis and other concurrency problems often fall into the “you just have to know how to not get it wrong” camp. If you know to write an automated test for it, you probably know enough to reason about it and get it right. For very important things, sure, maybe write a test, but these tests are very expensive to write and maintain. It’s often cheaper to recognize (and train other team members to recognize) these scenarios and use a higher isolation level. Postgres makes this easy because serializable doesn’t come with a huge perf penalty (mostly memory, I believe). I’m strongly in the TDD camp too, btw. So I don’t say this flippantly. I appreciate this article bringing further awareness to this. Also, I cannot recommend partitioning (and event sourcing) enough to eliminate and simplify these problems. Article on partitioning: https://github.com/aaronjensen/software-development/blob/master/partitions-and-compositions.md#partitions-and-compositions https://github.com/aaronjensen/software-development/blob/mas...