InnoDB storage engine: Transactions

Yanglinshan little wild boar 2022-05-22 12:22:42 阅读数:624


Understanding affairs


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 )


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 .


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 :

 Insert picture description here
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 :

redo_log_typespacepage_noredo log body

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 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 .


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


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 )


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 :

Transaction isolation level Dirty reading It can't be read repeatedly Fantasy reading
read-uncommitted( Read uncommitted ) yes yes yes
read-committed( It can't be read repeatedly ) no yes yes
repeatable-read( Repeatable ) no no yes
serializable( Serialization ) no no no

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
copyright:author[Yanglinshan little wild boar],Please bring the original link to reprint, thank you.