Database isolation level:
Running multiple things at the same time and accessing the same data in the database without adopting the necessary isolation mechanism will lead to various concurrency problems.
Similar to multi-threaded thread safety, five people go to the same toilet at the same time, so locking must be enabled to achieve thread synchronization and solve the problem.
Dirty read: two things: t1 and t2. The uncommitted fields updated by t2 were read by t1. The content read by t1 is temporarily invalid.
For example: A reporter revealed that my future wealth was one trillion yuan last second, but in fact I already have two trillion yuan. renew
Non-repeatable read: For two things t1 and t2, t1 reads a field, t2 is updated, and t1 reads the same field again with different values.
For example: When checking the balance of the network card, there were 1,000 at first and only 500 later. Revise
Phantom reading: For two things t1 and t2, t1 takes out a field from a table. After t2 inserts some data, t1 reads a few more rows.
For example: three elements become five elements. insert
can be designed
#1. Stop MySQL
C:\WINDOWS\system32>net stop mysql
#2. Start MySQL
C:\WINDOWS\system32>net start mysql
#3. Log in to MySQL
C:\WINDOWS\system32>mysql -uroot -pROOT
#4. View the default isolation level
mysql> select @@tx_isolation
#5. Set the isolation level to the lowest: read uncommitted
#6. Repeat step 4 to view the default level
#7. Open the test library
#8. View data in the table
#9. Modify the character set of the name column in the table
set names gbk;//It seems that because there are multiple names, you need to add s, and use name to report an error.
#10. Check the data in the table again
#11. Turn things on
#12. Modify the name of No. 1
At this time, things are started, there is no end statement, and the data is still in the memory and has not been submitted to the hard disk
#13. Open a cmd command line
When -p is used, the password can be hidden without entering the password and pressing Enter.
#14. View the default isolation level
#15. Set the isolation level to the lowest and check
#16. Open the database and start things
#17. View the data of the account table
#18.Set character set
#19. Check the data in the table again
At this time, Zhang Wuji’s name had been changed to John and was not actually submitted. At this time, this situation is named: dirty read
#20. Things rolled back
#21. Check again
This manifestation is called: non-repeatable read (results are different)
Phantom reading is unavoidable
#Submit the thing and end the previous thing
Both must be submitted.
#Design transaction isolation level 2: read committed
set session transaction isolation level read committed;
#2. Turn things on
#3. Then modify the name of 1
#Submit the thing and end the previous thing
#4. Set the level of another thing to level two
#5. Turn things on
#6. Query data
Dirty reads are avoided, non-repeatable reads and phantom reads are not avoided
#7. Verify and submit things
#8. The results of querying twice are different: non-repeatable read appears.
#9. Submit the end transaction
#Design transaction isolation level 3: repeatable read
set session transaction isolation level repeatable read;
#2. View the data in the table
#3. Start something new
#4.Change Zhang Fei to Liu Bei
#5. Start a new thing
#6. View data in the table
Discovery: Zhang Fei did not become Liu Bei, avoiding dirty reading
#7. Submit
#8. Query again
#9. Submit
#10.Open another thing
This is the latest data, and dirty reading and non-repeatable reading are solved at this time. There is no solution to phantom reading
Phantom reading demonstration
#1. Query the isolation level of things
#2. Open a thing
#3. End the previous thing, start a thing, and query the data of the original table
#4. Insert a piece of data
#5. Submit
6. Modify the name and find that three lines are affected. At this time, phantom reading occurs
Thing isolation level 4: serialization, very low performance, use with caution
The highest isolation level solves all concurrency issues
set session transaction isolation level serializable;
#2. Open a transaction and view the data in the table
#3. Open another thing and insert data. No reflection at this time
Like Java’s multi-threading, it is blocked and locked out. Unable to operate on data
Modification completed here
Normal submission
Wait timeout
