MySQL Transaction submission process (i)

As a relational database, MySQL has been widely used in many projects in the Internet. Today we will discuss the process of submitting the transaction.

MySQL Architecture

Due to the MySQL plug-in storage architecture, when Binlog is turned on, the transaction commit is essentially a two-phase commit, with two-phase commit to ensure consistency between the storage engine and the binary log.

This article discusses only the submission process in Binlog, and later discusses the commit logic after opening the Binlog option.

Test environment

Os:win7

ENGINE:

Bin-log:off

Db:

Test conditions

Set autocommit=0;

 for ' user '------------------------------int(a) is not NULL and ' account ' varchar  ) not NULL, ' name ' varchar (null,primary key (' ID '), key ' id ' (' id ') using Btree,key ' name ' (' name ') using B TREE) ENGINE=innodb DEFAULT Charset=utf8;

Test statement

Insert into user values (1, ' Sanzhang ', ' Zhang San ');

Commit

generally used in DML(INSERT, UPDATE, DELETE) statements and DCL(COMMIT, rollback) statements are all common interfaces provided with M Ysql Mysql_execute_command To execute the corresponding SQL statement. Let's analyze the process of Mysql_execute_command interface execution:

mysql_execute_command{Switch(command) { CaseSqlcom_insert:mysql_insert ();  Break;  Casesqlcom_update:mysql_update ();  Break;  CaseSqlcom_delete:mysql_delete ();  Break;   ......   } ifThd->is_error ()//Statement Execution Errortrans_rollback_stmt (THD); Elsetrans_commit_stmt (THD);}

from the above process, you can see the execution of any statement, and the final execution of trans_rollback_stmt or trans_commit_stmt, the two are statement rollback and statement submission.

Statement submission, for non-automatic mode, there are two main functions:

1, Release autoinc lock, this lock is mainly used to deal with multiple transactions mutually exclusive acquisition of the self-increment sequence. Therefore, the resource needs to be released immediately, regardless of whether the statement was last executed or the statement was rolled back.

2, identify the position of the statement in the transaction, convenient statement-level rollback. After a commit is executed, you can enter the commit process.

Now look at the specific transaction submission process:

Mysql_execute_commandtrans_commit_stmtha_commit_trans (THD, FALSE); {Tc_log_dummy:ha_commit_low Ha_commit_low () innobase_commit {//get the transaction structure for the INNODB layerTrx=check_trx_exists (THD); if(single statement, and not autocommit) {//releasing the Autoinc lock resource occupied by the self-increment columnLock_unlock_table_autoinc (TRX); //identifies the location of the SQL statement in a transaction and facilitates statement-level rollbackTrx_mark_sql_stat_end (TRX); }                ElseTransaction Commit {Innobase_commit_low () {Trx_co                            Mmit_for_mysql (); <span style= "color: #ff0000;" >trx_commit</span>(TRX); }//determine if the transaction corresponds to the redo log is "based on the Flush_log_at_trx_commit parameter, determine the Redo log drop-down mode"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            {                ///  For purge thread processing, cleaning up the rollback page             }            trx_commit_in_memory            {                // Release lock resource                  // Brush Log // release savepoints            }        }                  

MySQL is through the WAL way to ensure consistency and durability of database transactions, namely, the acid characteristics of C (consistent) and d (durability).

WAL (Write-ahead Logging) is a standard way to implement transaction logging, specifically :

1, must write the log before the change record;

2, the transaction submission process, it is necessary to ensure that the log before the disk, in order to calculate the transaction submission completed.

In the case of transaction characteristics, the performance of the database can be improved by means of the Wal method.

From the above process can be seen, the submission process, the main do 4 things,

1, clearing the Undo section information , for the InnoDB storage Engine Update operation, the undo section needs to purge, here the purge main function is to really delete physical records. When a delete or update operation is performed, the actual old record is not actually deleted, only a token is marked on the record, but the purge thread is actually deleted after the transaction commits, freeing the physical page space. Therefore, the undo information is added to the purge list for purge threading during the commit process.

2, release the lock resources , MySQL through the lock mutex mechanism to ensure that different transactions do not concurrently operate a record, the transaction will not really release all lock resources, and wake up waiting for its lock resources of other transactions;

3, brush redo log , before we said, MySQL to achieve transactional consistency and persistence mechanism. Through the Redo log operation, ensure that even if the modified data page is not even updated to disk, as long as the log is completed, it can guarantee the integrity and consistency of the database;

4, clean the list of Save points , each statement will actually have a savepoint (savepoint), the savepoint function is to roll back to the state of the transaction before any statement execution, because the transaction has been committed, so the savepoint list can be cleaned.

On the MySQL lock mechanism, purge principle, redo log, undo paragraph and so on, in fact, are the core content of the database.

MySQL itself does not provide transactional support, but instead opens up the storage engine interface, implemented by a specific storage engine, specifically the storage engine that supports MySQL transactions is InnoDB.

The common way that the storage engine implements transactions is based on redo log and undo log.

Simply put, redo log records the transaction's modified data, and the undo log records the original data before the transaction.

So when a transaction executes, the actual process simplification is described as follows:

1. Record First Undo/redo Log , make sure that logs are brushed to disk for persistent storage.
2. Updates the data record, caches the operation, and asynchronously brushes the disk.
3. commit the transaction, in Redo Log write in Commit Records.

If a failure is interrupted during MySQL execution, the transaction can be redo by redo log or rolled back through the undo log to ensure data consistency.

These are all done by the transactional storage engine, but the binlog is not within the scope of the transactional storage engine, but is logged by MySQL Server.

Then it is necessary to ensure consistency between the binlog data and the redo log, so the actual transaction execution after opening the binlog is one more step, as follows:

1. Record First Undo/redo Log , make sure that logs are brushed to disk for persistent storage.
2. Updates the data record, caches the operation, and asynchronously brushes the disk.
3. persisting the transaction log to Binlog .
4. commit the transaction, in Redo Log the commit record is written in.

In this case, as long as the binlog is not successful, the entire transaction needs to be rolled back, and Binlog after the successful writing, even if the MySQL Crash can resume the transaction and complete the submission.

To achieve this, it is necessary to associate Binlog with transactions, and only ensure the consistency of binlog and transactional data to ensure the consistency of master-slave data.

So the Binlog write process has to be embedded into the pure transactional storage engine execution and complete the two-phase commit in the form of an internal distributed transaction (XA transaction) .

Reference

1, "High-performance MySQL"

MySQL Transaction submission process (i)