Transaction isolation is one of those topics that looks settled until you try to verify behavior on a current database build. The SQL standard gives names like Read Uncommitted, Read Committed, Repeatable Read, and Serializable, but the practical meaning of those labels depends on which anomalies a database actually prevents. That gap matters. Two engines can advertise the same isolation level and still behave differently under the same concurrent workload. ⚠️
That is the motivation here. Older isolation-level catalogs have been useful for years, but many of the recorded outcomes for major databases were gathered a long time ago. In the meantime, MySQL has evolved, MariaDB has moved further away from MySQL, and transaction internals have changed enough that some old assumptions are no longer reliable. To revisit the landscape, a new open-source tool called Monastery scripts concurrent transaction sessions in the style of Hermitage and makes their behavior easier to reproduce and inspect.
Isolation levels in SQL are typically described in terms of anomalies: Dirty Reads, Lost Updates, Read Skew, and so on. That sounds precise, but the standard leaves room for ambiguity and even permits behavior nobody really wants. A good example is Dirty Writes.
If one transaction can overwrite another transaction’s uncommitted write, you may have a Dirty Write. If one transaction can merely observe another transaction’s uncommitted change, you may have a Dirty Read. In practice, major databases do not permit Dirty Writes, even though the standard leaves space for them. Dirty Reads, however, can still appear at Read Uncommitted in systems that support that level.
A simple way to reason about Dirty Writes is with a “pair of shoes” scenario: no concurrent process should be able to walk away with half of one pair and half of another. That idea translates cleanly into an executable test.
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})The important check is the final state. If either transaction can end up observing {Lin, Carlos, 1} or {Carlos, Lin, 1}, that suggests one transaction partially overwrote another’s uncommitted work. ✅ Passing this test does not prove Dirty Writes are impossible in all circumstances, but it is still a useful characterization tool.
Hermitage has been valuable because it catalogs concurrent transaction tests across popular SQL databases and their named isolation levels. The problem is that Hermitage itself is essentially a set of manual instructions. It is not an automated runner, and its steps vary by database. That makes it harder to replay consistently, especially when stricter isolation levels suppress an anomaly in different ways, such as blocking, aborting, or serializing later.
Monastery was written to address that gap. It scripts concurrent sessions, schedules statements with small delays so interleavings still happen, and records results in a single run. That made it possible to rerun classic Hermitage-style tests against current PostgreSQL, MySQL, and MariaDB versions. 🛠️
This became especially interesting because MariaDB has changed substantially. It used to present itself as a drop-in MySQL replacement, but that picture no longer holds. In 2024, MariaDB reworked its implementation of Repeatable Read and Serializable to use snapshot-isolation-style behavior closer to PostgreSQL’s approach than to MySQL’s two-phase locking model. That raised a practical question: do modern MariaDB isolation guarantees now differ from MySQL in observable ways? The answer is yes.
Monastery reproduced the same PostgreSQL pattern traditionally reported for Hermitage-style anomaly checks across all supported isolation levels. PostgreSQL does not offer Read Uncommitted, so that column is effectively absent. The outcomes were:
g0-write-cycles-dirty-writes: OK at Read Committed, Repeatable Read, Serializableg1a-aborted-reads-dirty-reads: OK at Read Committed, Repeatable Read, Serializableg1b-intermediate-reads-dirty-reads: OK at Read Committed, Repeatable Read, Serializableg1c-circular-information-flow-dirty-reads: OK at Read Committed, Repeatable Read, Serializableotv: OK at Read Committed, Repeatable Read, Serializablepmp: FAIL at Read Committed, OK at Repeatable Read and Serializablepmp-write-predicates: FAIL at Read Committed, OK at Repeatable Read and Serializablep4-lost-update: FAIL at Read Committed, OK at Repeatable Read and Serializableg-single-read-skew: FAIL at Read Committed, OK at Repeatable Read and Serializableg-single-write-predicate: FAIL at Read Committed, OK at Repeatable Read and Serializableg-single-predicate-read-skew: FAIL at Read Committed, OK at Repeatable Read and Serializableg2-item-write-skew: FAIL at Read Committed and Repeatable Read, OK at Serializableg2-predicate-read-write-skew: FAIL at Read Committed and Repeatable Read, OK at Serializableg2-predicate-read-fekete-write-skew: FAIL at Read Committed and Repeatable Read, OK at Serializable
MySQL on current builds shows the same or fewer anomalies than earlier results from a decade ago, but its Repeatable Read level still allows behaviors that academic literature says that level should generally prevent:
g0-write-cycles-dirty-writes: OK at every levelg1a,g1b,g1c: FAIL at Read Uncommitted, OK from Read Committed upwardotv: OK at every levelpmp: FAIL at Read Uncommitted and Read Committed, OK at Repeatable Read and Serializablepmp-write-predicates: OK at every levelp4-lost-update: FAIL at Read Uncommitted, Read Committed, and Repeatable Read; OK only at Serializableg-single-read-skew: FAIL at Read Uncommitted and Read Committed, OK at Repeatable Read and Serializableg-single-write-predicate: FAIL at Read Uncommitted, Read Committed, and Repeatable Read; OK at Serializableg-single-predicate-read-skew: FAIL at Read Uncommitted and Read Committed, OK at Repeatable Read and Serializableg2-item-write-skew,g2-predicate-read-write-skew,g2-predicate-read-fekete-write-skew: FAIL at Read Uncommitted, Read Committed, and Repeatable Read; OK at Serializable
MariaDB looks different:
g0-write-cycles-dirty-writes: OK at every levelg1a,g1b,g1c: FAIL at Read Uncommitted, OK from Read Committed upwardotv: OK at every levelpmp: FAIL at Read Uncommitted and Read Committed, OK at Repeatable Read and Serializablepmp-write-predicates: OK at every levelp4-lost-update: FAIL at Read Uncommitted and Read Committed, OK at Repeatable Read and Serializableg-single-read-skew: FAIL at Read Uncommitted and Read Committed, OK at Repeatable Read and Serializableg-single-write-predicate: FAIL at Read Uncommitted and Read Committed, OK at Repeatable Read and Serializableg-single-predicate-read-skew: FAIL at Read Uncommitted and Read Committed, OK at Repeatable Read and Serializableg2-item-write-skew,g2-predicate-read-write-skew,g2-predicate-read-fekete-write-skew: FAIL at Read Uncommitted, Read Committed, and Repeatable Read; OK at Serializable
That means MariaDB’s Repeatable Read blocks every anomaly that theory says it should block, while MySQL’s Repeatable Read still misses P4 Lost Update and g-single-write-predicate. MariaDB also prevents one extra anomaly at Read Committed relative to PostgreSQL, which is fine because isolation levels define a minimum bar, not a maximum one. 🚀
All three engines avoided Dirty Writes in the shoe example, but they did not do it the same way.
With MySQL at Serializable, the conflicting t2 update waits. If you inspect the timestamps from the run, t2’s first UPDATE completes only after t1 commits. In other words, MySQL handles the conflict by holding locks until commit or rollback.
MariaDB behaves differently. Instead of waiting through the conflicting write sequence, it detects the conflict and aborts t2 with:
Error 1020 (HY000): Record has changed since last read in table 'shoes'
PostgreSQL does something similar, aborting the competing transaction with:
could not serialize access due to concurrent update (40001)
These are all valid ways to prohibit the anomaly. The important point is the shared outcome: no Dirty Write is observed. ✅
The more interesting case is Lost Update, because this is where current MySQL and MariaDB materially diverge at Repeatable Read.
The canonical concurrent sequence looks like this:
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 cycle1The group cycle1 marker is a Monastery assertion: at least one of those four statements should fail if the anomaly is prevented.
PostgreSQL at Repeatable Read behaves as expected. One of the concurrent updates fails with a serialization error, so the group assertion passes. No Lost Update.
MySQL 9.7 still allows both updates and both commits to succeed under Repeatable Read. The group assertion reports 0 / 4 errored, which means the Lost Update test fails. That matches the older Hermitage observation from MySQL 5.6. 📌
MariaDB 11.8.6, however, now rejects the second update with:
Error 1020 (HY000): Record has changed since last read in table 'test'
That gives 1 / 4 errored, so the anomaly is prevented and the assertion passes. This is the clearest sign that MariaDB is no longer simply shadowing MySQL behavior.
A few caveats matter if you want to use Monastery yourself.
First, runs are inherently racy. By default, the scheduler waits 300 ms before dispatching the next query. Set that to zero and you will usually lose the interleaving needed to expose concurrency behavior. At the same time, everything cannot run on one thread because some operations may block until another transaction commits or aborts. ⚠️
Second, the assertion system is fairly rough. Graph-based checking is more expressive and more exact. The current assertions may be stricter than necessary in some cases, although they do not appear looser than required because they reproduce the expected Hermitage outcomes.
Third, Monastery is narrower in scope than Hermitage. Hermitage’s instruction-based format has been adapted to non-SQL transactional systems as well. Monastery is currently just a concurrent SQL transaction runner, though it does support plugins for other databases and it does not require assertions if you only want to observe behavior.
Finally, neither Monastery nor Hermitage is adversarial in the way a fault-finding system is. They categorize straightforward scenarios. They help answer, “What does this engine do in this simple interleaving?” They do not prove the absence of deeper bugs under more hostile conditions.
There are two clear takeaways.
One is that MariaDB has moved far enough from MySQL that treating it as a transparent substitute is no longer safe, especially if your application depends on specific concurrency guarantees.
The other is methodological: isolation-level test catalogs are extremely useful, but they age. Database internals change, and names like Repeatable Read do not guarantee identical semantics across engines or across years. If the behavior matters, rerun the scripts. Better yet, automate them. ✅
🔍 TL;DR Summary
🧪 A new tool, Monastery, automates Hermitage-style concurrent transaction tests so modern database behavior can be rechecked more easily.
📌 All three engines tested here avoid Dirty Writes, but they do so differently: MySQL blocks, while MariaDB and PostgreSQL abort the competing transaction.
⚠️ MySQL 9.7 still permits Lost Update under Repeatable Read, matching older behavior.
✅ MariaDB 11.8.6 prevents Lost Update at Repeatable Read, unlike MySQL, and aligns better with the expected anomaly profile for that level.
🚀 MariaDB’s transaction behavior now differs enough from MySQL that “drop-in replacement” is no longer a safe assumption.
🧭 Isolation labels are not enough on their own; rerunning concurrency tests against current versions is worth the effort.
No posts
Comments
Nothing yet. Say the first thing.
Sign in to join the conversation.