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

How to Enable or Disable Linux Services Using chkconfig and systemctl Commands

This is an important (and wonderful) topic for Li...

Linux kernel device driver Linux kernel basic notes summary

1. Linux kernel driver module mechanism Static lo...

js simple and crude publish and subscribe sample code

What is Publish/Subscribe? Let me give you an exa...

Solve the problem of margin merging

1. Merge the margins of sibling elements The effe...

Analysis of the principles and usage of Linux hard links and soft links

In the Linux system, there is a kind of file call...

Detailed explanation of mysql execution plan id is empty (UNION keyword)

Introduction During the work process, slow querie...

Detailed explanation of SELINUX working principle

1. Introduction The main value that SELinux bring...

CSS to achieve horizontal lines on both sides of the middle text

1. The vertical-align property achieves the follo...

Detailed analysis of the blocking problem of js and css

Table of contents DOMContentLoaded and load What ...

Command to remove (delete) symbolic link in Linux

You may sometimes need to create or delete symbol...

Mini Program Recording Function Implementation

Preface In the process of developing a mini progr...

Let's talk about the issue of passing parameters to React onClick

Background In a list like the one below, clicking...

VMware12.0 installation Ubuntu14.04 LTS tutorial

I have installed various images under virtual mac...

Detailed explanation of custom configuration of docker official mysql image

In order to save installation time, I used the of...

MySQL method of generating random numbers, strings, dates, verification codes and UUIDs

Table of contents Generate random numbers Generat...