(mysql.info) xa

Info Catalog

(mysql.info) set-transaction

(mysql.info) transactional-commands

 
 13.4.7 XA Transactions
 ----------------------
 

Menu

 
* xa-statements                XA Transaction SQL Syntax
* xa-states                    XA Transaction States
 
 MySQL 5.0.3 and up provides server-side support for XA transactions.
 Currently, this support 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'. This
 document is published by The Open Group and available at
 `http://www.opengroup.org/public/pubs/catalog/c193.htm'.  Limitations
 of the current XA implementation are described in 
 xa-restrictions.
 
 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.
 
 Currently, among the MySQL Connectors, MySQL Connector/J 5.0.0 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 allow
 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 non-distributed transaction, but
 not for a distributed transaction.)
 
 Some examples of distributed transactions:
 
    * An application may act as an integration tool that combines a
      messaging service with an RDBMS. The application makes sure that
      transactions dealing with message sending, retrieval, and
      processing that also involve a transactional database all happen
      in a global transaction. You can think of this as `transactional
      email.'
 
    * An application performs actions that involve different database
      servers, such as a MySQL server and an Oracle server (or multiple
      MySQL servers), where actions that involve multiple servers must
      happen as part of a global transaction, rather than as separate
      transactions local to each server.
 
    * A bank keeps account information in an RDBMS and distributes and
      receives money via automated teller machines (ATMs). It is
      necessary to ensure that ATM actions are correctly reflected in
      the accounts, but this cannot be done with the RDBMS alone. A
      global transaction manager integrates the ATM and database
      resources to ensure overall consistency of financial transactions.
 
 Applications that use global transactions involve one or more Resource
 Managers and a Transaction Manager:
 
    * A Resource Manager (RM) provides access to transactional
      resources. A database server is one kind of resource manager. It
      must be possible to either commit or roll back transactions
      managed by the RM.
 
    * A Transaction Manager (TM) coordinates the transactions that are
      part of a global transaction. It communicates with the RMs that
      handle each of these transactions. The individual transactions
      within a global transaction are `branches' of the global
      transaction. Global transactions and their branches are identified
      by a naming scheme described later.
 
 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.
 
 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 musts roll back. To manage a global transaction, it is
 necessary to take into account that any component or the connecting
 network might fail.
 
 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.
 
   1. 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.
 
   2. 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.
 
 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.
 

Info Catalog

(mysql.info) set-transaction

(mysql.info) transactional-commands