MySQL Phantom read

First of all, MySQL Phantom read is not "a transaction within two times the same operation actually got a different result", because it is not possible at the RR isolation level using the Read VIEW/MVCC, the definition of this magic is more suitable for oracle,oracle transaction isolation only two levels, RC and Serializable. And then there are a lot of people who argue that the non-repetition is for a record, and that the Phantom Read is for the record set, is it self-comforting?

Here's a comparison of the images of MySQL's phantom reading:

Users:id PRIMARY Key

1, T1:select * from users where id = 1;

2. T2:insert into ' users ' (' id ', ' name ') VALUES (1, ' big Cat ');

3. T1:insert into ' users ' (' id ', ' name ') VALUES (1, ' big Cat ');

T1: The main transaction, whether there is a record with ID 1 in the table, no insert, this is the normal business logic we expect.

T2: Interferes with the transaction in order to disrupt the normal transaction execution of T1.

In the RR isolation level, 1, 2 will be normal execution, 3 will error primary key conflict, for T1 business is the execution failed, here T1 is a phantom read, because T1 read the data state and can not support his next business, heck.

In the Serializable isolation level, 1 execution is implicitly added gap shared lock, so that 2 will be blocked, 3 will be normal execution, for T1 business is correct, successfully killed the disruption of business T2, for T1 He read the state can be used to support the business.

So MySQL's phantom read is not what read two times the return result set is different, but the transaction in the insert in advance detection of non-existent records, surprised to find that the data already exist, the previous detection read the data is like ghosts.

Here to be flexible to understand the meaning of reading, the first select is read, the second insert is also implicitly read, but is read in the mechanism of MySQL, inserting the data is to read the first key conflict to decide whether to perform the insert.

The non-repetition reading focuses on reading-reading, the Phantom reading is saying read-write, and write to confirm that the reading is Ghost Shadow.

The following example version

SELECT VERSION ();

Example 1, read commit

A	B
SET SESSION TRANSACTION Isolation level READ COMMITTED;
SET autocommit=0;
1. Non-repeatable reading
Begin	Begin
INSERT Test VALUES (+);
SELECT * from Test;	SELECT * from Test;
Commit
	SELECT * from Test;
	COMMIT
b The result of a query in a transaction has changed and cannot be read repeatedly
2. Lock
Begin	Begin
INSERT Test VALUES (2,2);
	SELECT * from Test;
	INSERT Test VALUES (2,2);
	Lock wait timeout exceeded; Try restarting transaction
COMMIT	COMMIT
	Begin
	INSERT Test VALUES (3,3);
	INSERT Test VALUES (+);
	COMMIT
BEGIN	BEGIN
SELECT COUNT (*) from Test WHERE a>2;	SELECT COUNT (*) from Test WHERE a>2;
INSERT Test VALUES (5,5);
SELECT COUNT (*) from Test WHERE a>2;	SELECT COUNT (*) from Test WHERE a>2;
COMMIT
SELECT COUNT (*) from Test WHERE a>2;	SELECT COUNT (*) from Test WHERE a>2;

Example 2: Repeat Read

A	B
SET SESSION TRANSACTION Isolation level repeatable READ;
SET autocommit=0;
1. REPEATABLE READ
Begin	Begin
INSERT Test VALUES (+);
SELECT * from Test;	SELECT * from Test;
Commit
	SELECT * from Test;
	COMMIT
	BEGIN
	SELECT * from Test;
	COMMIT
b the query in one transaction has not changed
2 lock
Begin	Begin
INSERT Test VALUES (2,2);
	SELECT * from Test;
	INSERT Test VALUES (2,2);
	Lock wait timeout exceeded; Try restarting transaction
COMMIT	COMMIT
3 (phantom reading)
BEGIN	BEGIN
INSERT Test VALUES (3,3);
SELECT * from Test;	SELECT * from Test;
COMMIT
	SELECT * from Test;
	INSERT Test VALUES (3,3);
	Duplicate entry ' 3 ' for key ' PRIMARY '
	COMMIT
	BEGIN
	SELECT * from Test;
	COMMIT
Phantom Reading, B clearly found no, insert when prompted the primary key conflict, just query No, appear hallucinations?
	Begin
	INSERT Test VALUES (+);
	COMMIT
4. REPEATABLE READ
BEGIN	BEGIN
SELECT COUNT (*) from Test WHERE a>2;	SELECT COUNT (*) from Test WHERE a>2;
INSERT Test VALUES (5,5);
SELECT COUNT (*) from Test WHERE a>2;	SELECT COUNT (*) from Test WHERE a>2;
COMMIT
BEGIN
SELECT COUNT (*) from Test WHERE a>2;	SELECT COUNT (*) from Test WHERE a>2;
COMMIT	COMMIT

Online a lot of say range ah, count and so on is wrong, not used for phantom reading

MySQL Phantom read