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.)
最重要的一点:使用MySQL中的XA实现分布式事务时必须使用serializable隔离级别。
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.
第一阶段:为prepare阶段,TM向RM发出prepare指令,RM进行操作,然后返回成功与否的信息给TM;
第二阶段:为事务提交或者回滚阶段,如果TM收到所有RM的成功消息,则TM向RM发出提交指令;不然则发出回滚指令;
XA transaction support is limited to the InnoDB
storage engine.(只有innodb支持XA分布式事务)
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
.
MySQL中的XA实现分为:外部XA和内部XA;前者是指我们通常意义上的分布式事务实现;后者是指单台MySQL服务器中,Server层作为TM(事务协调者),而服务器中的多个数据库实例作为RM,而进行的一种分布式事务,也就是MySQL跨库事务;也就是一个事务涉及到同一条MySQL服务器中的两个innodb数据库(因为其它引擎不支持XA)。
3. 内部XA的额外功能
XA 将事务的提交分为两个阶段,而这种实现,解决了 binlog 和 redo log的一致性问题,这就是MySQL内部XA的第三种功能。
MySQL为了兼容其它非事物引擎的复制,在server层面引入了 binlog, 它可以记录所有引擎中的修改操作,因而可以对所有的引擎使用复制功能;MySQL在4.x 的时候放弃redo的复制策略而引入binlog的复制(淘宝丁奇)。
2> InnoDB维持了状态为Prepare的事务链表,将这些事务的xid和Binlog中记录的xid做比较,如果在Binlog中存在,则提交,否则回滚事务。
将Binlog Group Commit的过程拆分成了三个阶段:
1> flush stage 将各个线程的binlog从cache写到文件中;
2> sync stage 对binlog做fsync操作(如果需要的话;最重要的就是这一步,对多个线程的binlog合并写入磁盘);
3> commit stage 为各个线程做引擎层的事务commit(这里不用写redo log,在prepare阶段已写)。每个stage同时只有一个线程在操作。(分成三个阶段,每个阶段的任务分配给一个专门的线程,这是典型的并发优化)
淘宝对binlog group commit进行了进一步的优化,其原理如下:
从XA恢复的逻辑我们可以知道,只要保证InnoDB Prepare的redo日志在写Binlog前完成write/sync即可。因此我们对Group Commit的第一个stage的逻辑做了些许修改,大概描述如下:
Step1. InnoDB Prepare,记录当前的LSN到thd中;
Step2. 进入Group Commit的flush stage;Leader搜集队列,同时算出队列中最大的LSN。
Step3. 将InnoDB的redo log write/fsync到指定的LSN (注:这一步就是redo log的组写入。因为小于等于LSN的redo log被一次性写入到ib_logfile[0|1])
Step4. 写Binlog并进行随后的工作(sync Binlog, InnoDB commit , etc)
也就是将 redo log的write/sync延迟到了 binlog group commit的 flush stage 之后,sync binlog之前。
通过延迟写redo log的方式,显式的为redo log做了一次组写入(redo log group write),并减少了(redo log) log_sys->mutex的竞争。
也就是将 binlog group commit 对应的redo log也进行了 group write. 这样binlog 和 redo log都进行了优化。
官方MySQL在5.7.6的代码中引入了淘宝的优化,对应的Release Note如下:
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参数 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 |
|
Default | TRUE |
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默认为true,表示启用XA,虽然它会导致一次额外的磁盘flush(prepare阶段flush redo log). 但是我们必须启用,而不能关闭它。因为关闭会导致binlog写入的顺序和实际的事务提交顺序不一致,会导致崩溃恢复和slave复制时发生数据错误。如果启用了log-bin参数,并且不止一个线程对数据库进行修改,那么就必须启用innodb_support_xa参数。
参考:
1. http://www.csdn.net/article/2015-01-16/2823591 (淘宝丁奇:怎么跳出MySQL的10个大坑)
2. http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_support_xa
http://dev.mysql.com/doc/refman/5.6/en/xa.html
http://dev.mysql.com/doc/refman/5.6/en/xa-restrictions.html