Automating Hermitage to see how transactions differ in MySQL and MariaDB
Monastery is a new open-source tool that helps script concurrent transaction sessions like Hermitage and observe recent changes in database behavior.
You are getting early access to this article as a subscriber. Your support makes articles like this possible. Thank you.
Transaction isolation levels (e.g. Read Uncommitted, Read Committed, Repeatable Read, Serializable) in the official SQL standard are defined in terms of transaction anomalies like Read Skew, Lost Updates, etc. But the SQL standard itself is ambiguous (yes, even the latest 2023 version) and allows some silly behavior.
Let's motivate this entire article by looking at Dirty Writes. If a transaction can overwrite the writes of another concurrent (uncommitted) transaction, we might have Dirty Writes. If a transaction can see the writes of another concurrent (uncommitted) transaction, we might have Dirty Reads. No major database allows Dirty Writes, even though the SQL specification allows it. But Dirty Reads are allowed by databases with Read Uncommitted isolation levels (PostgreSQL doesn't have such a level).
Lorin Hochstein has a thought experiment for Dirty Writes. Roughly put, in a bowling alley, no one should be able to acquire a half a pair of shoes within concurrent transactions. We'll adapt this thought experiment to a format we’ll be able to execute on multiple databases at multiple isolation levels with a new open-source tool, Monastery.
DROP TABLE IF EXISTS shoes;
CREATE TABLE shoes (left_shoe TEXT, right_shoe TEXT, shoe_id INT PRIMARY KEY);
INSERT INTO shoes VALUES ('', '', 1);
---
t1: BEGIN;
t2: BEGIN;
t1: UPDATE shoes SET left_shoe = 'Lin' WHERE shoe_id = 1;
t2: UPDATE shoes SET left_shoe = 'Carlos' WHERE shoe_id = 1;
t2: UPDATE shoes SET right_shoe = 'Carlos' WHERE shoe_id = 1;
t1: UPDATE shoes SET right_shoe = 'Lin' WHERE shoe_id = 1;
t1: SELECT * FROM shoes; -- # Inconsistent results here would mean dirty reads not necessarily dirty writes.
t2: SELECT * FROM shoes; -- # Inconsistent results here would mean dirty reads not necessarily dirty writes.
t1: COMMIT;
t2: COMMIT;
t1: SELECT * FROM shoes; -- assert ({Lin, Lin, 1}) or ({Carlos, Carlos, 1})
t2: SELECT * FROM shoes; -- assert ({Lin, Lin, 1}) or ({Carlos, Carlos, 1})
To reiterate, we are asserting that a transaction cannot overwrite uncommitted writes by other concurrent transactions. If t1 or t2 read {Lin, Carlos, 1} or {Carlos, Lin, 1} we might be seeing Dirty Writes.
Passing assertions like this cannot prove that a database never exhibits Dirty Writes. Hermitage notes this too. Even Jepsen tests, despite being way more adversarial, cannot prove a database is bug-free. But simple tests like this are still surprisingly useful for characterizing isolation levels as we attempt to trigger various anomalies.
If we run this script with Monastery against PostgreSQL (18.1) at the Repeatable Read level we might get:
Postgres does not exhibit Dirty Writes.
And on Oracle's MySQL (9.7) at Repeatable Read we might get:
MySQL does not exhibit Dirty Writes either.
Alright, so the SQL standard isn’t useful in this regard because of ambiguities and allowed behavior like Dirty Writes. Instead, researchers use academic formalisms such as A Critique of ANSI SQL Isolation Levels and Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions to think about transaction isolation levels. These two major papers in particular were connected, popularized, and made more approachable by Jepsen.
But researchers and database developers are not always on the same page. Databases don’t all behave the same way even when using the same terms for isolation levels. So a decade ago Martin Kleppmann, the author of Designing Data Intensive Applications, started a project called Hermitage.
Hermitage contains a series of concurrent transaction tests you can run against major SQL databases across standard SQL isolation levels in order to observe and catalog the transaction anomalies (such as Dirty Writes) each isolation level is subject to in each database.
Separately, a few months ago, Jepsen published a report on Galera, an open-source multi-master MariaDB product by MariaDB plc. The report mentioned that Galera was subject to Lost Updates. This didn’t seem weird to me at first because MySQL itself is subject to Lost Updates according to Hermitage.
When I dug into MariaDB plc’s Galera in another article I noticed that, contrary to what Hermitage seems to indicate, MariaDB transactions in the default isolation level (Repeatable Read) are not trivially subject to Lost Updates. Again, Hermitage says that Repeatable Read in MySQL is subject to Lost Updates. MariaDB is a fork of MySQL that once billed itself as a drop-in replacement. What’s going on?
As it happens, Hermitage hasn’t really been updated for MySQL or PostgreSQL in the last decade, and MariaDB has diverged significantly from MySQL since then. One of the ways it diverged is that in 2024 it entirely changed the way transactions worked by implementing repeatable read and serializable isolation levels on top of snapshot isolation more similar to PostgreSQL (Serializable Snapshot Isolation) than MySQL (Two-Phase Locking).
And to make things more complex still, Percona reported earlier this year that MariaDB is actually subject to Lost Updates under contention, among other things, even after this switch to snapshot isolation. But again, Hermitage (and now Monastery) are not adversarial. Hermitage and Monastery only attempt to categorize simple scenarios.
All in all, it seemed like a good time to rerun the decade-old Hermitage tests against MySQL and MariaDB.
But Hermitage itself is simply instructions for manually running concurrent transactions. It is not automated and the instructions differ across databases (sometimes necessarily).
The instructions are also not always clear about how abort behavior changes when the isolation level gets stricter. For example, if an anomaly is not exhibited at Repeatable Read, Hermitage does not describe that the way it is not exhibited might change in Serializable, which can be confusing as you notice a different, but still valid, way the database handles the situation.
So I wrote a concurrent transaction runner, Monastery, and ported Hermitage tests to it. With Monastery I was able to recreate PostgreSQL behavior (table below) across all isolation levels the same way Hermitage reports.
(Postgres has no Read Uncommitted level, so ignore that column.)
| Test | Read Uncommitted | Read Committed | Repeatable Read | Serializable |
|---|---|---|---|---|
| g0-write-cycles-dirty-writes | N/A | OK | OK | OK |
| g1a-aborted-reads-dirty-reads | N/A | OK | OK | OK |
| g1b-intermediate-reads-dirty-reads | N/A | OK | OK | OK |
| g1c-circular-information-flow-dirty-reads | N/A | OK | OK | OK |
| otv | N/A | OK | OK | OK |
| pmp | N/A | FAIL | OK | OK |
| pmp-write-predicates | N/A | FAIL | OK | OK |
| p4-lost-update | N/A | FAIL | OK | OK |
| g-single-read-skew | N/A | FAIL | OK | OK |
| g-single-write-predicate | N/A | FAIL | OK | OK |
| g-single-predicate-read-skew | N/A | FAIL | OK | OK |
| g2-item-write-skew | N/A | FAIL | FAIL | OK |
| g2-predicate-read-write-skew | N/A | FAIL | FAIL | OK |
| g2-predicate-read-fekete-write-skew | N/A | FAIL | FAIL | OK |
In my tests, MySQL (table below) has exhibited the same or fewer anomalies at every isolation level today compared to the Hermitage run a decade ago. At the Repeatable Read isolation level, MySQL still fails to handle Hermitage’s P4 Lost Update and G-Single (Write Predicate) which academic research suggests Repeatable Read should handle.
| Test | Read Uncommitted | Read Committed | Repeatable Read | Serializable |
|---|---|---|---|---|
| g0-write-cycles-dirty-writes | OK | OK | OK | OK |
| g1a-aborted-reads-dirty-reads | FAIL | OK | OK | OK |
| g1b-intermediate-reads-dirty-reads | FAIL | OK | OK | OK |
| g1c-circular-information-flow-dirty-reads | FAIL | OK | OK | OK |
| otv | OK | OK | OK | OK |
| pmp | FAIL | FAIL | OK | OK |
| pmp-write-predicates | OK | OK | OK | OK |
| p4-lost-update | FAIL | FAIL | FAIL | OK |
| g-single-read-skew | FAIL | FAIL | OK | OK |
| g-single-write-predicate | FAIL | FAIL | FAIL | OK |
| g-single-predicate-read-skew | FAIL | FAIL | OK | OK |
| g2-item-write-skew | FAIL | FAIL | FAIL | OK |
| g2-predicate-read-write-skew | FAIL | FAIL | FAIL | OK |
| g2-predicate-read-fekete-write-skew | FAIL | FAIL | FAIL | OK |
In contrast, MariaDB (table below) handles all the anomalies that the academic research suggests Repeatable Read should handle.
| Test | Read Uncommitted | Read Committed | Repeatable Read | Serializable |
|---|---|---|---|---|
| g0-write-cycles-dirty-writes | OK | OK | OK | OK |
| g1a-aborted-reads-dirty-reads | FAIL | OK | OK | OK |
| g1b-intermediate-reads-dirty-reads | FAIL | OK | OK | OK |
| g1c-circular-information-flow-dirty-reads | FAIL | OK | OK | OK |
| otv | OK | OK | OK | OK |
| pmp | FAIL | FAIL | OK | OK |
| pmp-write-predicates | OK | OK | OK | OK |
| p4-lost-update | FAIL | FAIL | OK | OK |
| g-single-read-skew | FAIL | FAIL | OK | OK |
| g-single-write-predicate | FAIL | FAIL | OK | OK |
| g-single-predicate-read-skew | FAIL | FAIL | OK | OK |
| g2-item-write-skew | FAIL | FAIL | FAIL | OK |
| g2-predicate-read-write-skew | FAIL | FAIL | FAIL | OK |
| g2-predicate-read-fekete-write-skew | FAIL | FAIL | FAIL | OK |
MariaDB also handles one more anomaly than PostgreSQL does at the Read Committed level, though this is not required by the academic research. (Handling more anomalies than necessary at each level is always allowed. Isolation levels describe a minimum not a maximum bar for prohibited anomalies.)
But what’s more, the way MariaDB handles things is a little different from MySQL because of the switch to Snapshot Isolation from Two-Phase Locking. Let us look at how MySQL at Serializable isolation handles Dirty Writes.
$ ./monastery mysql 'root@tcp(localhost)/test' serializable examples/dirty-write.sql
┌────────┬──────────────────────────────────────────────────┬─────────────┬─────────────┬─────────────────┬──────────────────────────────────┬──────────────────────────────────────────────┐
│CLIENT │COMMAND │STARTED │ENDED │RESULTS │ERROR │ASSERT │
╞────────╪──────────────────────────────────────────────────╪─────────────╪─────────────╪─────────────────╪──────────────────────────────────╪──────────────────────────────────────────────╡
│setup │DROP TABLE IF EXISTS shoes; │18:58:52.180 │18:58:52.243 │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│setup │CREATE TABLE shoes (left_shoe TEXT, right_shoe │18:58:52.246 │18:58:52.298 │ │ │ │
│ │TEXT, shoe_id INT PRIMARY KEY); │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│setup │INSERT INTO shoes VALUES ('', '', 1); │18:58:52.303 │18:58:52.318 │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │BEGIN; │18:58:52.337 │18:58:52.343 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │BEGIN; │18:58:52.377 │18:58:52.387 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │UPDATE shoes SET left_shoe = 'Lin' WHERE shoe_id =│18:58:52.417 │18:58:52.428 │() │ │ │
│ │1; │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │UPDATE shoes SET left_shoe = 'Carlos' WHERE │18:58:52.457 │18:58:52.675 │() │ │ │
│ │shoe_id = 1; │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │UPDATE shoes SET right_shoe = 'Lin' WHERE shoe_id │18:58:52.537 │18:58:52.543 │() │ │ │
│ │= 1; │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │SELECT * FROM shoes; │18:58:52.578 │18:58:52.585 │({Lin, Lin, 1}) │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │COMMIT; │18:58:52.658 │18:58:52.668 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │UPDATE shoes SET right_shoe = 'Carlos' WHERE │18:58:52.681 │18:58:52.688 │() │ │ │
│ │shoe_id = 1; │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │SELECT * FROM shoes; │18:58:52.695 │18:58:52.707 │({Carlos, Carlos,│ │ │
│ │ │ │ │1}) │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │COMMIT; │18:58:52.715 │18:58:52.729 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │SELECT * FROM shoes; │18:58:52.737 │18:58:52.747 │({Carlos, Carlos,│ │OK ({Lin, Lin, 1}) or ({Carlos, Carlos, 1}) │
│ │ │ │ │1}) │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │SELECT * FROM shoes; │18:58:52.777 │18:58:52.785 │({Carlos, Carlos,│ │OK ({Lin, Lin, 1}) or ({Carlos, Carlos, 1}) │
│ │ │ │ │1}) │ │ │
└────────┴──────────────────────────────────────────────────┴─────────────┴─────────────┴─────────────────┴──────────────────────────────────┴──────────────────────────────────────────────┘
If you look closely at the timestamps above, t2's first update only completes after t1 has committed. MySQL handles conflicting concurrent writes by holding locks until a commit or abort.
MariaDB handles it a little differently, noticing the conflict and aborting t2.
$ ./monastery mysql 'root:root@tcp(localhost)/test' serializable examples/dirty-write.sql
┌────────┬──────────────────────────────────────────────────┬─────────────┬─────────────┬─────────────────┬──────────────────────────────────┬──────────────────────────────────────────────┐
│CLIENT │COMMAND │STARTED │ENDED │RESULTS │ERROR │ASSERT │
╞────────╪──────────────────────────────────────────────────╪─────────────╪─────────────╪─────────────────╪──────────────────────────────────╪──────────────────────────────────────────────╡
│setup │DROP TABLE IF EXISTS shoes; │18:59:30.112 │18:59:30.118 │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│setup │CREATE TABLE shoes (left_shoe TEXT, right_shoe │18:59:30.120 │18:59:30.130 │ │ │ │
│ │TEXT, shoe_id INT PRIMARY KEY); │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│setup │INSERT INTO shoes VALUES ('', '', 1); │18:59:30.133 │18:59:30.138 │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │BEGIN; │18:59:30.143 │18:59:30.147 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │BEGIN; │18:59:30.154 │18:59:30.157 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │UPDATE shoes SET left_shoe = 'Lin' WHERE shoe_id =│18:59:30.163 │18:59:30.170 │() │ │ │
│ │1; │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │UPDATE shoes SET left_shoe = 'Carlos' WHERE │18:59:30.173 │18:59:30.227 │ │Error 1020 (HY000): Record has │ │
│ │shoe_id = 1; │ │ │ │changed since last read in table │ │
│ │ │ │ │ │'shoes' │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │UPDATE shoes SET right_shoe = 'Lin' WHERE shoe_id │18:59:30.193 │18:59:30.198 │() │ │ │
│ │= 1; │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │SELECT * FROM shoes; │18:59:30.203 │18:59:30.208 │({Lin, Lin, 1}) │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │COMMIT; │18:59:30.223 │18:59:30.245 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │UPDATE shoes SET right_shoe = 'Carlos' WHERE │18:59:30.231 │18:59:30.234 │ │current transaction is aborted, │ │
│ │shoe_id = 1; │ │ │ │statement ignored │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │SELECT * FROM shoes; │18:59:30.238 │18:59:30.244 │ │current transaction is aborted, │ │
│ │ │ │ │ │statement ignored │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │COMMIT; │18:59:30.249 │18:59:30.253 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │SELECT * FROM shoes; │18:59:30.261 │18:59:30.271 │({Lin, Lin, 1}) │ │OK ({Lin, Lin, 1}) or ({Carlos, Carlos, 1}) │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │SELECT * FROM shoes; │18:59:30.277 │18:59:30.283 │({Lin, Lin, 1}) │ │OK ({Lin, Lin, 1}) or ({Carlos, Carlos, 1}) │
└────────┴──────────────────────────────────────────────────┴─────────────┴─────────────┴─────────────────┴──────────────────────────────────┴──────────────────────────────────────────────┘
Postgres handles it similarly: by aborting t2.
$ ./monastery postgres "dbname=postgres host=/var/run/postgresql" serializable examples/dirty-write.sql
┌────────┬──────────────────────────────────────────────────┬─────────────┬─────────────┬─────────────────┬──────────────────────────────────┬──────────────────────────────────────────────┐
│CLIENT │COMMAND │STARTED │ENDED │RESULTS │ERROR │ASSERT │
╞────────╪──────────────────────────────────────────────────╪─────────────╪─────────────╪─────────────────╪──────────────────────────────────╪──────────────────────────────────────────────╡
│setup │DROP TABLE IF EXISTS shoes; │18:56:41.560 │18:56:41.571 │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│setup │CREATE TABLE shoes (left_shoe TEXT, right_shoe │18:56:41.574 │18:56:41.584 │ │ │ │
│ │TEXT, shoe_id INT PRIMARY KEY); │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│setup │INSERT INTO shoes VALUES ('', '', 1); │18:56:41.588 │18:56:41.596 │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │BEGIN; │18:56:41.606 │18:56:41.612 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │BEGIN; │18:56:41.907 │18:56:41.913 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │UPDATE shoes SET left_shoe = 'Lin' WHERE shoe_id =│18:56:42.208 │18:56:42.214 │() │ │ │
│ │1; │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │UPDATE shoes SET left_shoe = 'Carlos' WHERE │18:56:42.507 │18:56:44.020 │ │pq: could not serialize access due│ │
│ │shoe_id = 1; │ │ │ │to concurrent update (40001) │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │UPDATE shoes SET right_shoe = 'Lin' WHERE shoe_id │18:56:43.107 │18:56:43.114 │() │ │ │
│ │= 1; │ │ │ │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │SELECT * FROM shoes; │18:56:43.408 │18:56:43.414 │({Lin, Lin, 1}) │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │COMMIT; │18:56:44.007 │18:56:44.014 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │UPDATE shoes SET right_shoe = 'Carlos' WHERE │18:56:44.025 │18:56:44.033 │ │current transaction is aborted, │ │
│ │shoe_id = 1; │ │ │ │statement ignored │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │SELECT * FROM shoes; │18:56:44.043 │18:56:44.049 │ │current transaction is aborted, │ │
│ │ │ │ │ │statement ignored │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │COMMIT; │18:56:44.307 │18:56:44.317 │() │ │ │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t1 │SELECT * FROM shoes; │18:56:44.607 │18:56:44.615 │({Lin, Lin, 1}) │ │OK ({Lin, Lin, 1}) or ({Carlos, Carlos, 1}) │
├────────┼──────────────────────────────────────────────────┼─────────────┼─────────────┼─────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│t2 │SELECT * FROM shoes; │18:56:44.907 │18:56:44.915 │({Lin, Lin, 1}) │ │OK ({Lin, Lin, 1}) or ({Carlos, Carlos, 1}) │
└────────┴──────────────────────────────────────────────────┴─────────────┴─────────────┴─────────────────┴──────────────────────────────────┴──────────────────────────────────────────────┘
Again, all of these are fine ways to handle the anomaly. None of the three databases exhibit Dirty Writes here.
Let's look a little closer at the Lost Updates situation that got me looking into all of this.
Lost Updates#
Lost Updates in the academic literature are not supposed to be possible at the Repeatable Read isolation level. Hermitage's instructions to (try to) trigger Lost Updates for Postgres (which will not exhibit this anomaly at Repeatable Read):
begin; set transaction isolation level repeatable read; -- T1
begin; set transaction isolation level repeatable read; -- T2
select * from test where id = 1; -- T1
select * from test where id = 1; -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 11 where id = 1; -- T2, BLOCKS
commit; -- T1. T2 now prints out "ERROR: could not serialize access due to concurrent update"
abort; -- T2. There's nothing else we can do, this transaction has failed
We'll translate this to Monastery as follows (from the Monastery repo):
$ cat hermitage/08-p4-lost-update.sql
drop table if exists test;
create table test (id int primary key, value int);
insert into test (id, value) values (1, 10), (2, 20);
---
t1: begin;
t1: $SHOW_ISOLATION;
t2: begin;
t2: $SHOW_ISOLATION;
t1: select * from test where id = 1;
t2: select * from test where id = 1;
t1: update test set value = 11 where id = 1; -- group cycle1
t2: update test set value = 11 where id = 1; -- group cycle1
t1: commit; -- group cycle1
t2: commit; -- group cycle1
The group comment is an assertion syntax in Monastery to ensure that one of these four statements fails.
On Postgres (18.1) I get:
$ ./monastery postgres "dbname=postgres host=/var/run/postgresql" repeatable-read hermitage/08-p4-lost-update.sql
┌──────────┬────────────────────────────────────────────────────────────┬──────────────┬──────────────┬────────────────────┬────────────────────────────────────────┬──────────────────────────────────────────────────┐
│CLIENT │COMMAND │STARTED │ENDED │RESULTS │ERROR │ASSERT │
╞──────────╪────────────────────────────────────────────────────────────╪──────────────╪──────────────╪────────────────────╪────────────────────────────────────────╪──────────────────────────────────────────────────╡
│setup │drop table if exists test; │13:55:21.747 │13:55:21.774 │ │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│setup │create table test (id int primary key, value int); │13:55:21.779 │13:55:21.796 │ │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│setup │insert into test (id, value) values (1, 10), (2, 20); │13:55:21.800 │13:55:21.815 │ │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │begin; │13:55:21.825 │13:55:21.831 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │SHOW transaction_isolation; │13:55:22.126 │13:55:22.133 │({repeatable read}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │begin; │13:55:22.427 │13:55:22.432 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │SHOW transaction_isolation; │13:55:22.726 │13:55:22.731 │({repeatable read}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │select * from test where id = 1; │13:55:23.026 │13:55:23.032 │({1, 10}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │select * from test where id = 1; │13:55:23.326 │13:55:23.334 │({1, 10}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │update test set value = 11 where id = 1; │13:55:23.627 │13:55:23.633 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │update test set value = 11 where id = 1; │13:55:23.926 │13:55:24.243 │ │pq: could not serialize access due to │ │
│ │ │ │ │ │concurrent update (40001) │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │commit; │13:55:24.226 │13:55:24.235 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │commit; │13:55:24.526 │13:55:24.536 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│cycle1 │1/4 errored │ │ │ │ │OK group: at least one error │
└──────────┴────────────────────────────────────────────────────────────┴──────────────┴──────────────┴────────────────────┴────────────────────────────────────────┴──────────────────────────────────────────────────┘
Postgres doesn't exhibit Lost Updates.
On MySQL (9.7) I get:
./monastery mysql 'root@tcp(localhost)/test' repeatable-read hermitage/08-p4-lost-update.sql
┌──────────┬────────────────────────────────────────────────────────────┬──────────────┬──────────────┬────────────────────┬────────────────────────────────────────┬──────────────────────────────────────────────────┐
│CLIENT │COMMAND │STARTED │ENDED │RESULTS │ERROR │ASSERT │
╞──────────╪────────────────────────────────────────────────────────────╪──────────────╪──────────────╪────────────────────╪────────────────────────────────────────╪──────────────────────────────────────────────────╡
│setup │drop table if exists test; │14:00:53.430 │14:00:53.767 │ │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│setup │create table test (id int primary key, value int); │14:00:53.772 │14:00:53.906 │ │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│setup │insert into test (id, value) values (1, 10), (2, 20); │14:00:53.911 │14:00:53.947 │ │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │begin; │14:00:53.977 │14:00:53.985 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │SELECT @@transaction_isolation; │14:00:54.278 │14:00:54.293 │({REPEATABLE-READ}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │begin; │14:00:54.579 │14:00:54.587 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │SELECT @@transaction_isolation; │14:00:54.877 │14:00:54.884 │({REPEATABLE-READ}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │select * from test where id = 1; │14:00:55.177 │14:00:55.188 │({1, 10}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │select * from test where id = 1; │14:00:55.478 │14:00:55.488 │({1, 10}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │update test set value = 11 where id = 1; │14:00:55.778 │14:00:55.787 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │update test set value = 11 where id = 1; │14:00:56.077 │14:00:56.406 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │commit; │14:00:56.378 │14:00:56.398 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │commit; │14:00:56.677 │14:00:56.691 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│cycle1 │0/4 errored │ │ │ │ │FAIL group: at least one error │
└──────────┴────────────────────────────────────────────────────────────┴──────────────┴──────────────┴────────────────────┴────────────────────────────────────────┴──────────────────────────────────────────────────┘
MySQL's Repeatable Read exhibits this anomaly in MySQL 9.7 just as it did in Hermitage's tests of MySQL 5.6.
Now let's see MariaDB (11.8.6):
./monastery mysql 'root:root@tcp(localhost)/test' repeatable-read hermitage/08-p4-lost-update.sql
┌──────────┬────────────────────────────────────────────────────────────┬──────────────┬──────────────┬────────────────────┬────────────────────────────────────────┬──────────────────────────────────────────────────┐
│CLIENT │COMMAND │STARTED │ENDED │RESULTS │ERROR │ASSERT │
╞──────────╪────────────────────────────────────────────────────────────╪──────────────╪──────────────╪────────────────────╪────────────────────────────────────────╪──────────────────────────────────────────────────╡
│setup │drop table if exists test; │14:06:07.592 │14:06:07.597 │ │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│setup │create table test (id int primary key, value int); │14:06:07.600 │14:06:07.608 │ │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│setup │insert into test (id, value) values (1, 10), (2, 20); │14:06:07.611 │14:06:07.620 │ │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │begin; │14:06:07.626 │14:06:07.630 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │SELECT @@transaction_isolation; │14:06:07.926 │14:06:07.930 │({REPEATABLE-READ}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │begin; │14:06:08.227 │14:06:08.230 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │SELECT @@transaction_isolation; │14:06:08.526 │14:06:08.530 │({REPEATABLE-READ}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │select * from test where id = 1; │14:06:08.826 │14:06:08.830 │({1, 10}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │select * from test where id = 1; │14:06:09.126 │14:06:09.130 │({1, 10}) │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │update test set value = 11 where id = 1; │14:06:09.426 │14:06:09.430 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │update test set value = 11 where id = 1; │14:06:09.726 │14:06:10.031 │ │Error 1020 (HY000): Record has changed │ │
│ │ │ │ │ │since last read in table 'test' │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t1 │commit; │14:06:10.026 │14:06:10.038 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│t2 │commit; │14:06:10.326 │14:06:10.331 │() │ │ │
├──────────┼────────────────────────────────────────────────────────────┼──────────────┼──────────────┼────────────────────┼────────────────────────────────────────┼──────────────────────────────────────────────────┤
│cycle1 │1/4 errored │ │ │ │ │OK group: at least one error │
└──────────┴────────────────────────────────────────────────────────────┴──────────────┴──────────────┴────────────────────┴────────────────────────────────────────┴──────────────────────────────────────────────────┘
And MariaDB prevented the anomaly. Nice work MariaDB!
Let's talk a bit about Monastery.
On Monastery#
There are a few things to keep in mind with Monastery.
First, runs are inherently racy. The default behavior is to wait 300ms before scheduling the next query to be sent to whatever client. It must not be zero otherwise you will likely not get the interleaving behavior. However you also cannot run all queries on the same thread since some transactions may block until another concurrent transaction aborts or commits.
Second, the way assertions work in Monastery is hacky. Jepsen’s graph-based rules are both more flexible and more precise. I’m not sure if Monastery has much of a future, but it seems at least mildly useful for scripting and visualizing concurrent transactions. And furthermore I'm not convinced I've set up every assertion correctly. The assertions might be stricter than necessary, but probably aren't looser than required because it produces the same results Hermitage does.
Third, Monastery is less general than Hermitage. Since Hermitage is just written instructions, it has been adapted to non-SQL transactional databases like FoundationDB. Still I think Monastery has some benefits because it more clearly lets you see how the behavior changes at every isolation level.
Fourth, to reiterate, Monastery (like Hermitage) only attempts to categorize a database's best behavior. In contrast Jepsen attempts to catch databases in the act, doing things their own docs say they don't do.
But ultimately Monastery is just a concurrent SQL transaction runner. You can write plugins for other databases. You can run whatever concurrent transactions you'd like. You don't have to use assertions. If you notice any bugs please let me know!
Takeaways#
On the one hand this is just another example of how MariaDB is no longer a drop-in replacement for MySQL. On the other hand Uber famously replaced PostgreSQL with MySQL in 2016. And one of the largest MySQL users, Shopify, is currently replacing MySQL with Yugabyte (which is an offshoot of PostgreSQL). So there’s obviously, at least occasionally, an extremely large tolerance for switching everything about the system wherein you store data.
And on the Hermitage front, it is excellent work, but before trusting it as is, it is worth re-running scripts either manually or with Monastery as development continues on these databases.
Phil is the founder of The Consensus. Before this, he contributed to Postgres products at EnterpriseDB, cofounded and led marketing at TigerBeetle, and was an engineering manager at Oracle. He runs the Software Internals Discord, the Software Internals Email Book Club, and co-runs NYC Systems. @eatonphil
Enjoyed this article? Subscribe for unlimited access and to help us keep producing excellent articles.
Noticed a mistake? Have a question or comment? Write to the editor.