【Mysql数据库之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.

XA 事务的基础是两阶段提交协议。XA–eXtended Architecture 在事务中意为分布式事务 

XA由协调者(coordinator，一般为transaction manager)和参与者(participants,一般在各个资源上有各自的resource manager)共同完成。在MySQL中，XA事务有两种。

 

 

On the client side, there are no special requirements. The XA interface to a MySQL server consists of SQL statements that begin with the XA keyword. MySQL client programs must be able to send SQL statements and to understand the semantics of the XA statement interface. They do not need be linked against a recent client library. Older client libraries also will work.

 

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. (As with nondistributed transactions, SERIALIZABLE may be preferred if your applications are sensitive to read phenomena. REPEATABLE READ may not be sufficient for distributed transactions.)

 

 

To carry out a global transaction, it is necessary to know which components are involved, and bring each component to a point when it can be committed or rolled back. Depending on what each component reports about its ability to succeed, they must all commit or roll back as an atomic group. That is, either all components must commit, or all components must roll back. To manage a global transaction, it is necessary to take into account that any component or the connecting network might fail.

 

XA一般由两阶段完成，称为two-phase commit(2PC)。 

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.

阶段一为准备阶段，即所有的参与者准备执行事务并锁住需要的资源。参与者ready时，向transaction manager汇报自己已经准备好。 

阶段二为提交阶段。当transaction manager确认所有参与者都ready后，向所有参与者发送commit命令。 

 

In some cases, a global transaction might use one-phase commit (1PC). For example, when a Transaction Manager finds that a global transaction consists of only one transactional resource (that is, a single branch), that resource can be told to prepare and commit at the same time.

 

 

XA事务状态进展过程

1. 使用XA START 启动一个XA事务，并把它置为ACTIVE状态。

2. 对一个ACTIVE XA事务，发布构成事务的SQL语句，然后发布一个XA END 语句，XA END 把事务置为IDLE状态。

3. 对一个IDLE XA 事务， 发布一个XA PREPARE语句或者一个XA COMMIT ... ONE PHASE语句： 前者把事务置为PREPARE状态，此时XA RECOVER 语句的输出包含事务的xid值(XA RECOVER 语句会列出所有处于PREPARE状态的XA事务)； 后者用于预备和提交事务，不会被XA RECOVER列出，因为事务已经终止。

4. 对一个PREPARE XA 事务，可以发布一个XA COMMIT语句来提交和终止事务，或者发布一个XA ROLLBACK 来回滚并终止事务。

 

 

If you have a failed XA Transaction, it will show as "ACTIVE (PREPARED)" with process no 0 and thread id 0:

 

mysql> show engine innodb status\G

....

---TRANSACTION 0 1192549934, ACTIVE (PREPARED) 791 sec, process no 0, OS thread id 0

1 lock struct(s), heap size 368, undo log entries 3

 

To rollback the transaction, first get its xid:

 

mysql> xa recover;

+----------+--------------+--------------+------------------------------------------------------------+

| formatID | gtrid_length | bqual_length | data                                                       |

+----------+--------------+--------------+------------------------------------------------------------+

|   131075 |           30 |           28 | 1-a00640d:c09d:4ac454ef:b284c0a00640d:c09d:4ac454ef:b284c2 |

+----------+--------------+--------------+------------------------------------------------------------+

1 row in set (2.13 sec)

 

The xid is present in this output, but you have to perform a little string manipulation to get it. The format of a xid is: gtrid,bqual,formatID. The column 'data' contains a concatenation of 'gtrid' and 'bqual'. The columns 'gtrid_length' and 'bqual_length' specify how many bytes each of these values uses; use them to split apart 'data'. In this example, the result is:

 

mysql> xa rollback '1-a00640d:c09d:4ac454ef:b284c0','a00640d:c09d:4ac454ef:b284c2',131075;

 

ERROR 1402 (XA100): XA_RBROLLBACK: Transaction branch was rolled back

 

 

Mysql 的XA事务分为内部XA和外部XA。 外部XA可以参与到外部的分布式事务中，需要应用层介入作为协调者；内部XA事务用于同一实例下跨多引擎事务，由Binlog作为协调者，比如在一个存储引擎提交时，需要将提交信息写入二进制日志，这就是一个分布式内部XA事务，只不过二进制日志的参与者是MySQL本身。 Mysql 在XA事务中扮演的是一个参与者的角色，而不是协调者。

MySQL本身的插件式架构导致在其内部需要使用XA事务，此时MySQL即是协调者，也是参与者。例如，不同的存储引擎之间是完全独立的，因此当一个事务涉及两个不同的存储引擎时，就必须使用内部XA事务。需要特别注意的是，如果将二进制日志看做一个独立的“存储引擎”,就不难理解为什么即使是一个存储引擎参与的事务也需要使用XA事务了。在向存储引擎提交数据时，同时需要将提交的信息写入二进制日志，这就是一个分布式事务。

 

