Comprehensive analysis of isolation levels in MySQL

When the database concurrently adds, deletes, and modifies the same batch of data, a series of problems such as dirty writes, dirty reads, non-repeatable reads, and phantom reads may occur. MySQL provides a series of mechanisms to solve transaction concurrency problems, such as transaction isolation, locking mechanism, and MVCC multi-version concurrency control mechanism. Let's explore the transaction isolation mechanism today.

A transaction is a logical processing unit consisting of a group of SQL statements. Let's first look at the ACID characteristics of transactions:

• Atomicity: A transaction is an atomic operation unit that modifies data and is either fully executed or not executed at all. It is described from the execution level.
• Consistency: Data must remain in a consistent state when a transaction starts and completes. It is described from the perspective of execution results.
• Isolation: The database system provides a certain isolation mechanism to ensure that the transaction execution process is invisible to the outside world, runs independently, and is not affected by the outside world.
• Durable: After a transaction is completed, its changes to the data are permanent and can be maintained even if a system failure occurs.
  

Impact of concurrent transactions:

• Dirty write (Lost Update): Multiple transactions select the same row and are unaware of each other's existence, which will overwrite the data operations of previous transactions.
• Dirty Reads: Transaction A reads data that transaction B has not committed. Transaction B rolls back and transaction A commits. The final result does not conform to the consistency principle.
• Non-Repeatable Reads: The same transaction and the same query statement may have inconsistent results when executed multiple times. This may be caused by modifications made by external transactions and does not meet the isolation requirements.
• Phantom Reads: Transaction A reads the new data submitted by transaction B, which does not meet the isolation requirement.
  

Transaction isolation level:

MySQL provides the above four isolation levels. The stricter the isolation, the fewer problems may occur, but the greater the performance cost. The default isolation level is repeatable read. The following uses the client to perform operations for verification.

First create a table and data

CREATE TABLE `account` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `balance` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `account` (`id`, `balance`)
VALUES
  (1, 500),
  (2, 600),
  (3, 200);

Connect to the client and check the isolation level. You can see that it is repeatable read:

MySQL [test]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+

Read uncommitted test:

Both clients AB execute set tx_isolation='read-uncommitted'; setting the isolation level to read uncommitted.

Client A starts a transaction: start transaction; queries data: select * from account;

Client B starts a transaction: start transaction; updates data: update account set balance = balance - 100 where id = 1; the transaction is not committed at this time

Client A queries the data again: select * from account; now the data from the two queries are different.

A reads the data updated by B before B commits the data. If B rolls back at this time, the data on A will be dirty. This situation is a dirty read caused by uncommitted reading. This can be solved using the Read Committed isolation level.

Use the commit command to commit the transaction of client AB.

Read submitted tests:

Both clients A and B execute set tx_isolation='read-committed'; to set the isolation level to read committed.

Client A starts a transaction: start transaction; queries data: select * from account;

Client B starts a transaction: start transaction; updates data: update account set balance = balance - 100 where id = 1; the transaction is not committed at this time

Client A queries the data again: select * from account; Now we can see that the data queried by client A twice is consistent, and no dirty read occurs

At this time, client B commits the transaction: commit;

Client A queries the data again: select * from account; At this time, you can see that the data queried by client A has changed. This is a non-repeatable read.

Repeatable read test:

Both clients AB execute set tx_isolation='repeatable-read'; to set the isolation level to repeatable read.

Client A starts a transaction: start transaction; queries data: select * from account;

Client B starts a transaction: start transaction; updates data: update account set balance = balance - 100 where id = 1; commits the transaction

Client A queries the data again: select * from account; Now we can see that the data queried twice by client A is consistent, and the data read repeatedly is consistent.

Client A executes the update statement: update account set balance = balance - 50 where id = 1;

Client A queries the data again: select * from account; At this time, the data with id=1 is the data after client B updated -50, and the data consistency is not destroyed.

Client B reopens the transaction and inserts a piece of data: insert into account(id,balance) values ​​(4,1000); commit the transaction;

A client query, the result is the same as last time

Client A executes: update account set balance = balance - 100 where id = 4; to update the newly inserted data of client B. The execution is successful. When all data are queried again, the data with id = 4 can be inserted, and phantom read occurs.

# A client execution process: # Set the isolation level repeatability MySQL [test]> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# Start transaction MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
# Query all data MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 | 300 |
| 2 | 600 |
| 3 | 200 |
+----+---------+
3 rows in set (0.00 sec)
# Query again to verify whether the two results are consistent MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 | 300 |
| 2 | 600 |
| 3 | 200 |
+----+---------+
3 rows in set (0.00 sec)
# After client B inserts data, client A cannot query MySQL this time [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 | 150 |
| 2 | 600 |
| 3 | 200 |
+----+---------+
3 rows in set (0.00 sec)
# Client A updates the data inserted by client B and finds that the update is successful MySQL [test]> update account set balance = balance + 1000 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# Query again, the data can be queried, but phantom reading occursMySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 | 400 |
| 2 | 600 |
| 3 | 200 |
| 4 | 2000 |
+----+---------+
4 rows in set (0.00 sec)
# Commit transaction MySQL [test]> commit;
Query OK, 0 rows affected (0.01 sec)

# B client execution process: Set the isolation level to repeatable read MySQL [test]> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# Start transaction MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
# Update data and submit directly to MySQL [test]> update account set balance = balance - 100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MySQL [test]> commit;
Query OK, 0 rows affected (0.01 sec)
# Start transaction again MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
# Insert a piece of data MySQL [test]> insert into account(id,balance) values ​​(4,1000);
Query OK, 1 row affected (0.01 sec)
MySQL [test]> commit;
Query OK, 0 rows affected (0.00 sec)

The last type of serialization: set tx_isolation='serializable'; can be verified by yourself and can solve all the above problems, but it is generally not used. While ensuring consistency, it brings about a significant decrease in performance and extremely low concurrency. The default is repeatable read.

The isolation level can handle transaction concurrency issues to a certain extent. In addition, there are other means, which will be explored again later.

The above is a comprehensive analysis of the isolation levels in MySQL. For more information about MySQL isolation levels, please pay attention to other related articles on 123WORDPRESS.COM!