tidb repeatable read isolation level does not allow phantom read

[mzhang77]

Bug Report

1. Minimal reproduce step (Required)

2. What did you expect to see? (Required)

Per definition:

Repeatable Read: Guarantees that if a transaction reads a row, it will see the same data throughout the transaction, preventing dirty reads and non-repeatable reads but allowing phantom reads.

The test case creates a phantom reads scenario, so the data inserted at t2 should be seen at t3.

3. What did you see instead (Required)

In tidb, at t3 transaction 1 can't see data inserted at t2 by transaction 2. This is incompatible with Repeatable Read definition. So it's also incompatible with mysql

4. What is your TiDB version? (Required)

mysql> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 8.0.11-TiDB-v7.5.3 |
+--------------------+
1 row in set (0.00 sec)

[cfzjywxk]

The transaction snapshot read uses a unified start_ts, and it is by design that t3 cannot see the data written by t2.

Regarding the definition of isolation levels, there are multiple standards. Please refer to the documents:

• Difference Between TiDB and ANSI Repeatable Read
• Take You Through the Isolation Level of TiDB