MySQL Bugs: #114918: Phantom-read problem in REPEATABLE-READ mode

Bug #114918	Phantom-read problem in REPEATABLE-READ mode
Submitted:	8 May 2024 3:58	Modified:	9 May 2024 11:57
Reporter:	Ryan Bai	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	CentOS (7.9)
Assigned to:		CPU Architecture:	x86
Tags:	phantom-readm, REPEATABLE-READ

Description:
In REPEATABLE READ mode, if UPDATE has no index, the table is locked by adding X+Gap lock line by line. During this process, if another session modifies the data in it faster by indexing it before it is locked, it will be overwritten.

How to repeat:
1. create a test table
create table t1 (id int auto_increment,
    a varchar(50),
    b varchar(50),
    c varchar(50),
    primary key(id)
)  engine=INNODB  default charset=utf8;

2. insert datas
DELIMITER $$
 
CREATE PROCEDURE InsertRows()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 10000000000 DO
    INSERT INTO t1 (id, a, b, c)
    VALUES (i, CONCAT('Data', i), CONCAT('Data', i), CONCAT('Data', i));
    SET i = i + 1;
  END WHILE;
END$$
 
DELIMITER ;
 
CALL InsertRows();

3. Session 1: update data
update t1 set c='Data000001' where a='Data2999999';

4. Session 3: verify data_locks and data in t1，At this time, the amount of data is much smaller than the number of locks, and we know that not all data is locked
select count(*) from performance_schema.data_locks;
select count(*) from t1;

5. Session 2: update a data which is not locked
update t1 set c='Data000002' where id=2999999;

6. Session 1: after the session commit, select the data
select * from t1 where a='Data2999999';

the result of c is Data000001, not Data000002 which SQL is later execute.

Suggested fix:
use table lock

Hi Mr. Bai,

Thank you for your bug report.

However, we do not understand what you are trying to report.

First of all, if you do not create an index on InnoDB table, the InnoDB storage engine creates an invisible primary key, so that there is an index.

Second, you have not provided the info whether you use auto-committing or not.

If you are not using auto-committing, then what you report is expected behaviour.

This is because InnoDB is MVCC engine and session 1 can not see the changes made by session 2. These are basics of the multi-version-concurrency-control.

Not a bug.

Suppose you have a summary table of loan information that includes:

Status column (it is rare to create an index on the status column, because of poor filtering)
1: borrowing
2: overdue
3: Finished
User primary key column (there is generally an index created to facilitate the quick location of a user's information, return the corresponding result, and the filter is very good)

Now let me assume the scenario

1. Step 1: I use the platform for lending operations, and the status is 1
2. Step 2: Since yesterday was the last repayment date, the status 1 will be changed to 2 (overdue) in batches at 0:00 today. This is a batch processing, and all the status of unpaid money will be changed to overdue
3. Step 3: A user suddenly thinks of the need to pay back the money, slightly later than the above operation to execute, go to the user id (with index). When the money is paid back, the 1 should be changed to the 3.
Logically, there should be a lock at this time, telling me that the background is processing data, and I will operate later. But it turns out that because this data is not locked (Because the state is changed using a full table scan, the data is locked one by one), Some data is too late to lock.

End result: I just finished the payment, the state did not become 3 but became 2 (phantom reading), and because of the overdue, the overdue interest was stacked up, and the money was gone

Hi,

What you describe is exactly how repeatable-read should work. These are not phantom reads. This is how this isolation level is defined by SQL Standard.

Hence, what you need is some of the other 3 (three) available isolation levels.

Besides that, you can use transactional locking statements.

All of this is described in many chapters in our Reference Manual.

It is you who is designing your application, not MySQL Verification Team.