Detailed explanation of Mysql transaction isolation level read commit

View MySQL transaction isolation level

mysql> show variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

You can see that the current transaction isolation level is READ-COMMITTED

Let's take a look at the transaction isolation details under the current isolation level and open two query terminals A and B.

There is an order table below, the initial data is as follows

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 1 |
+----+--------+
1 row in set (0.00 sec)

The first step is to start transactions in both A and B.

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

The second step is to query the number values in the two terminals

 mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 1 |
+----+--------+
1 row in set (0.00 sec)

 mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 1 |
+----+--------+
1 row in set (0.00 sec)

The third step is to change the number in B to 2, but do not commit the transaction

mysql> update `order` set number=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Step 4: Query the value in A

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 1 |
+----+--------+
1 row in set (0.00 sec)

It is found that the value in A has not been modified.

Step 5: Submit transaction B and query the value in A again

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 2 |
+----+--------+
1 row in set (0.00 sec)

It is found that the value in A has changed

Step 6. Commit the transaction in A and query the values of A and B again.

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 2 |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 2 |
+----+--------+
1 row in set (0.00 sec)

It is found that the values in A and B are changed to 2.

Here is a simple schematic diagram

We can see that when the transaction isolation level is Read Committed, after the transaction in B is committed, the result of the B transaction commit can be read even if A is not committed. This solves the dirty read problem.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:

MySQL transaction isolation level details
In-depth understanding of Mysql transaction isolation level and locking mechanism issues
MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control
Mysql transaction isolation level principle example analysis
In-depth understanding of the four isolation levels of MySQL transactions

<<: Analysis of uniapp entry-level nvue climbing pit record

>>: CentOS 8 Installation Guide for Zabbix 4.4

Best Practices for Developing Amap Applications with Vue

Detailed explanation of Mysql transaction isolation level read commit

Best Practices for Developing Amap Applications with Vue

Detailed explanation of Vue component reuse and expansion

Use of js optional chaining operator

Detailed explanation of MySQL Explain

Nginx configuration cross-domain request Access-Control-Allow-Origin * detailed explanation

VMware Workstation virtual machine installation operation method

Parsing the commonly used v-instructions in vue.js

How to use MySQL group by and order by together

Simply master the use of horizontal line annotations and code comments in HTML

Use of MySQL DATE_FORMAT function

Recommend

Some basic instructions of docker

Docker image creation and one-click packaging and deployment of the entire project

Kali Linux Vmware virtual machine installation (illustration and text)

Detailed explanation of Docker+Jenkins+Gitlab+Django application deployment practice

How to set PATH environment variable in Linux system (3 methods)

uniapp dynamic modification of element node style detailed explanation

How to modify the group to which a user belongs in Linux

How to install MySQL Community Server 5.6.39

vue3+ts+EsLint+Prettier standard code implementation

Universal solution for MySQL failure to start under Windows system

Introduction to HTML DOM_PowerNode Java Academy

How to shut down/restart/start nginx

Native js to achieve accordion effect

Docker container monitoring principle and cAdvisor installation and usage instructions

Vue achieves the top effect through v-show