Automating Hermitage to see how transactions differ in MySQL and MariaDB
a day ago
- #SQL Anomalies
- #Database Testing
- #Transaction Isolation
- Transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) are defined by transaction anomalies like Read Skew and Lost Updates, but the SQL standard is ambiguous.
- Dirty Writes occur when a transaction overwrites uncommitted writes from another transaction, which is disallowed by major databases despite SQL allowing it, unlike Dirty Reads which are permitted under Read Uncommitted.
- A thought experiment illustrates Dirty Writes using a bowling shoe analogy, adapted for testing with the open-source tool Monastery on multiple databases and isolation levels.
- Example SQL transactions demonstrate potential Dirty Reads and Dirty Writes, with assertions to check for consistent results like 'Lin, Lin' or 'Carlos, Carlos' after commits.