1. XA-2PC (two phase commit, two-phase commit)
XA distributed transaction is standardized by X / Open organization submitted (X represents the transaction; A representative of accordant?). Interface: (Resource Manager RM) between: XA specification defines the main (global) Transaction Manager (TM Transaction Manager) and the (local) resource manager. In order to achieve XA distributed transaction, the transaction will be submitted into two stages: that is 2PC (tow phase commit), XA protocol is to be submitted by the transaction in two stages to achieve a distributed transaction.
1.1 prepare stage:
The first stage, the transaction manager to prepare to issue all database servers involved in the "ready to commit" request performs data processing such as logging and modify the database request is received after processing state of the transaction just to "be able to submit" and then the results returned to the transaction manager.
1.2 commit stage:
After receiving the response from the transaction manager into the second stage, if there is any operation a database error occurred, or a transaction manager does not receive a response from the database, then that transaction fails, all the databases retracement in the first stage affairs. Transaction database server can not receive confirmation of the second phase of submitting a request, it will be "to submit" retracement. If the first stage, all databases are submitted successfully, then the transaction manager issued a "sure to submit" request to the server database transaction "may submit a" state to "submit completed" state, and then returns a response to the database server.
2. MySQL realization of XA
Support for XA transactions is available for the InnoDB storage engine The MySQL XA implementation is based on the X / Open CAE document Distributed Transaction Processing:. The XA Specification.
Currently, among the MySQL Connectors, MySQL Connector / J 5.0.0 and higher supports XA directly, by means of a class interface that handles the XA SQL statement interface for you.
XA supports distributed transactions, that is, the ability to permit multiple separate transactional resources to participate in a global transaction. Transactional resources often are RDBMSs but may be other kinds of resources.
A global transaction involves several actions that are transactional in themselves, but that all must either complete successfully as a group, or all be rolled back as a group. In essence, this extends ACID properties "up a level" so that multiple ACID transactions can be executed in concert as components of a global operation that also has ACID properties. (However, for a distributed transaction, you must use the SERIALIZABLE isolation level to achieve ACID properties. It is enough to use REPEATABLE READ for a nondistributed transaction, but not for a distributed transaction.)
The most important point: You must use serializable isolation level when using MySQL in XA for distributed transactions.
The MySQL implementation of XA MySQL enables a MySQL server to act as a Resource Manager that handles XA transactions within a global transaction. A client program that connects to the MySQL server acts as the Transaction Manager.
The process for executing a global transaction uses two-phase commit (2PC). This takes place after the actions performed by the branches of the global transaction have been executed.
In the first phase, all branches are prepared. That is, they are told by the TM to get ready to commit. Typically, this means each RM that manages a branch records the actions for the branch in stable storage. The branches indicate whether they are able to do this, and these results are used for the second phase.
In the second phase, the TM tells the RMs whether to commit or roll back. If all branches indicated when they were prepared that they will be able to commit, all branches are told to commit. If any branch indicated when it was prepared that it will not be able to commit, all branches are told to roll back.
The first stage: the prepare phase, TM issued instructions to prepare RM, RM to operate, and then return to the success of information to TM;
The second stage: the transaction is committed or rolled back stage, if all RM TM receive a success message, the TM to issue instructions to submit RM; otherwise a rollback command is issued;
XA transaction support is limited to the InnoDB storage engine. (Only innodb support XA distributed transactions)
For "external XA" a MySQL server acts as a Resource Manager and client programs act as Transaction Managers. For "Internal XA", storage engines within a MySQL server act as RMs, and the server itself acts as a TM. Internal XA support is limited by the capabilities of individual storage engines. Internal XA is required for handling XA transactions that involve more than one storage engine. The implementation of internal XA requires that a storage engine support two-phase commit at the table handler level, and currently this is true only for InnoDB.
The MySQL XA implementation is divided into: external and internal XA XA; the former refers to a distributed transaction to achieve our usual sense; the latter refers to a single MySQL server, Server layer as TM (transaction coordinator), and server multiple database instances as RM, while a distributed transaction, which is across the MySQL database transaction; that is, a transaction involving the same two innodb MySQL server databases (for other engine does not support XA).
3. The additional features of internal XA
XA transaction will be submitted in two stages, and this realization to solve the consistency problem binlog and redo log, which is the third function of the MySQL internal XA.
MySQL for compatibility with other non-copy things engine, introduced in the server-level binlog, it can record all engine modification operations, which can use the copy function for all engines; MySQL abandon redo replication strategy in time and 4.x introducing binlog replication (Taobao Dinc).
But introduces binlog, cause a problem --binlog and consistency redo log: A transaction must submit written redo log and binlog, then how the two reconciled? Submit a transaction log which is a standard? How to determine the transaction commits? How transaction crash recovery?
MySQL submitted through two phases (internal XA two-phase commit) a good solution to this problem:
The first stage: InnoDB prepare, hold prepare_commit_mutex, and write / sync redo log; the rollback set Prepared state, binlog no action;
Second stage: two-step, 1> write / sync Binlog; 2> InnoDB commit (COMMIT tag after written release prepare_commit_mutex);
Binlog to write or not to submit the transaction as a sign of success, innodb commit mark the success of the transaction was not sign. Because the transaction crash recovery process is as follows:
1> crash recovery, scanning the last Binlog file, extract the xid;
2> InnoDB maintains the state of affairs Prepare list, these transaction xid and Binlog recorded xid comparison, if present in the Binlog, then submit, otherwise roll back the transaction.
In this way, you can let InnoDB and Binlog transaction consistent state. If the crash when writing innodb commit flag, when restored, will re-commit flag is written;
Collapse prepare phase, it will rollback, crash write / sync binlog stage, it will be rolled back. To achieve this transaction is MySQL5.6 submitted before implementation.
4. binlog Group submitted
The above two-phase commit process of the transaction before the 5.6 version of the implementation, there are serious flaws. When sync_binlog = 1, the second stage of the above it is clear in the write / sync binlog will become a bottleneck, but also hold large global lock (prepare_commit_mutex: prepare and commit a shared lock), which will lead to a sharp decline in performance. The solution is MySQL5.6 the binlog group commit.
The 4.1 MySQL5.6 binlog group commit:
The process Binlog Group Commit split into three phases:
1> flush stage will each thread binlog written document from the cache;
2> sync stage for binlog do fsync operation (if necessary, then; this is the most important step towards binlog merge multiple threads write to disk);
3> commit stage engine did commit the transaction layer for each thread (here not write redo log, in the prepare phase has written). Each stage only one thread in operation. (Divided into three stages, each stage of the task assigned to a dedicated thread, which is typical of concurrent optimization)
The advantage of this implementation is that the three stages can execute concurrently, thereby enhancing efficiency. Note prepare phase has not changed, or write / sync redo log.
(Also: 5.7 introduces MTS: multithreading slave replication, also submitted realized through binlog group in binlog group submitted to each group to submit marked a seqno, then you can follow the master as in the slave in accordance seqno of the size of the order, be submitted to the branch.)
The 4.2 MySQL5.7 binlog group commit:
Taobao binlog group commit was further optimized, the principle is as follows:
Logically XA recovery we can know, as long as InnoDB Prepare the redo log before writing Binlog complete write / sync to. We therefore logical first stage Group Commit to do a little modification, probably described as follows:
. Step1 InnoDB Prepare, record the current LSN to thd in;
. Step2 into the flush stage Group Commit of; Leader collection queue, the queue is also calculated maximum LSN.
. Step3 of the InnoDB redo log write / fsync to the specified LSN (Note: This step is to write redo log groups as less than equal to the LSN redo log is a write-once to ib_logfile [0 | 1].)
Step4. Write Binlog and subsequent work (sync Binlog, InnoDB commit, etc)
That is, after the redo log of write / sync delay to flush stage binlog group commit, and before sync binlog.
By delaying write redo log way to make explicit a group of redo log write (redo log group write), and reduce (redo log) log_sys-> mutex of competition.
That is, binlog group commit corresponding redo log also a group write. Thus binlog and redo log are optimized.
Taobao official MySQL introduced in 5.7.6 optimized code, the corresponding Release Note as follows:
When using InnoDB with binary logging enabled, concurrent transactions written in the InnoDB redo log are now grouped together before synchronizing to disk when innodb_flush_log_at_trx_commit is set to 1, which reduces the amount of synchronization operations. This can lead to improved performance.
5. XA parameters innodb_support_xa
Command-Line Format --innodb_support_xa
System Variable Name innodb_support_xa
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values Type boolean
Enables InnoDB support for two-phase commit (2PC) in XA transactions, causing an extra disk flush for transaction preparation. This setting is the default. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you turn it off, transactions can be written to the binary log in a different order from the one in which the live database is committing them. This can produce different data when the binary log is replayed in disaster recovery or on a replication slave. Do not turn it off on a replication master server unless you have an unusual setup where only one thread is able to change data.
For a server that is accepting data changes from only one thread, it is safe and recommended to turn off this option to improve performance forInnoDB tables. For example, you can turn it off on replication slaves where only the replication SQL thread is changing data.
You can also turn off this option if you do not need it for safe binary logging or replication, and you also do not use an external XA transaction manager.
Innodb_support_xa parameter defaults to true, indicating that enable XA, although it causes an extra disk flush (prepare phase flush redo log). But we must be enabled, but can not close it. Due to the closure will result in the order written binlog and actual transaction commit order is inconsistent, can cause data errors that occurred when crash recovery and slave replication. If the log-bin parameters enabled and more than one thread changes to the database, then you must enable innodb_support_xa parameters.