Mysql--repeatable-read "Duplicate data" read at isolation level

In MySQL, MVCC is used to implement the Repeatable-read isolation level, because the select operation does not lock the data, and the other reply can modify the data read by the current reply without being blocked, so read and write does not conflict.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read. Snapshot reads, read the visible version of the record (possibly a historical version), without locking. The current read, read is the latest version of the record, and the current read returned records, will be added to the lock, to ensure that other transactions will no longer concurrently modify this record. (Copy from MySQL plus lock processing analysis)

When a query is made in a transaction, MySQL merges the snapshot read with the current read result and returns it to the client, which can cause some strange results.

To generate test data:

Drop Tabletb002;Create Tabletb002 (IDint Primary Key, C2int,Unique Indexuni_c2 (C2));begin;Insert  intotb002 (ID,C2)Select 1,1;Insert  intotb002 (ID,C2)Select 2,2;Insert  intotb002 (ID,C2)Select 4,4;Commit;

Assuming A and B are in reply, both use the Repeatable-read isolation level

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

First, answer A to execute SQL:

begin ; Select *  from tb002;

The returned results are as follows:

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

Then answer B. Execute sql:

begin ; Delete  from where id=2; commit;

Since reply A is not locked, reply B can successfully delete and commit the transaction, no c2=2 records in the current database, and session B commits the transaction release lock.

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

Back to reply a execute SQL:

Insert  into Select 3,2;

Because there are no c2=2 records in the current database, and other reply-in is not locked in this c2=2 scope, the reply a can complete the c2=2 data insertion.

Make a second query on answer a:

Select *  from tb002;

Return results such as:

C2 has a unique index on it, but why does the query result still contain two c2=2 records? Id=2 records belong to the snapshot read data, id=3 the record data of the current read data, MySQL will be the current read and snapshot read data are simply merged back to the client, do not check "Results data meets unique index" requirements.

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

The above test is for a unique index, so what's the difference between a primary key?

Modify the Insert SQL to:

Insert  into Select 2,3;

That is, the ID value deleted by the answer B and the newly inserted ID value of reply A, the last query result will not contain two records of the same ID, for "snapshot read" and "current read" Two result sets there is a "primary key conflict" situation, the result of the final return to the client will "discard" snapshot read "old version" record, Keep the latest version of the record.

There is no such problem with the primary key visible.

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

Summarize:

Under the Repeatable-read isolation level based on the MVCC implementation, the effect of the snapshot read and the current read causes the returned data result set to exceed the expected result set, even if the result set contains duplicate "Unique index keys", but the returned result set does not contain duplicate "primary keys" (PS: Under the premise of a single-table query).

If you include a post-insert query in a transaction, you should consider the impact of the above issues on your business.

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

Mysql--repeatable-read "Duplicate data" read at isolation level