XA的性能问题 

XA的性能很低。一个数据库的事务和多个数据库间的XA事务性能对比可发现，性能差10倍左右。因此要尽量避免XA事务，例如可以将数据写入本地，用高性能的消息系统分发数据。或使用数据库复制等技术。 

只有在这些都无法实现，且性能不是瓶颈时才应该使用XA。

 

C:\Documents and Settings\Administrator>mysql -uroot -p

Enter password: ****

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.5.47 MySQL Community Server (GPL)

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> help xa;

Name: 'XA'

Description:

Syntax:

XA {START|BEGIN} xid [JOIN|RESUME]

 

XA END xid [SUSPEND [FOR MIGRATE]]

 

XA PREPARE xid

 

XA COMMIT xid [ONE PHASE]

 

XA ROLLBACK xid

 

XA RECOVER

 

For XA START, the JOIN and RESUME clauses are not supported.

 

For XA END the SUSPEND [FOR MIGRATE] clause is not supported.

 

Each XA statement begins with the XA keyword, and most of them require

an xid value. An xid is an XA transaction identifier. It indicates

which transaction the statement applies to. xid values are supplied by

the client, or generated by the MySQL server. An xid value has from one

to three parts:

 

xid: gtrid [, bqual [, formatID ]]

 

gtrid is a global transaction identifier, bqual is a branch qualifier,

and formatID is a number that identifies the format used by the gtrid

and bqual values. As indicated by the syntax, bqual and formatID are

optional. The default bqual value is '' if not given. The default

formatID value is 1 if not given.

 

gtrid and bqual must be string literals, each up to 64 bytes (not

characters) long. gtrid and bqual can be specified in several ways. You

can use a quoted string ('ab'), hex string (0x6162, X'ab'), or bit

value (b'nnnn').

 

formatID is an unsigned integer.

 

The gtrid and bqual values are interpreted in bytes by the MySQL

server's underlying XA support routines. However, while an SQL

statement containing an XA statement is being parsed, the server works

with some specific character set. To be safe, write gtrid and bqual as

hex strings.

 

xid values typically are generated by the Transaction Manager. Values

generated by one TM must be different from values generated by other

TMs. A given TM must be able to recognize its own xid values in a list

of values returned by the XA RECOVER statement.

 

For XA START xid starts an XA transaction with the given xid value.

Each XA transaction must have a unique xid value, so the value must not

currently be used by another XA transaction. Uniqueness is assessed

using the gtrid and bqual values. All following XA statements for the

XA transaction must be specified using the same xid value as that given

in the XA START statement. If you use any of those statements but

specify an xid value that does not correspond to some existing XA

transaction, an error occurs.

 

One or more XA transactions can be part of the same global transaction.

All XA transactions within a given global transaction must use the same

gtrid value in the xid value. For this reason, gtrid values must be

globally unique so that there is no ambiguity about which global

transaction a given XA transaction is part of. The bqual part of the

xid value must be different for each XA transaction within a global

transaction. (The requirement that bqual values be different is a

limitation of the current MySQL XA implementation. It is not part of

the XA specification.)

 

 

XA RECOVER 列出所有处于PREPARE状态的XA事务

The XA RECOVER statement returns information for those XA transactions

on the MySQL server that are in the PREPARED state. (See

http://dev.mysql.com/doc/refman/5.5/en/xa-states.html.) The output

includes a row for each such XA transaction on the server, regardless

of which client started it.

 

XA RECOVER output rows look like this (for an example xid value

consisting of the parts 'abc', 'def', and 7):

 

mysql> XA RECOVER

+----------+--------------+--------------+--------+

| formatID | gtrid_length | bqual_length | data   |

+----------+--------------+--------------+--------+

|        7 |            3 |            3 | abcdef |

+----------+--------------+--------------+--------+

 

The output columns have the following meanings:

 

o formatID is the formatID part of the transaction xid

 

o gtrid_length is the length in bytes of the gtrid part of the xid

 

o bqual_length is the length in bytes of the bqual part of the xid

 

o data is the concatenation of the gtrid and bqual parts of the xid

1. formatID 是事务xid的formatID部分。

2. gtrid_length 是xid的gtrid部分的长度，以字节为单位。

3. bqual_length 是xid的bqual部分的长度，以字节为单位。

4. data 是xid的gtrid部分和bqual部分的串联。

URL: http://dev.mysql.com/doc/refman/5.5/en/xa-statements.html

mysql>