MySQL Transaction Submission Process

Development boss request through Binlog query a modified data, data was detected after asked me, there is no possibility Binlog will not record, answer no, because the data is modified, if the failure of the direct rollback, will not be recorded in the Binlog, a friend with the force of the primitive tell me, failure will also record, Sitting without words, because he SQL Server DBA, with SQL Server thinking about MySQL, ha ha ha, with experiments to shut him up!

The simple test steps are as follows:
Root (yoon) > Flush logs;
Query OK, 0 rows affected (0.01 sec)

Root (None) > Show Binlog events in ' mysql-bin.000041 ';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
|   mysql-bin.000041 | 4 |    Format_desc |         232242 | 120 | Server ver:5.6.26-log, Binlog ver:4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+


Root (yoon) > begin;
Query OK, 0 rows Affected (0.00 sec)


Root (Yoon) > Update yoon Set id=7 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched:1 changed:1 warnings:0


In the absence of a commit, the location offset of the binary log has not changed:
Root (Yoon) > Show Binlog events in ' mysql-bin.000041 ';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
|   mysql-bin.000041 | 4 |    Format_desc |         232242 | 120 | Server ver:5.6.26-log, Binlog ver:4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+




Root (Yoon) > commit;
Query OK, 0 rows affected (1.01 sec)

Check the log offset again after commit, change, and record in Binlog
Root (Yoon) > Show Binlog events in ' mysql-bin.000041 ';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------- +
| Log_name | Pos | Event_type | server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------- +
|   mysql-bin.000041 | 4 |    Format_desc |         232242 | 120 | Server ver:5.6.16-log, Binlog ver:4 |
| mysql-bin.000041 | 120 |    Query |         232242 | 199 | BEGIN |
| mysql-bin.000041 | 199 |    Query |         232242 | 304 | Use ' Yoon '; Update Yoon Set id=7 where Id=1 |
| mysql-bin.000041 | 304 |    Xid |         232242 | 335 | COMMIT/* xid=18 */|
+------------------+-----+-------------+-----------+-------------+--------------------------------------------- +



MySQL Transaction submission process
When Binlog is turned on, the transaction submission process becomes a two-phase commit, where the two-phase commit does not involve a distributed transaction, and of course MySQL calls it an internal XA transaction (distributed transactions), with an external XA transaction.


The so-called two-phase commits are prepare phase and commit phase respectively.


Internal XA transactions exist primarily within MySQL to ensure the consistency of data between Binlog and redo logs, which is also determined by its architecture (Binlog in the MySQL layer, and redo log on the storage Engine layer);


External XA transactions refer to support for multi-instance distributed transactions, which is truly a distributed transaction.


Since it is an XA transaction, it inevitably involves a two-phase commit, and for internal XA, there are two stages of submission.


The following is a detailed explanation of the two-phase commit process of internal XA in conjunction with the source code and, in various cases, how MySQL recovers to ensure transactional consistency after mysqld crash.




Database version: 5.6.16


Operating system version: CentOS 6.5


Configuration file Parameters:
Log-bin=/my/log/mysql-bin


Binlog_format=row


Set autocommit=0


Innodb_support_xa=1


Sync_binlog=1


Innodb_flush_log_at_trx_commit=1


"Innodb_flush_log_at_trx_commit=1,sync_binlog=1


The difference between the different modes is that the write file calls the write and the Fsync calls are different in frequency, the consequence is that the mysqld or OS crash, the non-strict settings may lose the transaction update.


The double mode is the most restrictive mode, in which case the single machine will not lose the transaction update under any circumstances. 】




Test conditions:
Set autocommit=0;


DROP TABLE IF EXISTS ' user ';


