REPEATABLE READ is InnoDB’s default isolation level. MySQL has the following support for it:

  • A consistent read within a transaction is a snapshot of the first read data read. This means that if non-locked Select statements are executed multiple times within the same transaction, their results are consistent with each other
  • For read, update, or delete statements, locking depends on whether the statement uses a unique index as a query condition or a scope-type lookup condition
  1. If a unique query condition is used, InnoDB locks only the index records it finds and does not use gap
  2. For other queries, InnoDB locks the index scan range. Block other sessions to insert at the gap covered by the query range by using a gap lock or a next-key lock

Definition of illusion

Executing the same query at different times produces different result sets.

Test based on MySQL 5.7 at RR isolation level

Test preparation

Prepare a test sheet and start with no data

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Copy the code

Its isolation level is RR (InnoDB default)

Test 1: Illusory behavior of RR with unlocked Select in the presence of multiple transactions

The sequential Transaction 1 Transaction 2
1 begin begin
2 mysql> select * from test;

The Empty set (0.00 SEC)
3 mysql> insert into test (name,age) value(“t1”,1);

Query OK, 1 row affected (0.00 sec)
4 mysql> select * from test;

The Empty set (0.00 SEC)
5 mysql> commit;

Query OK, 0 rows affected (0.00 sec)
6 mysql> select * from test;

The Empty set (0.00 SEC)
7 mysql> commit;

Query OK, 0 rows affected (0.00 sec)

After transaction 1 commits, transaction 1 cannot read the results of transaction 2. After transaction 1 commits, transaction 1 reads the latest data

mysql> select * from test; + - + - + -- -- -- -- -- -- -- -- -- -- -- + | | id name | age | + - + - + -- -- -- -- -- -- -- -- -- -- -- + | | | t1 | 1 + 1 - + -- -- -- -- -- - + -- -- -- -- -- - + 1 row in the set (0.00) sec)Copy the code

This result is expected and confirms that consistent reads within the same transaction are snapshots of the first read data read. This means that if non-locked Select statements are executed multiple times within the same transaction, their results are consistent with each other

Test 2: Phantom read performance of RR with non-lock Select in the presence of multiple transactions

Before starting the test, insert a few more records into the test table

insert into test(name,age)values("t2",10),("t3",20);
Copy the code

The test sequence is as follows

The sequential Transaction 1 Transaction 2 Transaction 3
1 begin begin begin
2 mysql> select name from test where age>=10 and age<=20\G;

— 1. row —

name: t2

— 2. row —

name: t3

2 rows in set (0.00 sec)
3 mysql> update test set name =”t_update” where age=20;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0
4 mysql> insert into test (name,age) values(‘t4’,15);

A wait was found here		 mysql> insert into test (name,age) values(‘t5’,5);

Query OK, 1 row affected (0.00 sec)
5 mysql> select name from test where age>=10 and age<=20\G;

— 1. row —

name: t2

— 2. row —

name: t_update

2 rows in set (0.00 sec)
6 commit; After transaction 1 executes commit, transaction 2 executes immediately
7 commit; commit;

In the execution of the three transactions, the following phenomena can be seen

  1. Changes in the same transaction, subsequent reads are able to get the latest results
  2. When multiple transactions are executed at the same time, if the first transaction executes a statement involving locking, then the second transaction will not execute at the gap in the index, but will execute successfully at the gap

The effect of point 2 can be achieved because MySQL uses next-key locking to ensure that no magic reading occurs

As it turns out, if transaction 2 inserts an age value of 10 or 21, it will not succeed, that is, both sides of the index gap are locked

But it is worth noting in phenomenon 1 that when a change is made within a transaction, the transaction itself is able to read the changed value

InnoDB reads consistently

InnoDB consistent read means that InnoDB uses multiple versioning to query snapshots at one point in time. In this way, queries can see commits before this point in time, but can’t see changes or uncommitted transactions after this point in time. One exception to this approach is the ability to look up changes made by previous statements within the same transaction. This exception causes an exception: if some rows in the table are updated, SELECT will find the latest version of the updated row, as well as the old version of any row. If other sessions update the same table at the same time, this exception will make data visible in the original transaction that never existed before

Test 3: Adjust the order of transaction 2INSERT execution based on test 2

The sequential Transaction 1 Transaction 2
1 begin begin
2 mysql> select * from test where age=100;

The Empty set (0.00 SEC)
3 mysql> insert into test (name,age) values(‘t6’,100);

Query OK, 1 row affected (0.00 sec)
4 mysql> commit;
5 mysql> select * from test where age=100;

The Empty set (0.00 SEC)
6 mysql> update test set name=”t6_update” where age=100;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0
7 mysql> select name from test where age=100\G;

–1. row —

name: t6_update

1 row in set (0.00 sec)
8 commit;

In this case, the following phenomena can be observed:

  • After transaction 2 commits new data, transaction 1 cannot read the results of transaction 2
  • If a statement is executed in transaction 1 that changes the data committed by transaction 2, it can be successfully executed and the data can be retrieved by subsequent transaction 1

The MySQL document itself explains this as follows:

  • The state of the database snapshot applies only to a Select statement within a transaction, the DML (insert/update/delete/locking Select) is not. If a concurrent transaction inserts or modifs rows and commits them, the DELETE/UPDATE statement will affect the data even at the RR isolation level, even if the newly committed changes are not found in other concurrent transactions

reference

MySQL supports REPEATABLE READ with next-key lock; MySQL supports REPEATABLE READ with next-key lock; Oracle’s reply to InnoDB’s RR level can prevent phantom reading is discussed on Github