<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>XA/Distributed Transactions</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.transactions.html">Local transactions</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.qos-consistency.html">Service level and consistency</a></div>
<div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqlnd-ms.quickstart.xa_transactions" class="section">
<h2 class="title">XA/Distributed Transactions</h2>
<blockquote class="note"><p><strong class="note">Note</strong>:
<strong>Version requirement</strong><br />
<p class="para">
XA related functions have been introduced in PECL mysqlnd_ms version 1.6.0-alpha.
</p>
</p></blockquote>
<blockquote class="note"><p><strong class="note">Note</strong>:
<strong>Early adaptors wanted</strong><br />
<p class="para">
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.
</p>
<p class="para">
Please, contact the development team if you are interested in this feature.
We are looking for real life feedback to complement the feature.
</p>
</p></blockquote>
<p class="para">
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.
</p>
<p class="para">
<em>PECL/mysqlnd_ms</em> 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
<span class="function"><a href="function.mysqlnd-ms-xa-begin.html" class="function">mysqlnd_ms_xa_begin()</a></span>, <span class="function"><a href="function.mysqlnd-ms-xa-commit.html" class="function">mysqlnd_ms_xa_commit()</a></span>
and <span class="function"><a href="function.mysqlnd-ms-xa-rollback.html" class="function">mysqlnd_ms_xa_rollback()</a></span>. SQL details are mostly hidden from
the application as is the need to track and coordinate participants.
</p>
<p class="para">
<div class="example" id="example-2237">
<p><strong>Example #1 General pattern for XA transactions</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">, </span><span style="color: #DD0000">"username"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">) {<br /> </span><span style="color: #FF8000">/* Of course, your error handling is nicer... */<br /> </span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(), </span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br />}<br /><br /></span><span style="color: #FF8000">/* start a global transaction */<br /></span><span style="color: #0000BB">$gtrid_id </span><span style="color: #007700">= </span><span style="color: #DD0000">"12345"</span><span style="color: #007700">;<br />if (!</span><span style="color: #0000BB">mysqlnd_ms_xa_begin</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #0000BB">$gtrid_id</span><span style="color: #007700">)) {<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br />}<br /><br /></span><span style="color: #FF8000">/* run queries as usual: XA BEGIN will be injected upon running a query */<br /></span><span style="color: #007700">if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO orders(order_id, item) VALUES (1, 'christmas tree, 1.8m')"</span><span style="color: #007700">)) {<br /> </span><span style="color: #FF8000">/* Either INSERT failed or the injected XA BEGIN failed */<br /> </span><span style="color: #007700">if (</span><span style="color: #DD0000">'XA' </span><span style="color: #007700">== </span><span style="color: #0000BB">substr</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">sqlstate</span><span style="color: #007700">, </span><span style="color: #0000BB">0</span><span style="color: #007700">, </span><span style="color: #0000BB">2</span><span style="color: #007700">)) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"Global transaction/XA related failure, [%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br /> } else {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT failed, [%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br /> }<br /> </span><span style="color: #FF8000">/* rollback global transaction */<br /> </span><span style="color: #0000BB">mysqlnd_ms_xa_rollback</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #0000BB">$xid</span><span style="color: #007700">);<br /> die(</span><span style="color: #DD0000">"Stopping."</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #FF8000">/* continue carrying out queries on other servers, e.g. other shards */<br /><br />/* commit the global transaction */<br /></span><span style="color: #007700">if (!</span><span style="color: #0000BB">mysqlnd_ms_xa_commit</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #0000BB">$xa_id</span><span style="color: #007700">)) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br />}<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
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.
</p>
<p class="para">
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 <em>XA BEGIN</em> prior to
executing the actual SQL statement on the server. <em>XA BEGIN</em>
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,
<em>$mysqli->query("INSERT INTO orders(order_id, item) VALUES (1, 'christmas tree, 1.8m')")</em>
would indicate such an error. You may want to check the errors SQL state code to
determine whether the actual query (here: <em>INSERT</em>) 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.
</p>
<p class="para">
<div class="example" id="example-2238">
<p><strong>Example #2 Local and global transactions are mutually exclusive</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">, </span><span style="color: #DD0000">"username"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">) {<br /> </span><span style="color: #FF8000">/* Of course, your error handling is nicer... */<br /> </span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(), </span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br />}<br /><br /></span><span style="color: #FF8000">/* start a local transaction */<br /></span><span style="color: #007700">if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">begin_transaction</span><span style="color: #007700">()) {<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d/%s] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">sqlstate</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br />}<br /><br /></span><span style="color: #FF8000">/* cannot start global transaction now - must end local transaction first */<br /></span><span style="color: #0000BB">$gtrid_id </span><span style="color: #007700">= </span><span style="color: #DD0000">"12345"</span><span style="color: #007700">;<br />if (!</span><span style="color: #0000BB">mysqlnd_ms_xa_begin</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #0000BB">$gtrid_id</span><span style="color: #007700">)) {<br /> die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d/%s] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">sqlstate</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br />}<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
<div class="example-contents"><p>The above example will output:</p></div>
<div class="example-contents screen">
<div class="cdata"><pre>
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
</pre></div>
</div>
</div>
</p>
<p class="para">
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
<span class="function"><a href="function.mysqlnd-ms-xa-begin.html" class="function">mysqlnd_ms_xa_begin()</a></span> 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 <span class="function"><a href="function.mysqlnd-ms-xa-begin.html" class="function">mysqlnd_ms_xa_begin()</a></span>. However, note the
plugins <a href="mysqlnd-ms.transaction.html" class="link">limitations on detecting
transaction boundaries.</a>. In the worst case, if using direct SQL
for local transactions (<em>BEGIN</em>,
<em>COMMIT</em>, ...), it may happen that an error is delayed
until some SQL is executed on a server.
</p>
<p class="para">
To end a global transaction invoke <span class="function"><a href="function.mysqlnd-ms-xa-commit.html" class="function">mysqlnd_ms_xa_commit()</a></span> or
<span class="function"><a href="function.mysqlnd-ms-xa-rollback.html" class="function">mysqlnd_ms_xa_rollback()</a></span>. 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.
</p>
<p class="para">
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.
<em>PECL/mysqlnd_ms</em> 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.
</p>
<p class="para">
<div class="example" id="example-2239">
<p><strong>Example #3 Transaction coordinator state store</strong></p>
<div class="example-contents">
<div class="inicode"><pre class="inicode">{
"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"
}
}
}
}</pre>
</div>
</div>
</div>
</p>
<p class="para">
Currently, <em>PECL/mysqlnd_ms</em> supports only using MySQL database tables
as a state store. The SQL definitions of the tables are given in the
<a href="mysqlnd-ms.plugin-ini-json.html#ini.mysqlnd-ms-plugin-config-v2.xa" class="link">plugin configuration section.</a>
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.
</p>
<p class="para">
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 <em>localhost</em>, then <em>localhost</em>
resolves to different machines. There are two solutions to the problem.
Either you do not configure any servers with the host name <em>localhost</em> but
configure an IP address (and port) or, you hint the garbage collection.
In the above example, <em>localhost</em> is used for
<em>master_0</em>, hence it may not resolve to the correct
host during garbage collection. However, <em>participant_localhost_ip</em>
is also set to hint the garbage collection that <em>localhost</em>
stands for the IP <em>192.168.2.12</em>.
</p>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.transactions.html">Local transactions</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.qos-consistency.html">Service level and consistency</a></div>
<div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
