MySQL Phantom read

Source: Internet
Author: User
Tags repetition

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.