On 12/19/23 17:00, Kristian Nielsen wrote:
From a quick read of the link, the main point seems to be about what happens to INSERT/UPDATE/DELETE ("updates") in a REPEATABLE READ transaction.
That's part of it, definitely! But on top of that, there's a serious read safety issue--if you perform a mutation in a transaction, it breaks read isolation too! You can observe state from the middle of a separate transaction, or see some of a transaction's effects, then go on to *fail* to see other effects of the same transaction.
As is mentioned, the basic idea in InnoDB REPEATABLE READ is to use a logical snapshot at the time of transaction start to satisfy SELECTs. I think it was always obvious to me that this cannot work for updates. Updates have to modify the current version of the database, not some old version.
That's certainly one option, yes. Other databases choose other paths. For instance, you can apply writes to a transaction-local view of the database, and buffer their effects until commit time. That's the SI approach Postgres takes.
So I have always thought of REPEATABLE READ to be "repeatable" for read-only transactions spanning multiple selects.
That property does seem to hold! I can't seem to find any actual documentation to this effect though--you're the first person who's suggested it. Any chance you've got a source handy?
As is also remarked in the link, using statements for replication is also very complicated to do correctly in all corner cases, and in practise quite fragile, unfortunately.
Ah, I should clarify--all the behaviors I mentioned occur in single-node MySQL & MariaDB. No replication required. :-)
As to the SQL standard, I am not intimately familiar with it, but the inadequacy of the isolation levels as defined by the standard has been "folklore" in the community for decades. Unlike something like the C/C++ standard, the SQL standard seems to be far removed from the needs of users, at least in the applications that typically employ MySQL/MariaDB. And from my experience, people are resigned rather than hopeful that this could ever change. This is sad, as a good standard could help improve the portability of applications between different SQL implementations.
Very much agreed! Thanks for your thoughts. :-) --Kyle