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
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
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
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.
Client-side global transaction ID injection has shortcomings.
The potential issues are not specific to
PECL/mysqlnd_ms but are rather of general
- 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
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.
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.