Hermitage: Testing transaction isolation levels
“Aristotle maintained that women have fewer teeth than men; although he was twice married, it never occurred to him to verify this statement by examining his wives' mouths.”
― Bertrand Russell, The Impact of Science on Society (1952)
Hermitage is an attempt to nail down precisely what different database systems actually mean with their isolation levels. It's a suite of tests that simulates various concurrency issues — some common, some more obscure — and documents how different databases handle those situations.
This project was started by Martin Kleppmann as background research for his book, Designing Data-Intensive Applications. In this repository you'll find a lot of nitty-gritty detail. For a gentle, friendly introduction to the topic, please read the book. There is also a blog post with some background story.
Summary of test results
This repo contains tests for PostgreSQL, MySQL with InnoDB, Oracle, SQL Server, FoundationDB, CockroachDB, YugabyteDB, and Memgraph. If you would like to port the test suite to another database, please don't add it to this repository; instead you can make your own repository and add it to the following list. The test suite has also been ported to:
The cryptic abbreviations (G1c, PMP etc) are different kinds of concurrency anomalies — issues which can occur when multiple clients are executing transactions at the same time, and which can cause application bugs. The precise definitions of these anomalies are given in the literature (see below for details).
| DBMS | So-called isolation level | Actual isolation level | G0 | G1a | G1b | G1c | OTV | PMP | P4 | G-single | G2-item | G2 | |:--------------|:-----------------------------|:-----------------------|:--:|:---:|:---:|:---:|:---:|:---:|:--:|:--------:|:-------:|:----:| | PostgreSQL | "read committed" ★ | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | | | "repeatable read" | snapshot isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | | | "serializable" | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | | | | | | | | | | | | | | | MySQL/InnoDB | "read uncommitted" | read uncommitted | ✓ | — | — | — | — | — | — | — | — | — | | | "read committed" | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | | | "repeatable read" ★ | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | R/O | — | R/O | — | — | | | "serializable" | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | | | | | | | | | | | | | | | Oracle DB | "read committed" ★ | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | | | "serializable" | snapshot isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | — | some | | | | | | | | | | | | | | | | MS SQL Server | "read uncommitted" | read uncommitted | ✓ | — | — | — | — | — | — | — | — | — | | | "read committed" (locking) ★ | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | | | "read committed" (snapshot) | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | | | "repeatable read" | repeatable read | ✓ | ✓ | ✓ | ✓ | ✓ | — | ✓ | some | ✓ | — | | | "snapshot" | snapshot isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | | | "serializable" | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | | | | | | | | | | | | | | | FDB SQL Layer | "serializable" ★ | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | | | | | | | | | | | | | | | CockroachDB | "read committed" | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | | | "repeatable read" | snapshot isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | | | "serializable" ★ | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | | | | | | | | | | | | | | | YugabyteDB | "read committed" ★ | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | | | "repeatable read" | snapshot isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | | | "serializable" | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | | | | | | | | | | | | | | | Memgraph | "snapshot isolation" ★ | snapshot isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | | | "read committed" | read committed | ✓ | ✓ | ✓ | ✓ | ✓ | — | ✓ | — | — | — | | | "read uncommitted" | read uncommitted | ✓ | — | — | — | ✓ | — | ✓ | — | — | — |
