When the Binlog option is turned on, the Execute transaction commit command enters the two-phase commit mode. Two-phase submissions are divided into prepare phases and commit two phases. The process is as follows: There are two important parameters involved: Innodb_flush_log_at_trx_commit and Sync_binlog, parameters can be set to different values, you can see the MySQL help manual. I am setting the two-mode (innodb_flush_log_at_trx_commit=1,sync_binlog=1), the different mode difference is that the write file calls the writing and the Fsync call frequency is different, the result is mysqld or After OS crash, the non-strict settings may lose the update of the transaction. The double mode is the most restrictive mode, in which case the single machine will not lose the transaction update under any circumstances. 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; "The redo log generated by step 1 will also be brushed in" Commit phase: 1. Writes the binlog generated by the transaction to the file, brushes it into the disk, 2. Sets 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 corresponding Binlog offset of the transaction and write to the system table space; Trx_sys_update_mysql_binlog_offset call the following part of my abstraction of the source call part, you can use a single-step debugging, in the key function set breakpoints, to learn more about the process. =========== Prepare stage ===========mysql_bin_log::p repare ha_prepare_low {
engine:binlog_prepareinnobase_xa_preparemysql: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 Stage ============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 Binlog sites, set undO Status trx_commit_in_memory (TRX, LSN); Release the lock resource, clean up the Save points list, clean the rollback segment } } } MYSQLD may have problems with crash,os in any situation, and mysqld will also hang if the machine goes down. But even so, MySQL can still guarantee the consistency of the database. Next, I'll combine the above process to analyze how two-phase submissions can guarantee this. Here are a few common scenarios, 1.prepare stage, redo log before landing, mysqld crash2.prepare stage, redo log after the disk, Binlog before the plate, mysqld Crash3.commit stage, binlog after landing, mysqld crash for the first case, since Redo has no disk, there is no doubt that the update of the transaction is not written to disk, the consistency of the database is affected For 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 situation, at this time, redo log and binlog have been on the disk, but the undo state is not updated, this situation should also be submitted, because redo log and binlog have been consistent, of course, this is just my hypothesis, need to pass the source logic to verify. The following shows the execution logic and the key source code after the mysqld exception restart. For the third case, we can collect the Binlog event for uncommitted transactions, so we need to submit it, which is consistent with our hypothesis.In the second case, because Binlog is not written, the database consistency needs to be ensured by performing a rollback operation. After an abnormal restart, how to determine whether the transaction is committed or rolled back 1. Read the Binlog log to get the event that was not committed when the crash occurred; The //info->commit_list contains the element 2. If present, the corresponding transaction is to be committed, otherwise it needs to be rolled back. Determine transaction commit or rollback source code as follows: the basic process of two-phase commit is discussed, and after the server exception crash, How MySQL restarts recovery guarantees 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. Because this piece of source code involved more, I did not read all the source, if there is not the correct place, please correct me. //the rollback process after an abnormal restart Innobase_rollback_by_xid rollback_by_xid
trx_rollback_resurrected trx_rollback_active Row_undo {//Retrieve undo record from rollback page//analysis UN Do record type if (insert) Row_undo_ins else Row_undo_mod}
After an abnormal restart, the submission process Commit_by_xid trx_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
MySQL Transaction submission Process
