When it comes to business , It's natural to think ： ACID

ACID, Database management system （DBMS） In the process of writing or updating data , For the sake of security （transaction） Is correct and reliable , Four characteristics required ： Atomicity （atomicity, Or indivisibility ）、 Uniformity （consistency）、 Isolation, （isolation, Also called independence ）、 persistence （durability）

So we have to talk about ： ARIES theory  , This describes a series of theories related to how to recover and isolate the system , Those of you who are interested can study it

In short, there are several points ：

Atomicity and persistence

ARIES The paper ：  Here I attach a link to the paper , Interested students can have a look at  ARIES ： 

We all know the concepts of atomicity and persistence , But for how to implement , There are big doubts .

What are the implementation methods ？

Commit Logging

Definition

Only in the log （ Log specifically refers to the file writing method that only performs sequential appending ） All records are safely landed , See the successful submission of the representative transaction “Commit Record” after , The database will modify the real data according to the information on the log , After the modification is completed , Add an entry in the log “End Record” Indicates that the transaction has completed persistence , This transaction implementation method is called “Commit Logging”

How to ensure atomicity and persistence ？

principle

First , Once the log is successfully written CommitRecord, Then the whole business is successful , Even if it crashes when modifying data , After restart, restore the scene according to the log information written to the disk 、 Continue to modify the data , This ensures that persistence .

secondly , If the log is not written successfully, a crash occurs , After the system restarts, you will see that some of them are not CommitRecord Log , Then mark this part of the log as rollback , The whole thing seems to have never happened at all , This ensures that Atomicity .

The problem is ：

All real changes to the data must occur when the transaction is committed 、 The log is written CommitRecord after , Even before the transaction is committed, the disk I/O Have enough free time 、 Even if the amount of data modified by a transaction is very large , Take up a lot of memory buffer , Whatever the reason , It is never allowed to modify the data on the disk before the transaction is committed , This is very detrimental to improving the performance of the database .

Write-Ahead Logging

First , Let me first introduce the points that need to be considered before and after the transaction is submitted

Before the transaction is committed

STEAL： Before submission , allow Write change data

NO-STEAL： Before submission , Don't allow Write data changes

After the transaction is committed ：

FORCE： After submission , Change data meanwhile write in

NO-FORCE： After submission , Change data At any time write in

When you read this , You found out Commit Logging Do you support the situation before and after the transaction is committed ？

result ：Commit Logging Is allowed  NO-FORCE, But... Is not allowed STEAL

therefore , We need to improve Commit Logging, This will naturally mention Write-Ahead Logging, It is allowed ：

NO-FORCE, It is also allowed to STEAL, be based on NO-FORCE + STEAL The combination can achieve the best performance

Compare with Commit Logging, Write-Ahead Logging How to support STEAL What about ？

principle

First look at Write-Ahead Logging How to support NO-FORCE, This heel Commit Logging The principle is a bit similar to , By recording commit Log to achieve , We generally call it redo log （Redo Log）, Previously recorded log used to replay data changes during crash recovery , As long as the log exists , You can use this log , Judge and write the change data at any time .

How to support STEAL Well ？

The key lies in ： Undo Log, We generally call it rollback log , To support ：“ Before submitting , Allow to write change data ”, Then the change data , This is when the transaction is interrupted and rolled back abnormally or crash recovery , Need to be able to erase effectively , Naturally, corresponding log records are required for rollback operations , In case of transaction rollback or crash recovery , according to UndoLog Erase data changes written in advance

Crash recovery

For crash recovery , It is generally divided into three stages ： analysis phase , Redo phase , Rollback phase

analysis phase ：

This stage starts from the last checkpoint （Checkpoint, It can be understood that before this point, all changes that should be persistent have been safely closed ） Start scanning logs , Find out if there are any EndRecord The business of , Make up a collection of transactions to be recovered , Generally include TransactionTable（TT） and DirtyPageTable（DPT）

Redo phase （Redo）：

This stage is based on , The resulting set of transactions to be recovered （DPT） To repeat history （RepeatHistory）, Find out all that contains CommitRecord Log , Write them to disk , Add one after writing EndRecord, Then remove the transaction set to be recovered .

Rollback phase （Undo）：

This stage of processing is analyzed 、 The set of recovery transactions remaining after the redo phase （TT）, At this point, all that remains are transactions that need to be rolled back （ go by the name of Loser）, according to UndoLog Rollback these transactions with the information in .

There is also an implementation method called ：Shadow Paging

Shadow Paging

