PECL/mysqlnd_ms is now transaction aware, sometimes. A new experimental configuration option trx_stickiness=master disables load balancing if autocommit mode is turned off via API. This makes the mysqlnd library plugin transaction safe. Unfortunately it requires PHP 5.3.99 and, it can be fooled by using SQL to control autocommit. It requires some discipline to use.

Past and today: SQL hints for transactions

By default the PHP replication and load balancing plugin (mysqlnd_ms) is not transaction safe, because it is not transaction aware. The plugin does not know when a transaction starts and when it ends. Thus, it may decide to load balance statements and switch connections in the middle of a transaction. So far, SQL hints have been the only way to prevent connection switches. SQL hints can be used to overrule each and every automatic decision of the plugin.

$mysqli->autocommit(FALSE);
$mysqli->query(sprintf("/*%s*/INSERT INTO test(id) VALUES (1)", MYSQLND_MS_MASTER_SWITCH));
$mysqli->query(sprintf("/*%s*/INSERT INTO test(id) VALUES (2)", MYSQLND_MS_LAST_USED_SWITCH));
$mysqli->commit();
$mysqli->autocommit(TRUE);

The SQL hints in the example are used to run all of the statements on the master. If the first INSERT would use MYSQLND_MS_SLAVE_SWITCH instead, all statements would be run on one of the slaves. Whatever server you pick for the first statement, is also used for the second INSERT because of the SQL hint MYSQLND_MS_LAST_USED_SWITCH and the COMMIT. The statements are run as a unit of work on the same server.

Future: trx_stickiness=master

With PHP 5.3.99 and the new plugin configuration directive trx_stickiness=master you do not need the SQL hints any more, sometimes. The example below without the SQL hint does exactly the same as the first example from above: it tries to commit a transaction consisting of two INSERT statements on the master.

[myapp]
master[]=localhost:/tmp/mysql.sock
slave[]=192.168.2.27:3306
trx_stickiness=master

$mysqli->autocommit(FALSE);
$mysqli->query("INSERT INTO test(id) VALUES (1)");
$mysqli->query("INSERT INTO test(id) VALUES (2)");
$mysqli->commit();
$mysqli->autocommit(TRUE);

The mysqlnd library allows plugins to subclass the mysqlnd internal function trx_autocommit() as of PHP 5.3.99. PECL/mysqlnd_ms does overwrite it to monitor the state of the autocommit setting. If trx_stickiness=master and autocommit has been turned off via a user API call, such as $mysqli->autocommit(FALSE), the plugin knows that you want to run transactions. It stops load balancing and switching connections until autocommit is turned on again. In the meantime it executes all statements on the master. Please note that the plugin does not monitor SQL statements which change the autocommit mode, such as SET AUTOCOMMIT=0. The plugins autocommit monitoring is based on the mysqlnd library call trx_autocommit().

Who calls mysqlnd’s trx_autocommit()?

Please, consider trx_stickiness as an experimental feature. It got implemented today and some basic testing has been done. However, applications that need to rely on transactions must not go with "some basic testing". For example, the applications need to know exactly which PHP MySQL API call is using the mysqlnd library call trx_autocommit().

To be on the safe side, you should run your own test. Whenever you are unsure if a user API call invokes trx_autocommit(), which is a requirement for trx_stickiness=master to work properly, wrap the call in two calls of mysqlnd_ms_get_stats(). Let the user call deactivate autocommit. Compare the statistics trx_autocommit_off and trx_autocommit_on before and after the call. If both are unchanged, the user API call must have used a SQL statement to bypass the mysqlnd library. In that case you must not use trx_stickiness=master.

$stats = mysqlnd_ms_get_stats();
printf("trx_autocommit_on= %d\n", $stats['trx_autocommit_on']);
printf("trx_autocommit_off = %d\n", $stats['trx_autocommit_off']);
$mysqli->autocommit(FALSE);
$stats = mysqlnd_ms_get_stats();
printf("trx_autocommit_on= %d\n", $stats['trx_autocommit_on']);
printf("trx_autocommit_off = %d\n", $stats['trx_autocommit_off']);

I’ve added a few more details to the documentation draft. Though, it may take some hours until it appears on the server.

That’s it folks!

My feature whishlist for 1.0.0 is empty. Next is testing and releasing an alpha version.

Feature requests are most welcome. However, I guess some of you want the convenience of a downloadable alpha release to try it out, to find what’s missing.