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
statements.
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
calls.
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_begin(),
mysqli_commit() and mysqli_rollback().