Duplicate data read at the REPEATABLE-READ isolation level of MySQL"

Duplicate data read at the REPEATABLE-READ isolation level of MySQL"

In MySQL, MVCC is used to implement the REPEATABLE-READ isolation level, because the SELECT operation does not lock the data, other calls can modify the data read by the current session without being blocked, so read and write do not conflict.

In MVCC concurrency control, read operations can be divided into two types: snapshot read and current read ). Snapshot reading reads the visible version of the record (which may be a previous version) without locking. The current read reads the latest version of the record, and the record returned by the current read will be locked to ensure that other transactions will not modify this record concurrently.

When a transaction is queried, MySQL merges the snapshot read and current read results and returns them to the client. This merge may lead to some strange results.

Generate Test Data:

drop table tb002;create table tb002(id int primary key,c2 int,unique index uni_c2(c2));begin;insert into tb002(id,c2) select 1,1;insert into tb002(id,c2) select 2,2;insert into tb002(id,c2) select 4,4;commit;

Assuming there is session A and session B, both use the REPEATABLE-READ isolation level

##=================================================== ============================== ##

First, call back A to execute the SQL:

begin;select * from tb002;

The returned results are as follows:

##=================================================== ============================== ##

Then return to B and execute the SQL statement:

begin;delete from tb002 where id=2;commit;

Because session A does not have A lock, session B can successfully complete the deletion and commit the transaction. There is no record C2 = 2 in the current database, and session B commits the transaction to release the lock.

##=================================================== ============================== ##

Return to session A and execute the SQL statement:

insert into tb002(id,c2) select 3,2;

Because there is no record C2 = 2 in the current database, and other replies are not locked in the range C2 = 2, session A can complete data insertion of C2 = 2.

Query again on session:

select * from tb002;

The returned results are as follows:

There is a unique index on C2, but why does the query result still contain two records with C2 = 2? The record ID = 2 belongs to the data read by the snapshot. The record ID = 3 records the data currently read. MySQL simply merges the data read by the current read and snapshot and returns it to the client, it does not check whether "The result data meets the unique index" requirement.

##=================================================== ============================== ##

In the test above, the unique index is used. What is the difference between the primary key and the primary key?

Modify the inserted SQL statement:

insert into tb002(id,c2) select 2,3;

That is, when the ID value deleted by session B is the same as the ID value newly inserted by session A, the final query result does not contain two records with the same ID, if there is a "primary key conflict" between the "snapshot read" and "current read" result sets, the returned client results will "discard" old version "records in" snapshot read, record of the latest version.

The preceding problem does not exist for the primary key.

##=================================================== ============================== ##

Summary:

At the REPEATABLE-READ isolation level based on MVCC, The Impact of snapshot read and current read will cause the returned data result set to exceed the "expected result set, even the returned results contain duplicate "unique index keys", but the returned results do not contain duplicate "primary keys" (PS: Single Table query ).

If the transaction contains information that is inserted first and then queried, consider the impact of the above problem on the business.