Automating Hermitage to see how transactions differ in MySQL and MariaDB

This article explores transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and anomalies like Dirty Writes and Dirty Reads as defined by the SQL standard. It highlights ambiguities in the standard, even in the 2023 version, and demonstrates how to test for Dirty Writes using a bowling alley thought experiment adapted into executable SQL scripts. The author introduces Monastery, a new open-source tool, to automate these tests across databases. Results show that PostgreSQL and MySQL do not exhibit Dirty Writes at Repeatable Read isolation. The article emphasizes the practical value of simple tests for characterizing isolation levels despite their inability to prove bug-free behavior, contrasting with more rigorous approaches like Jepsen tests.