CREATE TABLE ' user ' (


' ID ' int (a) is not NULL,


' Account ' varchar is not NULL,


' Name ' varchar (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


Prepare stage:


1. Set UNDO state=trx_undo_prepared;//trx_undo_set_state_at_prepare call


2. Brush the redo log generated by the transaction update; "Redo log generated by step 1 will also be brushed into the"


Mysql_bin_log::p repare


Ha_prepare_low


{


Engine


Binlog_prepare


Innobase_xa_prepare


Mysql:


Trx_prepare_for_mysql


{


1.trx_undo_set_state_at_prepare//Set the undo segment to be labeled Trx_undo_prepared


2. Set the transaction status to Trx_state_prepared


3.trx_flush_log_if_needed//The resulting redolog is brushed into the disk


}


}




Commit phase:


1. Write the binlog generated by the transaction to the file and swipe it into the disk;


2. Set the state of the UNDO page to Trx_undo_to_free or trx_undo_to_purge; Trx_undo_set_state_at_finish Call


3. Record the Binlog offset of the transaction and write to the system table space; Trx_sys_update_mysql_binlog_offset Call

Mysql_bin_log::commit


Ordered_commit


{


1.flush_stage


Flush_cache_to_file//Brush Binlog


2.sync_stage


Sync_binlog_file//call Fsync () to sync the file to disk.


3.commit_stage


Ha_commit_low


{


Binlog_commit


Innobase_commit


Trx_commit (TRX)


{


Trx_write_serialisation_history (TRX, MTR); Update the Binlog site, set the undo state


Trx_commit_in_memory (TRX, LSN); Release the lock resource, clean up the savepoint list, clean up the rollback segment


}


}


}

In any case (the machine is out of power) mysqld crash or OS Crash,mysql can still guarantee the consistency of the database. How is data consistency achieved? It is the two phase commit.


We combine several scenarios to analyze how the next two-phase commit is done:


1.prepare stage, redo log before landing, Mysqld crash


2.prepare stage, redo log after landing, Binlog before the plate, mysqld crash


3.commit stage, binlog after landing, mysqld crash


In the first case, since Redo has no disk, there is no doubt that the update of the transaction is not written to disk, and the consistency of the database is affected;


In the second case, when the redo log write is complete, but the Binlog is not written, the transaction is in Trx_state_prepared state, is this a commit or rollback?


For the third case, at this point, redo log and binlog have been dropped, but the undo state is not updated, although redo log and binlog are consistent, should the transaction be committed?





We combine the execution logic of the MYSQLD exception reboot with the key source code.


For the third case, we can collect the Binlog event for uncommitted transaction, so we need to submit it.


In the second case, because Binlog is not written, the database consistency needs to be ensured by performing a rollback operation.





How to determine whether a transaction is committed or rolled back after an abnormal restart


1. Read the Binlog log to get the event that was not submitted when the crash occurred;//info->commit_list contains this element


2. If present, the corresponding transaction is to be submitted, otherwise it needs to be rolled back.

Determine the transaction commit or rollback source as follows:



The basic process of two-phase commit is discussed above, and after the server exception crash, how MySQL restarts recovery ensures consistency of binlog and data.


In short, for an abnormal XA transaction, the transaction should be committed if the binlog has been dropped, and the transaction should be rolled back if the binlog is not on the disk.


Rollback process after an abnormal restart

innobase_rollback_by_xidrollback_by_xidtrx_rollback_resurrected    trx_rollback_active        row_undo        {// Get the undo record from the rollback page//parse the undo record type if (insert)                row_undo_ins else Row_undo_mod        }

After an abnormal restart, the commit process

Commit_by_xidtrx_commit_for_mysql

Write Binlog interface

Handler.cc:binlog_log_rowsql/binlog.cc:commitmysys/my_sync:my_syncsql/binlog.cc:sync_binlog_filehandler/ha_ Innodb.cc:innobase_xa_prepare

The Binlog log file is a new log file introduced to address the MySQL master-slave replication feature, which contains a collection of event logs that raise data changes.

The request from the library to the main library sends Binlog and the log event restores the data written from the library, so the data source from the library is Binlog.

In this way, the MySQL main library only needs to do binlog with the local data to ensure that the master-slave database data is consistent (ignoring the master-slave inconsistency caused by network transmission).

Reference: http://www.cnblogs.com/exceptioneye/p/5451976.html

MySQL Transaction Submission Process