Introduction to the
MySQL has four transaction isolation levels: read uncommitted, read committed, repeatable read, and serializable.
Prepare data in advance
mysql> create table city(
-> id int(10) auto_increment,
-> name varchar(30),
-> primary key (id)
-> )engine=innodb charset=utf8mb4;
insert into city(name) values('Wuhan');
mysql> select * from city;
+----+-----------+
| id | name |
+----+-----------+
| 1 |Wuhan city|
+----+-----------+
Copy the codeWhat can happen to transaction concurrency
Dirty Read
A transaction reads data modified by another uncommitted transaction
Session B on A transaction, the name of the id = 1 to wuhan modified into wenzhou, another session at this time A also open A transaction, read the name of id = 1, at this time of the query results for wenzhou, the transaction session B finally rolled back just modify the record, this session A read data doesn’t exist, the phenomenon is dirty reads. (Dirty reads appear only when reading uncommitted isolation levels)
Non-repeatable Read
A transaction can only read data that has been modified by another committed transaction, and the transaction can query for the latest value each time the data has been modified and committed by another transaction. (Unrepeatable read Read Uncommitted and read Committed isolation levels may appear)
Session A starts A transaction and queries the result whose ID is 1. The query result name is Wuhan city. Session B then changes the name (id=1) to Wenzhou (implicit transaction, because autocommit =1, every SQL statement is automatically committed), and then the transaction in session A queries the result (id=1) again, and reads the result name (Wenzhou). Session B then changes the name of the transaction id=1 to Hangzhou, and the transaction id=1 of session A is queried again, and the value of name is Hangzhou, which is not repeatable.
Phantom
One transaction queries records based on certain criteria, and then another transaction inserts records that meet those criteria. When the original transaction queries again based on those criteria, the records inserted by the other transaction can be read as well. (Phantom read uncommitted, read committed, repeatable read isolation levels may appear)
Session A starts A transaction and queries the record whose ID is >0. The record whose name is Wuhan city is displayed. Then session B inserts A data whose name= Wenzhou (implicit transaction, because autoCOMMIT is 1 and every SQL statement is automatically committed), then the transaction of session A is queried again with the query condition (ID >0), then two records (name= Wuhan and Wenzhou records) will appear. This phenomenon is illusory reading.
The isolation level of the transaction
MySQL has four transaction isolation levels: read uncommitted, read committed, repeatable read, and serializable.
The purpose of MySQL’s isolation level is to isolate transactions from each other to ensure transaction consistency. Comparison of isolation levels: Serializable > Repeatable Read > Read Committed > Read Uncommitted comparison of the impact of isolation levels on performance: Serializable > Repeatable read > Read Committed > Read uncommitted The higher the isolation level, the greater the cost of MySQL performance (such as transaction concurrency severity). To balance the two, it is generally recommended to set the isolation level to repeatable read. The default isolation level of MySQL is also repeatable read.
READ UNCOMMITTED
At the read uncommitted isolation level, transaction A can read data that has been modified but not committed by transaction B.
Dirty, unrepeatable, and phantom read problems can occur, and this isolation level is rarely used.
READ COMMITTED
At the read Committed isolation level, transaction B can read the data modified by transaction B only after transaction A has modified it and committed it.
The read Committed isolation level solves the problem of dirty reads, but non-repeatable reads and phantom reads can occur and is rarely used.
REPEATABLE READ
At the repeatable read isolation level, transaction B can read the data modified by transaction B only after transaction A has modified and committed the data, and after transaction B has committed the transaction itself.
The repeatable read isolation level solves the problem of dirty and unrepeatable reads, but phantom reads may occur.
Question: why is the write lock on (write operation), other transactions can also read operation?
Because InnoDB has MVCC (multi-version concurrency control), snapshot reads can be used without blocking.
SERIALIZABLE
All kinds of problems (dirty reads, unrepeatable reads, phantom reads) do not occur and are implemented by locking (read and write locks).
How isolation levels are implemented
Use MySQL’s default isolation level (repeatable read) for this illustration.
When each record is updated, a rollback operation (undo log) is recorded. Multiple versions of the same record can exist in the system. This is called multi-version concurrency control (MVCC) for databases. That is, by rolling back (the rollback operation), you can return to the value of the previous state. If a value is changed from 1 to 2, 3, and 4 in order, the rollback log will have something like the following.
The current value is 4, but when querying this record, transactions started at different times will have different Read-views. As you can see, in views A, B, and C, the value of this record is 1, 2, and 4, respectively. The same record can have multiple versions in the system, which is the database multi-version Concurrency Control (MVCC). For read-view A, to get to 1, the current value must be rolled back by all of the operations in the graph.
At the same time, you can see that even if there is another transaction changing 4 to 5, this transaction will not conflict with the corresponding transaction of read-view A, B, and C. Question: When do ROLLBACK operation logs (undo logs) be deleted? MySQL determines that rollback logs are deleted when no transaction needs them. Question: When is it no longer necessary? When there is no read-view in the system older than this rollback log.
View & Set transaction isolation level
Note: The commands vary with the version of MySQL. This section uses MySQL 5.7.28 as an example.
Viewing the Isolation Level
- View the current session isolation level
select @@tx_isolation;
Copy the code- View the current isolation level of the system
select @@global.tx_isolation;
Copy the codeSetting the Isolation Level
Method 1: Run the set command
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
Copy the codeOne level there are four values: level: {REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE}
Key words: GLOBAL
SET GLOBAL TRANSACTION ISOLATION LEVEL level;
Copy the code- This applies only to sessions that occur after the statement is executed
- The existing session is invalid
Key words: SESSION
SET SESSION TRANSACTION ISOLATION LEVEL level;
Copy the code- Valid for all subsequent transactions of the current session
- This statement can be executed in the middle of an already opened transaction, but does not affect the currently executing transaction
- If executed between transactions, it is valid for subsequent transactions.
No keywords
SET TRANSACTION ISOLATION LEVEL level;
Copy the code- Only valid for the next transaction to be started in the current session
- After the next transaction completes, subsequent transactions revert to the previous isolation level
- This statement cannot be executed in the middle of an already opened transaction, and an error will be reported
Method 2: Run the service startup command
You can change the value of the startup parameter transaction-ISOLATION for example, if we specify — transaction-ISOLATION =READ UNCOMMITTED, The default isolation level of the transaction is changed from REPEATABLE READ to READ UNCOMMITTED.
conclusion
Welcome to wechat public account “Code zonE”, focusing on sharing Java, cloud computing related content, including SpringBoot, SpringCloud, microservices, Docker, Kubernetes, Python and other related technology dry goods, looking forward to meeting you!