Yyds dry goods inventory talking about MySQL transaction and mvcc

Code nongshen Shang 2022-01-26 11:21:10 阅读数:191

yyds dry goods inventory talking

Transactions are the smallest unit of work in a database , Its operation is an atomic operation that cannot be divided , It is one of the important means to ensure data consistency . This article will pass on the basic concepts 、 characteristic 、 Isolation level and implementation principle , Let's get familiar with it MySql The transaction . stay MySql in , Common storage engines that support transactions include innoDB,NDB etc. , Another common storage engine MyISAM It doesn't support transactions , This paper mainly aims at innoDB Analyze .

Business foundation

First, let's take a look at the... Of the transaction 4 Big characteristic ACID:

  • Atomicity (Atomicity): All operations in a transaction , Or it's all done , Or not at all , It doesn't end in the middle .
  • Uniformity (Consistent): One side , Before and after transaction start , The integrity of the database is not compromised ; On the other hand , The written data must fully comply with all preset rules .
  • Isolation, (Isolation): Different sessions or threads , Multiple transactions may occur when operating the database . If you operate a table or the same row of data at the same time , There must be concurrent or interfering operations . Isolation requires that transactions be transparent to table or data operations , There is no interference with each other , Ensure consistency in this way .
  • persistence (Durable): After transaction ends , Changes to data are permanent , Even if the system fails, it will not be lost .

that , When will transactions occur in the database ? stay MySql in , There are two ways to commit transactions , Automatic submission and manual submission , Auto submit is enabled by default .

You can use the command to see if Auto submit is turned on :

show variables like 'autocommit';

#yyds Dry inventory # Talking about MySql Affairs and MVCC

When variables autocommit The value of is ON when , On behalf of automatic submission , Change it to OFF Change to manual submission . In manual submission mode , You can use the following two instructions to start a transaction :

-- The first one is 
start transaction;
-- The second kind 

Both instructions show that a transaction is started , Identify the beginning of the transaction , Have the same effect .

There are also two ways to end a transaction , Transaction confirmation commit :


Transaction rollback :


When commit or rollback After statement execution , The transaction will close automatically .

Transaction concurrency

After introducing the basic concept of transaction , Let's take a look at... Through a few examples , What's the problem with transaction concurrency .

1、 Dirty reading

Dirty reads indicate that a transaction is being processed , Read uncommitted data from other transactions . This kind of data is called dirty data , Operations based on dirty data may be incorrect .


As shown in the figure above , In the transaction A Transaction read in B Uncommitted data . So if the transaction B Then a rollback is performed , The transaction A Then all the dirty data .

2、 It can't be read repeatedly

Non repeatable reading means that within a transaction scope , Make the same query multiple times , But got different results . That is, read the same record multiple times , But the values of some columns in the record have been modified .


Unlike dirty reading , Dirty reading refers to reading uncommitted data from other transactions , Non repeatable reading refers to reading the data submitted by other transactions .

3、 Fantasy reading

Unreal reading refers to a phenomenon that occurs when a transaction is not executed independently , Unreal reading mainly refers to reading records within a range multiple times , Including direct query of all record results or aggregate statistics , Inconsistencies in the number of results were found ( Including the increase or decrease of results ).


Same as non repeatable reading , It is also a transaction that reads the committed data of other transactions . But here's the difference , The result set of the query has changed in number .

The three major problems of transaction concurrency in the above database are actually the problem of database read consistency , The database must provide some transaction isolation mechanism to solve .

Transaction isolation level

to glance at SQL92 ANSI/ISO Four isolation levels of transactions defined :

  • Read Uncommitted( Read uncommitted ): The uncommitted data of the transaction is also visible to other transactions , Dirty reading will occur
  • Read Committed( Read submitted ): After a transaction has started , You can only see the changes made by the submitted transactions , There will be non repeatable reading
  • Repeatable Read( Repeatable ): Read the same data multiple times in the same transaction, and the results are consistent , This isolation level is not defined to solve the problem of unreal reading
  • Serializable( Serialization ): Highest isolation level , By forcing the serial execution of a transaction

