• Concepts
  • Local transaction handling

  • Local transaction handling
  • Local transaction handling

    Local transaction handling

    Transaction handling is fundamentally changed. An
    SQL transaction is a unit of work that is run on one database
    server. The unit of work consists of one or more SQL

    By default the plugin is not aware of SQL
    transactions. The plugin may switch connections for load balancing
    at any point in time. Connection switches may happen in the middle
    of a transaction. This is against the nature of an SQL transaction.
    By default, the plugin is not transaction safe.

    Any kind of MySQL load balancer must be hinted
    about the begin and end of a transaction. Hinting can either be
    done implicitly by monitoring API calls or using SQL hints. Both
    options are supported by the plugin, depending on your PHP version.
    API monitoring requires PHP 5.4.0 or newer. The plugin, like any
    other MySQL load balancer, cannot detect transaction boundaries
    based on the MySQL Client Server Protocol. Thus, entirely
    transparent transaction aware load balancing is not possible. The
    least intrusive option is API monitoring, which requires little to
    no application changes, depending on your application.

    Please, find examples of using SQL hints or the API
    monitoring in the examples section. The details behind the API monitoring,
    which makes the plugin transaction aware, are described below.

    Beginning with PHP 5.4.0, the mysqlnd library allows this
    plugin to subclass the library C API call
    set_autocommit(), to detect the status of
    autocommit mode.

    The PHP MySQL extensions either issue a query (such
    as SET AUTOCOMMIT=0|1), or use the mysqlnd library call
    set_autocommit() to control the autocommit
    setting. If an extension makes use of set_autocommit(),
    the plugin can be made transaction aware. Transaction awareness
    cannot be achieved if using SQL to set the autocommit mode. The
    library function set_autocommit() is called by the
    mysqli_autocommit() and
    PDO::setAttribute(PDO::ATTR_AUTOCOMMIT) user API

    The plugin configuration option trx_stickiness=master can be used to make the
    plugin transactional aware. In this mode, the plugin stops load
    balancing if autocommit becomes disabled, and directs all
    statements to the master until autocommit gets enabled.

    An application that does not want to set SQL hints
    for transactions but wants to use the transparent API monitoring to
    avoid application changes must make sure that the autocommit
    settings is changed exclusively through the listed API calls.

    API based transaction boundary detection has been
    improved with PHP 5.5.0 and PECL/mysqlnd_ms 1.5.0 to cover not only
    calls to mysqli_autocommit() but also
    mysqli_commit() and mysqli_rollback().