Understanding affairs

summary

Business ： A program execution unit that accesses and updates various data items in a database .

The main purpose of introducing transactions into databases ： Transactions transform a database from one consistent state to another . When the database submits work , You can ensure that either all changes have been saved , Or all changes will not be saved .

InnoDB The transactions in the storage engine fully match ACID Characteristics of ：

• Atomicity （atomicity）（ The entire database transaction is an indivisible unit of work ）
• Uniformity （consistency）（ Transactions move the database from one state to the next consistent state ）
• Isolation, （isolation）（ It is required that the objects of each read-write transaction can be separated from the operation objects of other transactions , That is, the transaction is invisible to other transactions before it is committed ）
• persistence （durability）（ Once the transaction is committed , The result is permanent . Even in case of downtime and other faults , Database can also recover data ）

classification

From the perspective of transaction theory , Transactions can be divided into the following types ：

• Flat affairs （ The operation is atomic , Or both , Either roll back ）
• Flat transactions with savepoints （ In addition to supporting flat operations , Allows rollback to an earlier state in the same transaction during transaction execution ）
• Transaction chain （ When committing a transaction , Release unwanted data objects , Implicitly passing the necessary processing context to the next transaction to start ）
• Nested transactions （ Hierarchical framework , A top-level transaction controls transactions at all levels ）
• Distributed transactions （ A flat transaction running in a distributed environment , Therefore, it is necessary to access different nodes in the network according to the location of the data ）

The difference between chain transactions and flat transactions with savepoints ： Flat transactions with savepoints can be rolled back to any correct savepoint , The rollback in chain transaction is limited to the current transaction , That is, it can only be restored to the nearest save point ; Chain transactions are executing COMMIT Then the lock held by the current firm is released , Flat transactions with savepoints do not affect the locks held so far .

Nested transaction considerations ： Child transactions can be committed or rolled back . But its submission does not take effect immediately , Unless the parent transaction has been committed . It means Any child transaction is actually committed after the top-level transaction is committed .

Of course, you can also use savepoint technology to simulate nested transactions , But in lock There are still some differences in the holding of ： Simulate nested transactions through savepoint Technology , No matter how many save points there are , All locked objects can be obtained and accessed ; In nested transactions , Different sub transactions hold different locks on database objects .

Implementation of transactions

The transaction Isolation, from lock Realization , Atomicity 、 Uniformity 、 persistence Through the database redo log and undo log To achieve .

redo

Basic concepts

Redo logs are used to achieve transaction persistence , It consists of two parts ：

1. Redo log buffer in memory （redo log buffer）（ Volatile ）
2. Redo log file （redo log file）（ persistent ）

When the transaction commits , All logs of the transaction must first be written to the redo log file for persistence , Waiting for business COMMIT The operation is complete .

To ensure that every log is written to the redo log file , After each redo log buffer is written to the redo log file ,InnoDB All storage engines need to be called once fsync operation .

log block

Redo log block （log block）： stay InnoDB In the storage engine , Redo log files are saved in blocks , The size of each piece is 512 byte .

If the number of redo logs generated in a page is greater than 512 byte , Then it needs to be divided into multiple redo log blocks for storage . Because the size of redo log block is the same as that of disk sector , Therefore, redo log writing can ensure Atomic level .

The following figure shows the structure of redo log cache ：

It can be seen from the above figure , The log block consists of three parts ：

1. Log block header （log block header）
2. Log contents （log body）
3. Log block tail （log block tailer）

log group

log group Reorganize the log group , There are multiple redo log files ,InnoDB There's actually only one storage engine log group.

log group It's a logical concept , There is no physical file actually stored to represent log group Information .log group Composed of multiple redo logs , Every log group The log file size in is the same .

Redo log format

Different database operations will have corresponding redo log formats . because InnoDB The storage management of the storage engine is page based , Therefore, the redo log format is also page based . Redo logs have a common header format ：

The common header format consists of the following 3 Part of it is made up of ：

• redo_log_type： Redo log type
• space： Table space ID
• page_no： The offset of the page

LSN

LSN yes Log Sequence Number Abbreviation , It represents the log serial number . stay InnoDB In the storage engine ,LSN Occupy 8 byte , And monotonically increasing .LSN The meaning of the expression is ：

• The total number of redo log writes
• checkpoint The location of
• Page version

LSN Indicates the total number of bytes written to the redo log by the transaction ;LSN Not only in the redo log , It also exists on every page .

recovery

InnoDB The storage engine starts regardless of whether the database was shut down normally during the last run , Will try to recover .

undo

Basic concepts

When a transaction needs to be rolled back , It needs to be used undo.

redo Stored in redo log file , And redo Different ,undo Stored in a special segment inside the database , This paragraph is called undo paragraph .

It should be noted that undo yes Logic log , Just logically restore the database to its original state .

undo log Format

stay InnoDB In the storage engine ,undo log It is divided into ：

• insert undo log（ stay insert In operation undo log. because insert Records of operations , Visible only to the transaction itself , Not visible to other things , Therefore, we should undo log You can delete... Directly after the transaction is committed ）
• update undo log（ Yes delete and update The operation produces undo log. The undo log May need to provide MVCC Mechanism , So you can't delete a transaction when it's committed ）

purge

delete and update The operation may not directly delete the original data , so purge For final completion delete and update operation .

group commit

If the transaction is not read-only , Each time a transaction is committed, it needs to be done fsync operation , To ensure that the redo log has been written to disk . In order to improve the disk fsync The efficiency of , The current database provides group commit The function of , Namely a fsync The operation can refresh to ensure that multiple transaction logs are written to the file .

The isolation level of the transaction

SQL The four isolation levels defined by the standard are ：

InnoDB The default transaction isolation level of the storage engine is repeatable-read（ Repeatable ）, Under this isolation level , Use Next-Key Lock The lock algorithm avoids the generation of unreal reading .

Distributed transactions

Distributed transaction refers to allowing multiple independent transaction resources to participate in a global transaction , A global transaction requires that all participating transactions in it either commit , Either roll back . And when using distributed transactions ,InnoDB The transaction isolation level of the storage engine must be set to serializable（ Serialization ）.

Distributed transactions use two-stage commit ：

1. The first stage , All nodes participating in the global transaction begin to prepare , Tell the transaction manager that they are ready to commit .
2. The second stage , The transaction manager tells the resource manager to execute rollback still commit; If any node shows that it cannot be submitted , Then all nodes are told to roll back .

Business habits

Developers need to pay attention to 3 Some business habits ：

1. Don't commit in a loop （ Either explicitly or implicitly ）
2. Do not use auto submit
3. Do not use automatic rollback