The implementation method , Students who are interested in it can study it by themselves , I don't know much .

Isolation,

Thesis link ：ARIESIKVL: A Key-Value Locking Method for Concurrency Control of Multiaction Transactions Operating on B-Tree Indexes

Speaking of isolation level , What do you think of the first time ？

Is it right? ： Read uncommitted , Read submitted , Repeatable , Serializable The division of these four isolation levels ,  If you read the paper mentioned above , You'll find out , There are more than four isolation levels . It's just , Other isolation levels are not necessary , It brings problems , It can no longer meet the scope of normal database requirements .

Actually , Speaking of the four isolation levels , Its essential content still needs to go deep into its implementation , You will find that they are inseparable from a word ： lock

What are the corresponding locks ？

• Read the lock

With a reading lock , No more write locks , Other users can add read lock , No write lock

• Write lock

Other users cannot be locked

• Range lock

Data within the scope of other users cannot be read and write locked

MYSQL Implemented in the ： Clearance lock and rear code lock

Next , Let's look at the specific contents of different isolation levels and which locks are applied

Serializable

So parallel transactions are executed sequentially , After one execution, continue to the next , The parallelism is the lowest

among ： The above three locks are applied , Continue until the end of the transaction

Repeatable

Do not use range lock , The read-write lock lasts until the end of the transaction （ Two stage lock protocol ）

Problems at a higher level ： The problem of unreal reading （ Unreal reading only refers to “ New inserted line ”）

MYSQL Will there be the problem of unreal reading ？

The answer is ： Can't

MYSQL How to solve it ？

The reason for this is , The row lock can only lock the line , But the new insert records the action , What you want to update is between records “ The gap ”( Newly inserted data , After the update operation exists ,binlog Of sql There is data inconsistency in the execution sequence ）.

therefore , To solve the problem of unreal reading ,InnoDB We have to introduce a new lock , It's a gap lock GapLock. In the process of scanning line by line , Not only will the row be locked , And the space on both sides of the line , There's also a gap lock

MySQL The implementation of the ：

Read only transactions do not have this problem ;

Read write transactions ( There are both read and write operations in a transaction ) There will be （ Read transactions need to display the specified lock （select ... for update perhaps lock in share mode) The action of （ Including row lock and clearance lock ）, To avoid

meanwhile , The gap lock is only effective at the repeatable read isolation level , There is no conflict between clearance locks , The clearance lock is marked as the open interval , hold next-keylock Record as the front opening and back closing section , Gap lock and row lock are called together next - key lock, Every next - key lock It's the front opening and back closing section . Introduction of clearance lock , May cause the same statement to lock in a larger range , This actually affects the concurrency

Every time you add a lock, it will say that it is added to “ Which index ” Of . because , The lock is added to the index , This is a InnoDB A basic setting of , You need to always remember when analyzing problems .

Lock rule ：

principle 1： The basic unit of locking is next-keylock. I hope you remember ,next-keylock It's the front opening and back closing section .

principle 2： Only objects accessed during the search process will be locked .

Optimize 1： Equivalent query on Index , When you lock a unique index ,next-keylock Degenerate to row lock .

Optimize 2： Equivalent query on Index , When traversing to the right and the last value does not meet the equivalence condition ,next-keylock Degenerate to clearance lock .

One bug： The range query on the unique index will access the first value that does not meet the condition （ Premise ： namely 5.x series < = 5.7.24,8.0 series < = 8.0.13, Later versions have fixed this bug 了 ）

Read submitted

The write lock lasts until the end of the transaction , But read lock query （select for update With the exception of ） Release immediately after

Problems at a higher level ： Unrepeatable read problem

Read uncommitted

Write lock only , Continue until the end of the transaction , But no read lock at all （ But you can read data ）.

Problems at a higher level ： Dirty reading problem

Speaking of this isolation, I'll probably finish the introduction

notes ：mysql One of them is for ： The isolation level is repeatable read and read the submitted lock free optimization scheme

This lock free optimization scheme has ：

• Multi version concurrency control （MVCC principle ）： In view of this “ A transaction reads + Another transaction writes ” The isolation problem , “ unlocked ” It refers specifically to reading without locking
• Optimistic concurrency control

Expand

The matters discussed here , It's all local business

Transaction processing , Conceptually , Can be divided into ： Local transactions , Shared transactions , Global transaction , Distributed transactions , Specific division principles can be simply passed , Is it multi service , Whether it is multiple data sources . Interested students , You can study it yourself , There is not much to be said about .

Here we are , This article ends . Thank you for reading