So what problems can these levels solve :

  • Reading uncommitted level , Can't solve any problem of transaction concurrency
  • Read committed level , Can solve the dirty reading problem
  • At the repeatable level , Can solve the problem of non repeatable reading
  • At the serialization level , Can solve all problems , But the corresponding will reduce the concurrency of database transactions , Reduce performance

Take an intuitive look at... In the form of a table :


It should be noted that ,innoDB The default transaction isolation level is 3 Level repeatable , however innoDB At this level , The problem of unreal reading is avoided in some scenes , Next, let's look at two solutions .


LBCC(Lock Based Concurrency Control), It is called lock based concurrency control . Before reading the data , Lock it , Prevent other transactions from modifying data . It corresponds to 4 The highest level of serialization isolation among the three isolation levels . At this level , Because the granularity of locks is too large , It can lead to performance degradation , Therefore, it is proposed that LBCC A better way to perform MVCC.


MVCC(Multi version Concurrency controll), Called snapshot based ( Many versions ) concurrency control .

MVCC It is a lock free concurrency control used to solve read-write conflicts ,innoDB This mechanism is used internally to achieve consistent non blocking reading , Greatly improve the concurrent read and write rate , The write operation does not affect the read operation , And read a snapshot of the data (snapshot) edition .

Its basic implementation principle is to allocate one-way increasing timestamp to transactions , Generate a consistent data snapshot at the time point of data request , The snapshot version is associated with the transaction timestamp , And use this snapshot to provide a certain level of consistent reading ( Including statement level or transaction level ).

Looking at MVCC Before the implementation of , Need to know MySql in 3 Implicit fields :

  • DB_TRX_ID: Create version number , It's the one who recently modified the transaction ID, Auto increment . A transaction that records the last transaction that inserted or updated a row ID
  • DB_ROLL_PTR: rollback pointer ( Or delete pointer ), coordination undo log, Point to the previous version of this record
  • DB_ROW_ID: Hidden lines ID, If the data table does not have a primary key set , It will generate a clustered index

MVCC Multi version concurrency control of , The main principle of implementation is to rely on this 3 An implicit field implementation , The core idea is :

  1. Only the creation time is less than or equal to the current transaction ID The line of
  2. You can only find a transaction whose deletion time is greater than or equal to the current transaction ID The line of , Or undeleted lines

Based on these two points , We analyze it according to examples , Or to item Table as an example , Not for now price Field . Two user-defined fields and three implicit fields are formatted as follows :


Business 2 When making a query , Business 3 insert data , Post insert transaction 2 Continue to query :


Business 3 in ,beer The created version of is 3, And the business 2 Only the modification of this transaction can be queried , And the modification of transactions submitted before the first query , So version 3 Your data does not meet the conditions .

On top of that , New transaction 4 Delete data , In business again 2 Query in :


Business 4 in , take id by 2 The deleted version number of the data is written as 4, But in business 2 You can still read and delete data with a version number greater than the current version , So you can still read id by 2 The data of .

On top of that , New transaction 5 Do data update operation , In business again 2 Query in


Business 2 You can still only find the modification of this transaction and the modification of the transaction submitted before the first query , And the business 5 Updated in the id by 1 The data of , The creation version is greater than the transaction version 2, Not meeting the conditions , Cannot be found .

It's just for MVCC A simplified demonstration of the two rules used , besides , In many cases MVCC It is used in combination with the lock of the database , For example, the current read will use a lock , And snapshot read uses MVCC. Through the above example , Hope to help you better understand MqSql The working mechanism of the transaction .

Author's brief introduction , official account Manongshen , An official account of sharing love , Interesting 、 thorough 、 direct , Talk to you about technology . Welcome to pay attention to , Get push for the first time .

copyright:author[Code nongshen Shang],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/01/202201261121035908.html