XA/Distributed Transactions

Bilginize: Version requirement

XA related functions have been introduced in PECL mysqlnd_ms version 1.6.0-alpha.

Bilginize: Early adaptors wanted

The feature is currently under development. There may be issues and/or feature limitations. Do not use in production environments, although early lab tests indicate reasonable quality.

Please, contact the development team if you are interested in this feature. We are looking for real life feedback to complement the feature.

XA transactions are a standardized method for executing transactions across multiple resources. Those resources can be databases or other transactional systems. The MySQL server supports XA SQL statements which allows users to carry out a distributed SQL transaction that spawns multiple database servers or any kind as long as they support the SQL statements too. In such a scenario it is in the responsibility of the user to coordinate the participating servers.

PECL/mysqlnd_ms can act as a transaction coordinator for a global (distributed, XA) transaction carried out on MySQL servers only. As a transaction coordinator, the plugin tracks all servers involved in a global transaction and transparently issues appropriate SQL statements on the participants. The global transactions are controlled with mysqlnd_ms_xa_begin(), mysqlnd_ms_xa_commit() and mysqlnd_ms_xa_rollback(). SQL details are mostly hidden from the application as is the need to track and coordinate participants.

Unlike with local transactions, which are carried out on a single server, XA transactions have an identifier (xid) associated with them. The XA transaction identifier is composed of a global transaction identifier (gtrid), a branch qualifier (bqual) a format identifier (formatID). Only the global transaction identifier can and must be given when calling any of the plugins XA functions.

Once a global transaction has been started, the plugin begins tracking servers until the global transaction ends. When a server is picked for query execution, the plugin injects the SQL statement XA BEGIN prior to executing the actual SQL statement on the server. XA BEGIN makes the server participate in the global transaction. If the injected SQL statement fails, the plugin will report the issue in reply to the query execution function that was used. In the above example, $mysqli->query("INSERT INTO orders(order_id, item) VALUES (1, 'christmas tree, 1.8m')") would indicate such an error. You may want to check the errors SQL state code to determine whether the actual query (here: INSERT) has failed or the error is related to the global transaction. It is up to you to ignore the failure to start the global transaction on a server and continue execution without having the server participate in the global transaction.

Warning: mysqlnd_ms_xa_begin(): (mysqlnd_ms) Some work is done outside global transaction. You must end the active local transaction first in ... on line ...
[1400/XAE09] (mysqlnd_ms) Some work is done outside global transaction. You must end the active local transaction first

A global transaction cannot be started when a local transaction is active. The plugin tries to detect this situation as early as possible, that is when mysqlnd_ms_xa_begin() is called. If using API calls only to control transactions, the plugin will know that a local transaction is open and return an error for mysqlnd_ms_xa_begin(). However, note the plugins limitations on detecting transaction boundaries.. In the worst case, if using direct SQL for local transactions (BEGIN, COMMIT, ...), it may happen that an error is delayed until some SQL is executed on a server.

To end a global transaction invoke mysqlnd_ms_xa_commit() or mysqlnd_ms_xa_rollback(). When a global transaction is ended all participants must be informed of the end. Therefore, PECL/mysqlnd_ms transparently issues appropriate XA related SQL statements on some or all of them. Any failure during this phase will cause an implicit rollback. The XA related API is intentionally kept simple here. A more complex API that gave more control would bare few, if any, advantages over a user implementation that issues all lower level XA SQL statements itself.

XA transactions use the two-phase commit protocol. The two-phase commit protocol is a blocking protocol. There are cases when no progress can be made, not even when using timeouts. Transaction coordinators should survive their own failure, be able to detect blockades and break ties. PECL/mysqlnd_ms takes the role of a transaction coordinator and can be configured to survive its own crash to avoid issues with blocked MySQL servers. Therefore, the plugin can and should be configured to use a persistent and crash-safe state to allow garbage collection of unfinished, aborted global transactions. A global transaction can be aborted in an open state if either the plugin fails (crashes) or a connection from the plugin to a global transaction participant fails.

{
    "myapp": {
        "xa": {
            "state_store": {
                "participant_localhost_ip": "192.168.2.12",
                "mysql": {
                    "host": "192.168.2.13",
                    "user": "root",
                    "password": "",
                    "db": "test",
                    "port": "3312",
                    "socket": null
                }
            }
        },
        "master": {
            "master_0": {
                "host": "localhost",
                "socket": "\/tmp\/mysql.sock"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.2.14",
                "port": "3306"
            }
        }
    }
}

Currently, PECL/mysqlnd_ms supports only using MySQL database tables as a state store. The SQL definitions of the tables are given in the plugin configuration section. Please, make sure to use a transactional and crash-safe storage engine for the tables, such as InnoDB. InnoDB is the default table engine in recent versions of the MySQL server. Make also sure the database server itself is highly available.

If a state store has been configured, the plugin can perform a garbage collection. During garbage collection it may be necessary to connect to a participant of a failed global transaction. Thus, the state store holds a list of participants and, among others, their host names. If the garbage collection is run on another host but the one that has written a participant entry with the host name localhost, then localhost resolves to different machines. There are two solutions to the problem. Either you do not configure any servers with the host name localhost but configure an IP address (and port) or, you hint the garbage collection. In the above example, localhost is used for master_0, hence it may not resolve to the correct host during garbage collection. However, participant_localhost_ip is also set to hint the garbage collection that localhost stands for the IP 192.168.2.12.