My collection of articles: gitee.com/mydb/interv…
There are four isolation levels for transactions in MySQL:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
The default transaction isolation level of MySQL is REPEATABLE READ. The four isolation levels are described below.
1.READ UNCOMMITTED
Read uncommitted, also known as uncommitted read, transactions at this isolation level can see uncommitted data from other transactions. This isolation level is called dirty data because it can read uncommitted data from other transactions, and uncommitted data may be rolled back. This problem is called dirty reads.
2.READ COMMITTED
Read Committed, also known as committed read, a transaction at this isolation level can read data from committed transactions, so it does not have dirty read problems. However, since the results of other transactions can be read during the execution of a transaction, different results may be obtained from the same SQL query at different times. This phenomenon is called non-repeatable reads.
3.REPEATABLE READ
Repeatable reads, MySQL’s default transaction isolation level, ensure that the results of multiple queries for the same transaction are consistent. However, there are also new problems, such as when a transaction of this level is executing, another transaction successfully inserts a certain data, but because its query results are the same every time, it fails to query the data, and fails to insert itself repeatedly (due to unique constraints). This is called a Phantom Read when you can’t insert this information into a transaction when you clearly can’t query it.
4.SERIALIZABLE
Serialization, the highest isolation level of transactions, which forces transactions to be sorted so that they do not collide, thus solving the problem of dirty reads, non-repeatable reads, and phantom reads, but because of the low execution efficiency, there are not many scenarios that are really used.
To summarize, the four transaction isolation levels of MySQL correspond to dirty reads, unrepeatable reads and phantom reads as follows:
| Transaction isolation level | Dirty read | Unrepeatable read | Phantom read |
|---|---|---|---|
| READ UNCOMMITTED | Square root | Square root | Square root |
| READ COMMITTED | x | Square root | Square root |
| REPEATABLE READ | x | x | Square root |
| SERIALIZABLE | x | x | x |
While the above concepts are somewhat abstract, let’s walk through the results of the implementation step by step to understand the differences between these isolation levels.
Front knowledge
1. Common commands related to transactions
# check the MySQL versionselectversion(); # start transactionstarttransaction; # commit transactioncommit; # rollback transactionrollback;
Copy the codeMySQL > query transaction isolation level
MySQL > select transaction isolation level from transaction isolation level
select @@global.tx_isolation,@@tx_isolation;
Copy the codeThe above SQL execution results are as follows:
MySQL > query transaction isolation level
select @@global.transaction_isolation,@@transaction_isolation;
Copy the code4. View the information about the connected client
Each MySQL command line window is a MySQL client, and each client can be individually set to (different) transaction isolation levels, which is the basis for demonstrating MySQL concurrent transactions. The following is the SQL command to query the client connection:
show processlist;
Copy the codeThe SQL execution result is as follows:
5. Query the number of connected clients
You can run the following SQL command to query the number of clients connected to the MySQL server:
show status like 'Threads%';
Copy the codeThe SQL execution result is as follows:
6. Set the transaction isolation level of the client
The transaction isolation level of the current client can be set using the following SQL:
setSession Transaction Isolation Level Transaction isolation level;Copy the codeThe transaction isolation level has four values: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
7. Create a database and test data
Create test database and table information, execute SQL as follows:
Create database
drop database if exists testdb;
create database testdb;
use testdb;
- create a table
create table userinfo(
id int primary key auto_increment,
name varchar(250) not null,
balance decimal(10.2) not null default 0
);
Insert test data
insert into userinfo(id,name,balance) values(1.'Java'.100), (2.'MySQL'.200);
Copy the codeCreate table structure and data as follows:
8. Name conventions
Next, two Windows (two clients) are used to demonstrate the problem of dirty reads, unrepeatable reads, and phantom reads at different isolation levels of the transaction. The client with green characters on a black background on the left will be referred to as “window 1” below, while the client with white characters on a blue background on the right will be referred to as “Window 2” below, as shown below:
Dirty read
A transaction that reads data that another transaction has not committed is called a dirty read. The execution flow of dirty reads is as follows:
| step | Client 1 (Window 1) | Client 2 (Window 2) | instructions |
|---|---|---|---|
| Step 1 | set session transaction isolation level read uncommitted; start transaction; select * from userinfo; | Set transaction isolation level to Read Uncommitted; Start a transaction; Query the user list, where the balance of the Java user is 100 yuan. | |
| Step 2 | start transaction; update userinfo set balance=balance+50 where name=’Java’; | Start a transaction; Add $50 to Java user’s account; | |
| Step 3 | select * from userinfo; | Query the user list, where the Balance of the Java user becomes $150. |
Dirty read shows step 1
To set window 2’s transaction isolation level to read uncommitted, run the following command:
set session transaction isolation level read uncommitted;
Copy the codePS: the transaction isolation level read is not committed. Dirty reads exist.
Then use the command to check the transaction isolation boundary of the current connection window, as shown below:
Dirty read shows step 2
Open a transaction in window 1, add $50 to the Java account, but do not commit the transaction, execute the following SQL:
Dirty read shows step 3
Query the user list again in Window 2. The result is as follows:
Unrepeatable read
Unrepeatable read indicates that a transaction executes the same SQL but reads different data. The execution flow of the non-repeatable read demonstration is as follows:
| step | Client 1 (Window 1) | Client 2 (Window 2) | instructions |
|---|---|---|---|
| Step 1 | set session transaction isolation level read committed; start transaction; select * from userinfo; | Set transaction isolation level to Read Committed; Start a transaction; Query the user list where the balance of the Java user is $100. | |
| Step 2 | start transaction; update userinfo set balance=balance+20 where name=’Java’; commit; | Start a transaction; Add $20 to the Java user’s balance; Commit the transaction. | |
| Step 3 | select * from userinfo; | Query the user list, where the balance of the Java user becomes $120. |
In window 2, two queries in the same transaction get different results. This is called an unrepeatable read. Perform the following steps.
Do not read demo Step 1 again
To set window 2’s transaction isolation level to Read Committed, run the following command:
set session transaction isolation level read committed;
Copy the codePS: Read submitted can solve the problem of dirty read, but the problem of unrepeatable read exists.
Use the command to check the transaction isolation boundary of the current connection window, as shown below:
Do not read demo Step 2 again
Open transaction in window 1, add 20 yuan to Java user, but do not commit transaction, then observe whether there is dirty read problem in window 2, the specific execution result is as follows:
Do not read demo Step 3 again
Switch to Window 2 to query the user list again, and the result is as follows:
The difference between unrepeatable and dirty reads
Dirty reads can read uncommitted data from other transactions, while unrepeatable reads can read committed data from other transactions, but the results of two reads are different.
Phantom read
The phantom name is just like the text. It is like some kind of illusion. In a transaction, the data with primary key X is obviously not found, but the data with primary key X is not inserted, just like some kind of illusion. The execution flow of phantom reading demonstration is as follows:
| step | Client 1 (Window 1) | Client 2 (Window 2) | instructions |
|---|---|---|---|
| Step 1 | set session transaction isolation level repeatable read; start transaction; select * from userinfo where id=3; | Set transaction isolation level to repeatable read; Start a transaction; None example Query the data of user 3. The result is empty. | |
| Step 2 | start transaction; insert into userinfo(id,name,balance) values(3,’Spring’,100); commit; | Start a transaction; Add a user whose id is 3. Commit the transaction. | |
| Step 3 | insert into userinfo(id,name,balance) values(3,’Spring’,100); | Window 2 Fails to add user 3. | |
| Step 4 | select * from userinfo where id=3; | None example Query the data of user 3. The result is empty. |
The detailed execution result is as follows:
Phantom reading demonstration Step 1
Set window 2 as repeatable read, and query user 3. The specific SQL is as follows:
set session transaction isolation level repeatable read;
start transaction;
select * from userinfo where id=3;
Copy the codeThe above SQL execution results are as follows:
Phantom reading demonstration Step 2
Select * from user 1; insert user 3;
start transaction;
insert into userinfo(id,name,balance) values(3.'Spring'.100);
commit;
Copy the codeThe above SQL execution results are as follows:
Phantom reading demonstration Step 3
Insert user 3 into window 2 and execute SQL as follows:
insert into userinfo(id,name,balance) values(3.'Spring'.100);
Copy the codeThe above SQL execution results are as follows:
Phantom reading demonstration Step 4
In window 2, re-execute the query:
select * from userinfo where id=3;
Copy the codeThe above SQL execution results are as follows:
The difference between unrepeatable and phantom reads
The emphasis of the two descriptions is different. The emphasis of the unrepeatable read description is the modification operation, while the emphasis of the phantom read description is the addition and deletion operation.
conclusion
This article demonstrated the four transaction isolation levels of MySQL: read uncommitted (with dirty reads), read committed (with unrepeatable reads), repeatable (with phantom reads), and serialization. Repeatable reads are the default transaction isolation level of MySQL. A dirty read reads data that has not been committed by another transaction. A non-repeatable read reads data that has been committed by another transaction, but has different query results. A phantom read cannot be inserted even though it cannot be queried.
Judge right and wrong from yourself, praise to listen to others, gain and loss in the number.
Public account: Java Chinese Community