Transaction handling

Transaction handling is fundamentally changed. A SQL transaction is a unit of work 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 a SQL transaction. By default the plugin is not transaction safe.

At the time of writing, applications using SQL transactions together with the plugin must use SQL hints to disable connection switches in the middle of a SQL transaction. Please, find details in the examples section.

The latest version of the mysqlnd library, as found in PHP 5.3.99, allows the plugin to subclass the library C API call trx_autocommit() to detect the status of the autocommit mode. The PHP MySQL extensions either issue a query such as SET AUTOCOMMIT=0|1 or use the mysqlnd library call trx_autcommit() to control the autocommit setting. If an extension makes use of trx_autocommit(), the plugin can be made transaction aware. Transaction awareness cannot be achieved, if using SQL to set the autocommit mode.

The experimental pluging configuration option trx_stickiness=master can be used to make the plugin transaction aware if using PHP 5.3.99. In this mode the plugin stops load balancing if autocommit gets disabled and directs all statements to the master until autocommit gets enabled.