Global transaction IDs

Note: Version
requirement

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 Transaction handling.

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 PECL/mysqlnd_ms
1.3.0-alpha or later. Neither are client-side transaction boundary
monitoring nor any setup activities required if using the server
feature.

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.

Idea and client-side
emulation

PECL/mysqlnd_ms 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.

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.

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.

When injection is
done

The plugin transparently maintains the GTID table
on the master. In autocommit mode the plugin injects an
UPDATE statement before executing the users statement for
every master use. In manual transaction mode, the injection is done
before the application calls commit() to close a
transaction. The configuration option report_error 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).

Please note, the PHP version requirements for
transaction
boundary monitoring and their limits.

Limitations

Client-side global transaction ID injection has
shortcomings. The potential issues are not specific to
PECL/mysqlnd_ms but are rather of general nature.

• Global transaction ID
  tables must be deployed on all masters and replicas.
• The GTID can have holes.
  Only PHP clients using the plugin will maintain the table. Other
  clients will not.
• Client-side transaction
  boundary detection is based on API calls only.
• 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.

Using server-side global
transaction identifier

Starting with PECL/mysqlnd_ms 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.

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.

From an applications perspective there is hardly a
difference in using one or the other approach. The following
properties differ.

• 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. 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.
  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.
• Plugin global
  transaction ID statistics are only available with client-side
  emulation because they monitor the emulation.

Note: Global transaction
identifiers in distributed systems

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. PECL/mysqlnd_ms, 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.