Detailed explanation of Mysql transaction isolation level read commit

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

A

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

B

 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

B

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

A

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.

A

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)

B

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

Recommend

Explanation of the problem of selecting MySQL storage time type

The datetime type is usually used to store time i...

Nginx tp3.2.3 404 problem solution

Recently I changed Apache to nginx. When I moved ...

Detailed explanation of CocosCreator project structure mechanism

Table of contents 1. Project folder structure 1. ...

Provides helpful suggestions for improving website design

<br />Scientifically Design Your Website: 23...

An article to understand the execution process of MySQL query statements

Preface We need to retrieve certain data that mee...

MySQL 5.7 and above version download and installation graphic tutorial

1. Download 1. MySQL official website download ad...

MySQL commonly used SQL and commands from entry to deleting database and running away

Table of contents Start and stop Database related...

Implementation of mysql data type conversion

1. Problem There is a table as shown below, we ne...

Brief analysis of centos 7 mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar

Baidu Cloud Disk: Link: https://pan.baidu.com/s/1...

Detailed explanation of the solution to the Baidu https authentication prompt "Please redirect your http site 301 to the https site"

I recently wanted to convert a website to https a...

Detailed explanation of angular parent-child component communication

Table of contents APIs used Simple Example person...

Interactive experience trends that will become mainstream in 2015-2016

The most important interactive design article in ...

Detailed explanation of Linux text editor Vim

Vim is a powerful full-screen text editor and the...

How to build svn server in linux

1: Install SVN yum install -y subversion 2. Creat...

About the usage and precautions of promise in javascript (recommended)

1. Promise description Promise is a standard buil...