stay MySQL Of the many storage engines , Only InnoDB Support transactions , All the transaction isolation levels mentioned here refer to InnoDB Transaction isolation level under .

MySQL It supports concurrent execution of multiple transactions . Otherwise, a transaction processing a request , When dealing with a person's request , Everything else is waiting , No one dares to use it MySQL As a database , Because the user experience is too bad , I guess it's going to hit the keyboard .

Since transactions can operate concurrently , Here are some questions ： When a transaction writes data , Another transaction wants to read this row of data , What to do with ？ A transaction is writing data , Another data should also write this line of data , How to deal with this conflict ？

Since transactions can operate concurrently , Here are some questions ： When a transaction writes data , Another transaction wants to read this row of data , What to do with ？ A transaction is writing data , Another data should also write this line of data , How to deal with this conflict ？

These are some of the problems caused by concurrent transactions . To be specific ： Dirty reading 、 No repeated reading or phantom reading

Dirty reading

Dirty reading is when a transaction is accessing data , And the data has been modified , This modification has not yet been committed to the database , At this time , Another transaction also accesses this data , And then I used this data .

for example ：

Zhang San's salary is 5000, Business A Change his salary to 8000, But the business A Not yet submitted .

meanwhile ,

Business B Reading Zhang San's salary , I read that Zhang San's salary is 8000.

And then ,

Business A Something goes wrong , and Roll back It's a matter of . Zhang San's salary is rolled back to 5000.

Last ,

Business B The read salary of Zhang San is 8000 The data is dirty , Business B Did a dirty reading .

It can't be read repeatedly

Within a transaction , Read the same data multiple times . Before the end of the business , Another transaction also accesses the same data . that , Between two reads in the first transaction , Due to the modification of the second transaction , So the data read by the first transaction twice may be different . This happens that the data read twice in a transaction is different , So it's called unrepeatable reading .

for example ：

In the transaction A in , I read that Zhang San's salary is 5000, Operation not completed , The transaction has not been submitted .

meanwhile ,

Business B Change Zhang San's salary to 8000, And commit the transaction .

And then ,

In the transaction A in , Read Zhang San's salary again , At this time, the salary becomes 8000. The result of reading two times in a transaction does not result in , It leads to non repeatable reading .

Fantasy reading

A phenomenon that occurs when a transaction is not executed independently , For example, the first transaction modifies the data in a table , This modification involves all data rows in the table . meanwhile , The second transaction also modifies the data in this table , This modification is to insert a new row of data into the table . that , In the future, the user who operates on the first transaction will find out whether there are any modified data rows in the table , It's like an illusion .

for example ：

The present salary is 5000 Of the employees 10 people , Business A Read all salaries as 5000 The number of people is 10 people .

here ,

Business B Insert a salary for 5000 The record of .

At this time ,

Business A Read the salary again as 5000 The employees' , Record as 11 people . At this time, unreal reading .

remind  

The point of unrepeatable reading is to modify ： 

The same conditions , The data you read , Read it again and find that the value is different

The point of unreal reading is to add or delete ： 

The same conditions , The first 1 Time and number 2 The number of records read is not the same