<!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>Global transaction IDs</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.qos-consistency.html">Service level and consistency</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.concept_cache.html">Cache integration</a></div>
<div class="up"><a href="mysqlnd-ms.concepts.html">Concepts</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqlnd-ms.gtid" class="section">
<h2 class="title">Global transaction IDs</h2>
<blockquote class="note"><p><strong class="note">Note</strong>:
<strong>Version requirement</strong><br />
<p class="para">
Client side global transaction ID injection exists as of mysqlnd_ms version 1.2.0-alpha.
Transaction boundaries are detected by monitoring API calls. This is possible
as of PHP 5.4.0. Please, see also <a href="mysqlnd-ms.transaction.html" class="link">Transaction handling</a>.
</p>
<p class="para">
As of MySQL 5.6.5-m8 the MySQL server features built-in global transaction identifiers.
The MySQL built-in global transaction ID feature is supported by
<em>PECL/mysqlnd_ms</em> 1.3.0-alpha or
later. Neither are client-side transaction boundary monitoring nor any setup
activities required if using the server feature.
</p>
<p class="para">
Please note, all MySQL 5.6 production versions do not provide
clients with enough information to use GTIDs for enforcing session consistency.
In the worst case, the plugin will choose the master only.
</p>
</p></blockquote>
<p class="para">
<em class="emphasis">Idea and client-side emulation</em>
</p>
<p class="para">
<em>PECL/mysqlnd_ms</em> can do client-side transparent global transaction ID injection.
In its most basic form, a global transaction identifier is a counter which is
incremented for every transaction executed on the master. The counter is held
in a table on the master. Slaves replicate the counter table.
</p>
<p class="para">
In case of a master failure a database administrator can easily identify the
most recent slave for promoting it as a new master. The most recent slave has
the highest transaction identifier.
</p>
<p class="para">
Application developers can ask the plugin for the global transaction identifier
(GTID) for their last successful write operation. The plugin will return
an identifier that refers to an transaction no older than that of the clients last
write operation. Then, the GTID can be passed as a parameter
to the quality of service (QoS) filter as an option for session consistency.
Session consistency ensures read your writes. The filter ensures that all
reads are either directed to a master or a slave which has replicated the write
referenced by the GTID.
</p>
<p class="para">
<em class="emphasis">When injection is done</em>
</p>
<p class="para">
The plugin transparently maintains the GTID table on the master.
In autocommit mode the plugin injects an <em>UPDATE</em> statement
before executing the users statement for every master use. In manual
transaction mode, the injection is done before the application calls
<em>commit()</em> to close a transaction. The configuration option
<em>report_error</em> of the GTID section in the plugins configuration
file is used to control whether a failed injection shall abort the current
operation or be ignored silently (default).
</p>
<p class="para">
Please note, the
PHP version requirements for
<a href="mysqlnd-ms.transaction.html" class="link">transaction boundary monitoring</a>
and their limits.
</p>
<p class="para">
<em class="emphasis">Limitations</em>
</p>
<p class="para">
Client-side global transaction ID injection has shortcomings. The potential
issues are not specific to <em>PECL/mysqlnd_ms</em>
but are rather of general nature.
<ul class="itemizedlist">
<li class="listitem">
<span class="simpara">
Global transaction ID tables must be deployed on all masters and replicas.
</span>
</li>
<li class="listitem">
<span class="simpara">
The GTID can have holes. Only PHP clients using the plugin will
maintain the table. Other clients will not.
</span>
</li>
<li class="listitem">
<span class="simpara">
Client-side transaction boundary detection is based on API calls only.
</span>
</li>
<li class="listitem">
<span class="simpara">
Client-side transaction boundary detection does not take implicit
commit into account. Some MySQL SQL statements cause an implicit
commit and cannot be rolled back.
</span>
</li>
</ul>
</p>
<p class="para">
<em class="emphasis">Using server-side global transaction identifier</em>
</p>
<p class="para">
Starting with <em>PECL/mysqlnd_ms</em> 1.3.0-alpha
the MySQL 5.6.5-m8 or newer built-in global
transaction identifier feature is supported. Use of the server feature lifts
all of the above listed limitations. Please, see the MySQL Reference Manual
for limitations and preconditions for using server built-in global transaction
identifiers.
</p>
<p class="para">
Whether to use the client-side emulation or the server built-in
functionality is a question not directly related to the plugin, thus it is not
discussed in depth. There are no plans to remove the client-side emulation and
you can continue to use it, if the server-side solution is no option. This may
be the case in heterogenous environments with old MySQL server or, if any of the
server-side solution limitations is not acceptable.
</p>
<p class="para">
From an applications perspective there is hardly a difference in using one or the
other approach. The following properties differ.
<ul class="itemizedlist">
<li class="listitem">
<span class="simpara">
Client-side emulation, as shown in the manual, is using an easy to compare sequence number
for global transactions. Multi-master is not handled to keep the manual examples easy.
</span>
<span class="simpara">
Server-side built-in feature is using a combination of a server identifier
and a sequence number as a global transaction identifier. Comparison cannot
use numeric algebra. Instead a SQL function must be used. Please,
see the MySQL Reference Manual for details.
</span>
<span class="simpara">
Server-side built-in feature of MySQL 5.6 cannot be used to ensure session consistency
under all circumstances. Do not use it for the quality-of-service feature. Here is a simple
example why it will not give reliable results. There are more edge cases that cannot
be covered with limited functionality exported by the server. Currently, clients can
ask a MySQL replication master for a list of all executed global transaction IDs only.
If a slave is configured not to replicate all transactions, for example, because replication
filters are set, then the slave will never show the same set of executed global transaction
IDs. Albeit the slave may have replicated a clients writes and it may be a candidate
for a consistent read, it will never be considered by the plugin. Upon write the plugin
learns from the master that the servers complete transaction history consists of GTID=1..3.
There is no way for the plugin to ask for the GTID of the write transaction itself, say GTID=3.
Assume that a slave does not replicate the transactions GTID=1..2 but only GTID=3 because of
a replication feature. Then, the slaves transaction history is GTID=3. However, the plugin tries
to find a node which has a transaction history of GITD=1...3. Albeit the slave has replicated
the clients write and session consistency may be achieved when reading from the slave, it
will not be considered by the plugin. This is not a fault of the plugin implementation but
a feature gap on the server side. Please note, this is a trivial case to illustrate the
issue there are other issues. In sum you are asked not to attempt using MySQL 5.6 built-in GTIDs
for enforcing session consistency. Sooner or later the load balancing will stop working properly
and the plugin will direct all session consistency requests to the master.
</span>
</li>
<li class="listitem">
<span class="simpara">
Plugin global transaction ID statistics are only available with client-side
emulation because they monitor the emulation.
</span>
</li>
</ul>
</p>
<blockquote class="note"><p><strong class="note">Note</strong>:
<strong>Global transaction identifiers in distributed systems</strong><br />
<p class="para">
Global transaction identifiers can serve multiple purposes in the context of distributed
systems, such as a database cluster. Global transaction identifiers can
be used for, for example, system wide identification of transactions,
global ordering of transactions, heartbeat mechanism and
for checking the replication status of replicas. <em>PECL/mysqlnd_ms</em>, a clientside
driver based software, does focus on using GTIDs for tasks that can be
handled at the client, such as checking the replication status of replicas
for asynchronous replication setups.
</p>
</p></blockquote>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.qos-consistency.html">Service level and consistency</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.concept_cache.html">Cache integration</a></div>
<div class="up"><a href="mysqlnd-ms.concepts.html">Concepts</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
