hermitage

MySQL Serializable is pretty similar to serializable in other databases, in terms of the observable anomalies. There's a good set of tests here: https://github.com/ept/hermitage > So maybe I'm supposed to consider this post only for 'Every transactional system' running with SERIALIZABLE transaction isolation. No, it's a general point about the nature of transactions in DBMSs, and the different implementation choices. As the article says, there are some variations (e.g. MVCC at levels lower than serializable inherently has two 'order' steps).

Beyond the Kleppmann Hermitage work that is linked in this post? https://github.com/ept/hermitage

> Cockroach only allow SERIALIZABLE transactions so the unviability of SERIALIZABLE seems questionable. We've actually been hard at work on adding Read Committed and Repeatable Read isolation into CockroachDB. The risks of weak isolation levels are real, but they do have a role in SQL databases. We did our best to avoid the pitfalls and inconsistencies of MySQL and even PostgreSQL by defining clear read snapshot scopes (statement vs. transaction). The preview release for both will be dropping in Jan. Some links if you're interested: - RFC: https://github.com/cockroachdb/cockroach/blob/master/docs/RF... - Hermitage test: https://github.com/ept/hermitage/blob/master/cockroachdb.md

There are some controversy about definitions of isolation levels in SQL standard. For example absence of mentioned phenomenas doesn't guarantees serialisability, as it shown in Berenson paper [1]. Implementation independent description of weak isolation levels can be found in Adya phd thesis [2] (and that's a tough read). Also nice evaluation of modern databases in terms of [2] can be found in Martin Kleppmann's github [3]. [1] https://www.microsoft.com/en-us/research/publication/a-criti... [2] http://pmg.csail.mit.edu/papers/adya-phd.pdf [3] https://github.com/ept/hermitage

I agree with the author that the various levels of isolation, etc. within the current crop of SQL databases is a morass. I’ll point to some recent fine work by Martin Kleppmann (https://github.com/ept/hermitage) that explores the issue and shows how many systems fall short of serializable isolation in their default modes. (And sometimes in modes labeled “serializable”!) In his test three databases actually achieve full serializability: PostgreSQL, MS SQL Server, and FoundationDB. But don’t give up on ACID yet! If can actually get real serializability, you have a very powerful model that is also very simple to reason about. Serializable isolation gives you a virtual “whole database lock” that lets you modify lots of things all over the database before you “unlock” it and hand the database to the next client. The amazing thing about MVCC co

Just submitted a pull request for FoundationDB: https://github.com/ept/hermitage/pull/1

What you describe is the Lost Update (P4) anomaly. It's in the test suite: https://github.com/ept/hermitage/blob/master/mysql.md#lost-u... MySQL in repeatable read mode actually doesn't prevent this anomaly (it doesn't deadlock). You need to be in serializable mode to get the deadlock.