[high performance mysql learning notes] transaction

Time:2021-7-21

Thorough understanding of MySQL transactions
Let’s not talk about atomicity, consistency, isolation and persistence. Let’s talk about the transactions under InnoDB storage engine

1、 Isolation level

The default isolation level of MySQL is repeatable-read, which can be viewed and modified by the following command

show variables like '%tx_ Isolation% '// view
set global transaction isolation level read committed; // Global permanent modification
set session transaction isolation level read committed;// Modify current session

Let’s talk about four isolation levels

Read uncommitted is not commonly used
It is seldom used in practical application unless it is really necessary
Suppose that two transactions a and B are opened at the same time
Insert, update and delete operations after a begin and before commit
Transaction B can be selected  
If transaction a rolls back, the insert, update and delete made will be rolled back 
The data just selected by transaction B is dirty data, referred to as dirty read
Serializable is not commonly used
The highest level transaction forces transactions to be executed one by one, and only one transaction is allowed to be executed in a table at the same time
The data read by a transaction will be locked upward, causing other non transaction queries not to be executed normally. This level is considered only when it is necessary to ensure data consistency
Read committed supports mvcc
 Suppose that two transactions a and B are opened at the same time
 Insert, update and delete operations after a begin and before commit
 Transaction B can't find it. At this time, transaction B finds the parameters before transaction a is started
 Suppose transaction a commits now and transaction B doesn't commit yet. Here's the problem
 The second time transaction B goes to select, it can select the data submitted by transaction a
 As a result, the results of two same select in the same transaction are inconsistent, which is called non repeatable read
 It can lead to some strange things
Repeatable read MySQL InnoDB default isolation level supports mvcc
InnoDB solves the above non repeatable read problem through mvcc (multi version concurrency control)
After a begin transaction and before commit, a new data line will be generated for the insert, update and delete operations of each line. The version number is the ID of the current transaction and does not affect the original data line
Transaction B: if begin reads a data row with multiple versions (other transactions have insert, update, delete), it will also add its own version number to the data row

epilogue

This article is continuously updated and revised

This work adoptsCC agreementReprint must indicate the author and the link of this article

Recommended Today

Implementation example of go operation etcd

etcdIt is an open-source, distributed key value pair data storage system, which provides shared configuration, service registration and discovery. This paper mainly introduces the installation and use of etcd. Etcdetcd introduction etcdIt is an open source and highly available distributed key value storage system developed with go language, which can be used to configure sharing […]