MySQL Learning summary-demonstrates concurrency issues at different isolation levels

Demonstrate concurrency issues at different isolation levels

1. When the isolation level of a transaction is set to read UNCOMMITTED, dirty reads, non-repeatable reads, and virtual reads are thrown
A window
Set TRANSACTION isolation level READ UNCOMMITTED;
Start transaction;
SELECT * from bank;
----found a account is 1000 yuan, go to window B
SELECT * from bank;

b window
Start transaction;
Update bank set money=money+100 where Name= ' a ';
----do not commit, go to a window query

2. When the isolation level of a transaction is set to read committed, non-repeatable reads and virtual reads are thrown, but dirty reads are avoided
A window
Set TRANSACTION ISOLATION level Read Committed;
Start transaction;
SELECT * from bank;
----found a account is 1000 yuan, go to window B
SELECT * from bank;
----found that the a account more than 100, this time, a read the data submitted by other transactions, continuous read a account read a different result (non-repeatable read)

b window
Start transaction;
Update bank set money=money+100 where Name= ' a ';
Commit
----go to a window query

3. When the isolation level of a transaction is set to repeatable read (MySQL default level), a virtual read is thrown, but dirty reads and non-repeatable reads are avoided
A window
Set TRANSACTION ISOLATION level repeatable read;
Start transaction;
SELECT * from bank;
----Discovery table has 4 records, go to Window B
SELECT * from bank;
----may find that the table has 5 records, this time a read to another transaction inserted data (virtual Read)

b window
Start transaction;
INSERT into bank (Name,money) VALUES (' E ', 1000);
Commit
----go to a window query

4. When the isolation level of a transaction is set to serializable, all problems are avoided
A window
Set TRANSACTION isolation level Serializable;
Start transaction;
SELECT * from bank;

b window
Start transaction;
INSERT into bank (Name,money) VALUES (' E ', 1000);
----found unable to insert, only wait for a window to end the transaction to insert

MySQL Learning summary-demonstrates concurrency issues at different isolation levels