mysql.com/”>MySQL has become one of the most widely deployed SQL databases over the last 28 years. It is primarily used for online transaction processing (OLTP) workloads, but is also deployed as a part of OLAP and queuing systems.
MySQL was originally designed as a single-server database, but has since been extended with various multi-node replication schemes. These include several flavors of binlog replication, group replication, NDB cluster, and third-party plugins like Galera Cluster & Percona XtraDB Cluster. In this analysis, we focus on single-server MySQL, but also evaluated clusters with a single writeable primary and read-only secondaries using binlog replication.
We also evaluated MySQL clusters and found that AWS RDS MySQL clusters routinely violate Serializability. However, we focus primarily on the default storage engine, InnoDB, throughout this text, when we refer to “MySQL.”
While MySQL supports multiple storage engines with different safety properties, we look at the safety of the default engine, InnoDB.
Throughout the development of SQL, four increasingly safe degrees of transaction consistency were introduced in 1977 by Gray, Lorie, Putzolu, and Traiger. These levels were then codified in the SQL-92 standard. We consider the nuances of SQL isolation levels in this analysis and assess the safety of MySQL’s behavior against these standards. ANSI SQL Isolation is Bad, Actually
Gray, Lorie, Putzolu, and Traiger introduced these degrees of transaction consistency, which were related to the behavior of increasingly conservative locking regimes. However, it became apparent that non-locking concurrency control was also possible. To address this, ANSI phrased their levels in terms of three possible phenomena that should not occur. As the standard puts it, “the following phenomena are possible”:
P1 (“Dirty Read”)
