The difference between Phantom Reading and unrepeatable Reading in Mysql

What is the difference between phantom reading and unrepeatable reading in Mysql? This problem may be often seen in our daily study or work. Through this question, I hope you can understand the difference between fantasy reading and unrepeatable reading.

Unrepeatable: under the same conditions, the value of the read data changes when we read it again.

Illusion: under the same conditions, the number of records read out for the first time and the second time is not the same.

Specific analysis:

1. Non-repeatable

Under the same conditions, the value of the read data changes when we read it again.

Example:

In transaction 1, A reads its own salary of 1000, but at this time the operation of transaction 1 has not been completed, and there is a similar read operation later.

Con1 = getConnection (); select salary from employee where employeeName = "A"

In transaction 2, the financial staff changed A's salary to 2000 and committed the transaction.

Con2 = getConnection (); update employee set salary = 2000 where employeeName = "A"; con2.commit ()

In transaction 1, when A reads his salary again, the salary becomes 2000.

Select salary from employee where employeeName = "A"

The result of two reads before and after in a transaction does not result in unrepeatable reads.

2. Illusory reading

Under the same conditions, the number of records read out for the first time and the second time is not the same.

Example:

At present, there are five employees with a salary of 1000.

Transaction 1, read all employees with a salary of 1000, reading a total of 10 records.

Con1 = getConnection (); Select * from employee where salary = 1000

Then another transaction inserts an employee record into the employee table with a salary of 1000

Con2 = getConnection (); Insert into employee (employeeName,salary) values ("B", 1000); con2.commit ()

Transaction 1 again reads all employees with a salary of 1000 and reads a total of six records, which leads to illusion.

/ / con1 select * from employee where salary = 1000

After reading the above, do you have any further understanding of the difference between phantom reading and unrepeatable reading in Mysql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel.