Inconsistencies in MySQL and TiDB under RR Isolation Level

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: MySQL和TiDB RR隔离级别下的不一致问题

| username: EricSong

[TiDB Usage Environment] Test
[TiDB Version] v6.5.0
[Reproduction Path]
Create the following table in TiDB and insert data

CREATE DATABASE test;
CREATE TABLE test.student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);
INSERT INTO student (name) VALUES ('John');
INSERT INTO student (name) VALUES ('Alice');
INSERT INTO student (name) VALUES ('Bob');
INSERT INTO student (name) VALUES ('Emma');

Then open two sessions and test in the following order

Process1 Process2
use test;
start transaction;
use test;
start transaction;
select name from student s where id=2 for update;
select name from student s where id=2 for update;
update student set name = ‘Eric’ where id = 1;
commit;
select * from student where id = 1;
commit;

[Encountered Problem: Phenomenon and Impact]
The select * from student where id = 1; in Process2 still reads John instead of Eric.
However, when performing the same operation on MySQL, the select * from student where id = 1; in Process2 reads Eric.
This issue is caused by MySQL’s delayed loading.

The begin/start transaction command is not the starting point of a transaction. The transaction actually starts at the first operation on an InnoDB table (the first snapshot read statement) after executing these commands. If you want to start a transaction immediately, you can use the start transaction with consistent snapshot command.

But this will undoubtedly cause different results in certain scenarios between TiDB and MySQL.
I would like to ask if it can be adjusted to be consistent with MySQL, and whether this will cause problems for some code originally adapted to MySQL? My personal understanding is that it is possible, because previously MySQL could synchronize the updated value after locking to the next lock acquirer, but this is not possible in TiDB.

| username: tidb狂热爱好者 | Original post link

Did you set the transaction isolation level?

| username: EricSong | Original post link

The isolation level is the default RR. Both MySQL and TiDB have an isolation level of RR, but I understand that the RR of these two is not quite the same. TiDB is based on the start of the transaction, while MySQL is based on the first InnoDB read statement.

| username: zhanggame1 | Original post link

Changing TiDB to RC level, I think the RR level is not very useful.

| username: EricSong | Original post link

Will changing to RC level cause some concurrency issues? I understand that the difference between RC and RR is quite significant.

| username: zhanggame1 | Original post link

No, according to my communication with TiDB vendor engineers, it generally runs in RC mode.

Also, I don’t really understand the significance of RR.

| username: 胡杨树旁 | Original post link

According to the official documentation’s description of the RR isolation level, TiDB’s display is correct. I understand that it should obtain the TSO at the beginning of the transaction and use the data corresponding to the TSO as the standard.

| username: 胡杨树旁 | Original post link

In a MySQL environment, can it be understood as the difference between current read and snapshot read under the RR level? If it is a current read, it should be consistent with TiDB. The current read should be reading the latest data. I wonder if my understanding is correct.

| username: 小于同学 | Original post link

Have you set the transaction isolation level?

| username: dba远航 | Original post link

The isolation level is RR, so this happens because you are reading within an unfinished transaction.

| username: residentevil | Original post link

The RR isolation level is the default in MySQL. Based on over a decade of database experience, this isolation level can have many issues, especially when encountering INDEX GAP LOCK problems (in scenarios where data is updated through auxiliary indexes). Therefore, it is recommended to use the RC isolation level instead.

| username: zhanggame1 | Original post link

The significance of the new version of MySQL’s RR is also unclear.

| username: residentevil | Original post link

Some business operations indeed require repeatable read, so it depends on the business. If the business layer can accommodate it, choosing RC is the best option. You can look at commercial databases like ORACLE and DB2 to understand this.

| username: forever | Original post link

For 90% of business scenarios, RC is sufficient. I haven’t heard of any business not using Oracle just because Oracle only supports RC.

| username: residentevil | Original post link

Very reasonable.

| username: TIDB-Learner | Original post link

Good analysis.

| username: tidb菜鸟一只 | Original post link

The difference between TiDB and MySQL RR: TiDB 事务隔离级别 | PingCAP 文档中心

| username: 小龙虾爱大龙虾 | Original post link

In the early days, due to the issue with MySQL’s binlog format, if it operated under the RC isolation level, MySQL could cause master-slave data inconsistency. There was no problem under the RR mode, so RR was set as the default.

| username: zhanggame1 | Original post link

This is an old issue and has already been resolved.

| username: 小龙虾爱大龙虾 | Original post link

It is like this, the default is RR and it hasn’t been changed.