Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the submission process of MySQL transactions". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL transaction commit process

When binlog is turned on, the transaction commit process becomes a two-phase commit, where the two-phase commit does not involve a distributed transaction. Of course, mysql calls it an internal xa transaction (Distributed Transactions), along with an external xa transaction.

The so-called two-phase commit here is the prepare phase and the commit phase.

Internal xa transactions mainly exist within mysql to ensure data consistency between binlog and redo log, which is also determined by its architecture (binlog is at the mysql layer, while redo log is at the storage engine layer)

External xa transaction refers to the support of multi-instance distributed transaction, which is the real distributed transaction.

Since it is a xa transaction, it is bound to involve two-phase commit, and for internal xa, there are also two stages of commit.

The following section will interpret in detail the two-phase commit process of the internal xa with the source code, and how to restore the mysql after the mysqld crash in various cases to ensure transaction consistency.

Database version: 5.6.16

Operating system version: CentOS 6.5

Profile 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 different modes is that the frequency of write file call write and disk drop fsync call is different, the consequence is that after mysqld or os crash, lax settings may lose transaction updates.

The dual mode is the most stringent mode, and in this setting, the stand-alone will not lose transaction updates under any circumstances. ]

Test conditions:

Set autocommit=0

DROP TABLE IF EXISTS `user`

CREATE TABLE `user` (

`id` int (20) NOT NULL

`roomt` 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

Prepare phase:

1. Set undo state=TRX_UNDO_PREPARED; / / trx_undo_set_state_at_prepare call

two。 Browse the redo log generated by transaction updates; [the redo log generated by step 1 will also be brushed in]

MYSQL_BIN_LOG::prepare

Ha_prepare_low

{

Engine:

Binlog_prepare

Innobase_xa_prepare

Mysql:

Trx_prepare_for_mysql

{

1.trx_undo_set_state_at_prepare / / sets the undo segment to be marked TRX_UNDO_PREPARED

two。 Set the transaction status to TRX_STATE_PREPARED

3.trx_flush_log_if_needed / / Brush the generated redolog into the disk

}

}

Commit phase:

1. Write the binlog generated by the transaction to the file and brush it to disk

two。 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 corresponding to the transaction and write it to the system tablespace; / / trx_sys_update_mysql_binlog_offset call

MYSQL_BIN_LOG::commit

Ordered_commit

{

1.FLUSH_STAGE

Flush_cache_to_file / / swipe 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 and set undo status

Trx_commit_in_memory (trx, lsn); / / release lock resources, clean up SavePoint list, and clean up rollback segments

}

}

}

In any case (machine power down) mysqld crash or os crash,MySQL can still ensure database consistency. How is the consistency of data achieved? It is the two-stage submission.

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

1.prepare phase, before redo log closes, mysqld crash

In the 2.prepare stage, after the redo log is closed, and before the binlog is closed, mysqld crash

3.commit phase, after the binlog is set down, mysqld crash

For the first case, since the redo is not down, 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 has not been written, and the transaction is in the TRX_STATE_PREPARED state, is this a commit or a rollback?

In the third case, both redo log and binlog have been closed, but the undo status has not been updated, although redo log and binlog are consistent, should the transaction be committed?

We combine the execution logic after the mysqld exception restart and the key source code.

For the third case, we can collect the binlog event of the uncommitted transaction, so we need to commit

In the second case, because the binlog is not written, you need to perform a rollback operation to ensure database consistency.

After an abnormal restart, how to determine whether the transaction should be committed or rolled back

1. Read the binlog log and get the element in event; / / info- > commit_list that was not committed at the time of the crash.

two。 If it exists, the corresponding transaction is committed; otherwise, it needs to be rolled back.

Determine the transaction commit or rollback source code as follows:

Above, we discussed the basic process of two-phase commit and how to restart mysql to ensure the consistency of binlog and data after the server exception crash.

In short, for abnormal xa transactions, if the binlog has been closed, the transaction should be committed; if the binlog has not been offloaded, the transaction should be rolled back.

/ / rollback the process after an abnormal restart

Innobase_rollback_by_xidrollback_by_xidtrx_rollback_resurrected trx_rollback_active row_undo {/ / get undo record from rollback page / / analyze undo record type if (insert) row_undo_ins else row_undo_mod}

/ / submit the process after an exception restart

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 solve the MySQL master-slave replication function. It contains a collection of event logs that cause data changes.

The slave library requests the master database to send binlog and restore data to write to the slave library through log events, so the data source of the slave library is binlog.

In this way, the MySQL master library only needs to ensure that the binlog is consistent with the local data to ensure the consistency of the master and slave database data (ignoring the master inconsistency caused by network transmission for the time being).

This is the end of "the submission process of MySQL transactions". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.