Why MySQL Uses Repeatable Read as Its Default Isolation Level While Oracle Defaults to Read Committed

MySQL and Oracle both implement the four ANSI/ISO SQL isolation levels—Serializable, Repeatable Read, Read Committed, and Read Uncommitted—but they differ in which levels they support and which they set as defaults.

Oracle only supports Read Committed, Serializable, and a Read‑Only mode; because the two higher‑level options are unsuitable as defaults, Oracle chooses Read Committed as its default isolation level.

MySQL supports all four standard levels. It discards Serializable (too restrictive) and Read Uncommitted (allows dirty reads), leaving Repeatable Read (RR) and Read Committed (RC) as viable choices.

The decision between RR and RC is rooted in MySQL’s binlog implementation. Early MySQL versions used only the STATEMENT binlog format, which records the original SQL statements. Using RC or Read Uncommitted with this format can cause data inconsistency during replication.

For example, consider the following table and two concurrent transactions:

CREATE TABLE t1 (a int(11) DEFAULT NULL, b int(11) DEFAULT NULL, KEY a (a)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (10,2),(20,1);
-- Transaction 1: UPDATE t1 SET a=11 WHERE b=2;
-- Transaction 2: UPDATE t1 SET b=2 WHERE b=1;

When these transactions run under Read Committed, the binlog records the statements in the order they are committed. The replica replays them in that order, leading to both rows ending up as (11,2), which diverges from the master’s state.

Repeatable Read solves this problem by adding a GAP lock in addition to row‑level locks, preventing the second transaction from proceeding until the first commits, thus preserving consistency.

Consequently, MySQL sets Repeatable Read as its default isolation level and also forbids using READ‑COMMITTED with the STATEMENT binlog format, emitting an error such as:

ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

In summary, MySQL’s default RR choice ensures safe replication with the historic statement‑based binlog, while Oracle defaults to RC because its supported levels make RC the only practical default.