Hi Kyle, On Tue, Dec 19, 2023 at 6:03 PM Kyle Kingsbury via discuss <discuss@lists.mariadb.org> wrote:
I've just completed an analysis of isolation levels in MySQL. The report focuses on MySQL, but the test suite runs against MariaDB as well. MariaDB exhibits all the same behaviors--"Repeatable Read" allows a whole bunch of anomalies that should be prohibited under Repeatable Read, including G2-item, G-single (even without predicates), non-repeatable reads, violations of Monotonic Atomic View, etc. Figured this might be of interest to the list.
I really appreciate your efforts. Heikki Tuuri implemented locking reads in a peculiar way: They are actually always READ COMMITTED, and the read view is being ignored. I do not remember it clearly, but I heard from a former member of the MySQL Falcon storage engine team that they called some of the InnoDB anomalies "write committed". It could be exactly this. Also, in InnoDB, in any read view, the changes of the current transaction will be visible. InnoDB’s READ COMMITTED is like REPEATABLE READ, but it will suppress some next-key locking, and the read view (the set of transactions that will be considered as committed) will be refreshed at statement boundaries. We have a few bug reports that I think could be related to your findings: https://jira.mariadb.org/browse/MDEV-32898 https://jira.mariadb.org/browse/MDEV-26642 https://jira.mariadb.org/browse/MDEV-26643 In https://jira.mariadb.org/browse/MDEV-14589 there is some discussion related to the problems of using SERIALIZABLE isolation level for some but not all read/write transactions. I hope that this helps you identify the exact root causes of the anomalies that you found. I think that it could make sense to implement some additional isolation levels. A starting point for that would be a minimal patch that would make the test pass (never mind that it could break compatibility with other applications). Locking can be really tricky. https://jira.mariadb.org/browse/MDEV-27025 and https://jira.mariadb.org/browse/MDEV-27992 are a good example. InnoDB would treat an UPDATE of the PRIMARY KEY as a DELETE and an UPDATE. The regression scenario involved updating a PRIMARY KEY to a smaller value. There are also race conditions, such as https://jira.mariadb.org/browse/MDEV-15326 which I think could still be open in MySQL. With best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc