• Concepts
  • Global transaction IDs

  • Global transaction IDs
  • Global transaction IDs

    Global transaction IDs

    Note: Version

    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

    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

    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

    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
    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 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

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