MySQL As a relational database , It has been widely used in many projects on the Internet . Today we'll talk about the commit process of a transaction .

MySQL Architecture

because mysql Plug in storage architecture , Cause to open binlog after , The essence of transaction submission is two-stage submission , Submit in two phases , To ensure the consistency between the storage engine and the binary log .

This article only discusses binlog Submission process without clocking in , We will discuss it later binlog The submission logic after the option .

Test environment

OS:WIN7

ENGINE:

bin-log:off

DB:

Testing conditions

set autocommit=0;
-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(20) NOT NULL,
`account` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`) USING BTREE,
KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Test statement

insert into user values(1, 'sanzhang', ' Zhang San ');
commit;

Commonly used DML:Data Manipulation Language Data manipulation language , Operate on table data ,(insert、update、delete ) sentence and  DCL:Data Control Language Database control language

( Create user 、 Delete user 、 to grant authorization 、 Cancel Authorization ) sentence and DDL:Data Definition Language Database definition language , Create objects inside the database 、 Delete 、 The modified operation statement

, They all use MySQL The public interface provided mysql_execute_command, To execute the corresponding SQL sentence . Let's analyze mysql_execute_command Interface execution process :

mysql_execute_command
{
switch (command)
{
case SQLCOM_INSERT:
mysql_insert();
break;
case SQLCOM_UPDATE:
mysql_update();
break;
case SQLCOM_DELETE:
mysql_delete();
break;
......
}
if thd->is_error() // Statement execution error
trans_rollback_stmt(thd);
else
trans_commit_stmt(thd);
}

From the above process , You can see that executing any statement , In the end trans_rollback_stmt perhaps trans_commit_stmt, The two are statement rollback and statement commit .

Statement commit , For non automatic mode , There are two main functions :

1、 Release autoinc lock , This lock is mainly used to deal with the acquisition self increasing sequence of mutual exclusion of multiple transactions . therefore , Whether the last execution is a statement commit or a statement rollback , This resource needs to be released immediately .

2、 Identify the position of the statement in the transaction , Facilitate statement level rollback . perform commit after , Can enter the commit technological process .

Now let's look at the specific transaction submission process :

mysql_execute_command
trans_commit_stmt
ha_commit_trans(thd, FALSE);
{
TC_LOG_DUMMY:ha_commit_low
ha_commit_low()
innobase_commit
{
// obtain innodb The transaction structure corresponding to layer
trx = check_trx_exists(thd);
if( Single statement , And not automatically )
{
// Release the occupied by self increasing column autoinc Lock resource
lock_unlock_table_autoinc(trx);
// identification sql The position of the statement in the transaction , Facilitate statement level rollback
trx_mark_sql_stat_end(trx);
}
else Transaction submission
{
innobase_commit_low()
{
trx_commit_for_mysql();
<span style="color: #ff0000;">trx_commit</span>(trx);
}
// Determine the corresponding redo Whether the log is down 【 according to flush_log_at_trx_commit Parameters , determine redo How to drop logs 】
trx_commit_complete_for_mysql(trx);
trx_flush_log_if_needed_low(trx->commit_lsn);
log_write_up_to(lsn);
}
}
}
trx_commit
trx_commit_low
{
trx_write_serialisation_history
{
trx_undo_update_cleanup // for purge threading , Clean up the rollback page
}
trx_commit_in_memory
{
lock_trx_release_locks // Release lock resource
trx_flush_log_if_needed(lsn) // Brush the log
trx_roll_savepoints_free // Release savepoints
}
}

MySQL It's through WAL The way , To ensure the consistency and persistence of database transactions , namely ACID Characteristic of C(consistent) and D(durability).

WAL(Write-Ahead Logging) Is a standard way to implement transaction logging , Specifically :

1、 Before modifying the record , Be sure to write a journal first ;

2、 During transaction commit , Make sure that the log goes down first , Only then can the transaction be submitted .

adopt WAL The way , With transaction characteristics guaranteed , Can improve the performance of the database .

As can be seen from the above process , During submission , Mainly done 4 thing ,

1、 clear undo Segment information , about innodb Storage engine update operation ,undo Segment needs purge, there purge The main function is , Really delete physical records . In execution delete or update In operation , The actual old record is not really deleted , It's just a mark on the record , But after the transaction is committed ,purge Threads are really deleted , Free up physical page space . therefore , During the submission process undo Information to join purge list , for purge threading .

2、 Release lock resource ,mysql Lock mutual exclusion mechanism ensures that different transactions operate a record at different times , After the transaction is executed, all lock resources will be released , And wake up other transactions waiting for their lock resources ;

3、 brush redo journal , We said earlier ,mysql Mechanisms to achieve transaction consistency and persistence . adopt redo Log drop operation , Ensure that even if the modified data page is not updated to disk , As long as the log is complete , Can ensure the integrity and consistency of the database ;

4、 Clean up the list of savepoints , Every statement actually has a savepoint( Save it ), The purpose of a savepoint is to roll back to the state before any statement of a transaction is executed , Since the transaction has been committed , So the list of savepoints can be cleaned up .

About mysql Lock mechanism ,purge principle ,redo journal ,undo And so on , It's actually the core of the database .

MySQL It does not provide transaction support itself , Instead, it opens the storage engine interface , Implemented by a specific storage engine , Specifically, support MySQL The storage engine of transactions is InnoDB.

The general way storage engines implement transactions is based on redo log and undo log.

Simply speaking ,redo log Record the data modified by the transaction , undo log Record the original data before the transaction .

So when a transaction is executed, the actual process is simplified as follows :

  1. First record undo/redo log, Make sure the logs are flushed to disk for persistent storage .
  2. Update data records , Cache operation and asynchronous disk flushing .
  3. Commit transaction , stay redo log writes commit Record .

stay MySQL If the execution of a transaction is interrupted due to a failure , Can pass redo log To redo a transaction or through undo log To roll back , Ensures data consistency .

This is done by the transactional storage engine , but binlog Not in the scope of the transactional storage engine , But by the MySQL Server Recorded .

Then you have to make sure that binlog Data and redo log Consistency between , So it's on binlog After that, the actual transaction execution is one more step , as follows :

  1. First record undo/redo log, Make sure the logs are flushed to disk for persistent storage .
  2. Update data records , Cache operation and asynchronous disk flushing .
  3. Persist the transaction log to binlog.
  4. Commit transaction , stay redo log writes commit Record .

In this case , as long as binlog It didn't work , The whole transaction needs to be rolled back , and binlog After successful writing, even if MySQL Crash All transactions can be resumed and commit completed .

To do that , We need to binlog Associated with transactions , And only guaranteed binlog Consistency with transaction data , To ensure the consistency of master-slave data .

therefore binlog The write process of has to be embedded in the execution process of pure transaction storage engine , And part of the distributed transactions (xa Business ) Complete two-phase submission in the same way .

Reference resources

1、《 High performance MySQL》

MySQL Transaction commit process ( One ) More articles about

  1. MySQL Transaction commit process ( Two )

    In the last article we talked about closing binlog Under the circumstances , The general process of transaction commit . The reason why it's closed binlog, It's because it's on binlog After that, the transaction commit process becomes a two-phase commit , The two-phase commit here does not involve distributed transactions , Of course mysql Take it ...

  2. MySQL Transaction commit process

    One .MySQL Transaction commit process ( One ) MySQL As a relational database , It has been widely used in many projects on the Internet . Today we'll talk about the commit process of a transaction . because mysql Plug in storage architecture , Cause to open binlog after , Transaction commit ...

  3. mysql Transaction commit process

      open binlog After the options , When executing the transaction commit command , It's going to go into two-phase commit mode . The two-stage submission is divided into prepare Phase and commit Two phases . The process is as follows : There are two important parameters involved :innodb_flush_log_ ...

  4. MySQL Transaction commit process ( Reprint )

    http://blog.csdn.net/sofia1217/article/details/53968214 In the last article we talked about closing binlog Under the circumstances , The general process of transaction commit . The reason why it's closed binlo ...

  5. mysql Source code interpretation of the transaction submission process ( One )

    mysql It's a relational database , An important feature of relational database is to support transactions , This is different from no-sql A core feature of the product . Yes, of course ,no-sql The product supports key value query , Can not support sql sentence , It's also a difference . Today's main discussion is ...

  6. 4. Transaction commit process , The basic concept of trading ,Oracle Trading cycle , Save it savepoint, Isolation level of database

     Transaction commit process Business Basic concepts Concept : One or more DML Language composition characteristic : Either they all succeed . Or they all failed Isolation of transactions : Multiple client When operating the database at the same time . To isolate their operations , Otherwise appear : Dirty reading   It can't be reversed ...

  7. MySQL Transaction submission -- Bad and good business habits .

    MySQL Transaction submission -- Bad and good business habits We know " Business " It's one of the most important features that distinguishes a database from a file system .MySQL Of InnoDB The transactions in the engine also fully match ACID( Atomicity Uniformity Isolation, a ...

  8. Distributed transactions _03_2PC frame raincat The source code parsing - Transaction commit process

    One . Preface The first two sections , We have already raincat Of demo Project start up , And a simple analysis of the transaction coordinator and transaction participants start process . This section , Let's see raincat Transaction commit process for . Two . Transaction commit process overview 1. Two stages correspond to ...

  9. mysql Source code interpretation of the transaction submission process ( Two )

    In the last article, I talked about closing binlog Under the circumstances , The general process of transaction commit . The reason why it's closed binlog, It's because it's on binlog After that, the transaction commit process becomes a two-phase commit , The two-phase commit here does not involve distributed transactions , Of course mysql Call it ...

Random recommendation

  1. 1074. Reversing Linked List (25)

    Simulation question , Pay attention to when k == 1 And k == n Time situation #include <stdio.h> #include <string.h> #include <iostream&g ...

  2. 【BZOJ-2599】Race Point divide and conquer

    2599: [IOI2011]Race Time Limit: 70 Sec  Memory Limit: 128 MBSubmit: 2590  Solved: 769[Submit][Status ...

  3. Data import and read read.table Function details , How to read irregular data (fill=T)

    function  read.table  Is the most convenient way to read rectangular lattice data . Because the actual situation may be encountered more , So I preset some functions . These functions call  read.table  But changed some of its default parameters . Be careful ,read.ta ...

  4. JS Date Function operation

    1. Add Format function // common functionsstart Date.prototype.Format = function(fmt) { //author: meizz var o ...

  5. jQuery Medium &amp;&amp; ||

    jQuery1.2.6 clean There is a section of the method that makes people dizzy at first sight . I have no idea what he said . “||, && It can be used in this way ?”,“ What kind of object does this piece finally return to ?” // Trim ...

  6. android Code settings 、 open WLAN wifi Hotspots and connections to hotspots

    In fact, creating hotspots is very simple , Get it first wifi Service for , Then configure the hotspot name . Passwords and so on , And then turn it on through reflection, and OK 了 . Let's take a look at the code implementation for creating hotspots : This is the beginning of WLAN hotspot , And you can specify its hotspot name and password After the branch , ...

  7. iOS Several methods of drawing text on the web

    Text rendering is a common function in developing client programs , It can be divided into control and direct drawing . It's easy to use controls , Add one like UILabel object , Then set the relevant properties . But this method has more limitations . Draw directly ...

  8. windows Build under the system linux

    1. Install the virtual machine first VMware Workstation( Step by step ) 2. Install... On a virtual machine CentOS6.5Linux System ( Step by step )   3. install SecureCRT Terminal emulator , Used to log in Linux service ...

  9. android studio gradle Automatic signature build implementation

    I represent myself. : One . stay android studio Generate signature file in . 1. stay android studio Selected items , Select from the menu bar Build. 2. Click on Generate Signed APK tab . 3 ...

  10. IClone Terrain editor integration T4M Plug in Unity3D Use

    adopt IClone The terrain created by the editor , after T4M After the transformation of plug-ins , It takes up very little resources , Than in Unity I brush the terrain out of it and then pass through it T4M The transformation is much smaller , Perfect for mobile phones . IClone Terrain editor download address : If the ...