On 12/21/23 05:14, Marko Mäkelä wrote:
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.
Oh that's *very* interesting! I hadn't heard of this--I'll have to take a look.
Also, in InnoDB, in any read view, the changes of the current transaction will be visible.
That's fine! Internal consistency requires transactions observe their own prior effects. One of the problems here is that InnoDB goes *too* far--not only do you observe your own transaction's previous effects, but you also observe new (some, but not necessarily all) effects from *other* transactions.
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
Oh that's *very* interesting! MDEV-26642 in particular--I'm guessing you're right, it's something related to the behavior Alvaro and I identified where updates break snapshots in some way. I'll bet you we might have similar cases lurking in our histories, but maybe it depends on predicates instead of kv writes.
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.
Ah, that does look tricky! I'm afraid I've skimmed some of the literature on isolation level mixing but I do *not* understand it, haha. Perhaps someday. :) Thanks so much, Marko! --Kyle