Yyds dry goods inventory talking about MySQL transaction and mvcc

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';

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 
begin;

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 ：

commit;

Transaction rollback ：

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

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

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 .