The PHP replication and load balancing plugin has a configuration setting to run all transactions on the master. But how to detect the start of a transaction? I got a hint to look at a flag in the client-server protocol which tells us, if we are in a transaction. I was not aware of the flag and, I loved the idea when I heard it. The plugin wouldn’t have to worry about how the client starts a transaction. A PHP client can start a transaction by turning off autocommit via API (e.g. mysqli_autocommit()) or executing SQL (BEGIN, START TRANSACTION, SET AUTOCOMMIT=0). It is time consuming to catch them all, if possible at all.

Server status flag from the protocol

I hacked mysqlnd to print the protocol transaction flag after statement execution.

$link = new mysqli("localhost", "root", "root", "test"); 
$link->autocommit(false);
$link->query("DROP TABLE IF EXISTS test"); 
$link->query("CREATE TABLE test(id INT) ENGINE=InnoDB"); 
$link->query("SET @myvar=1"); 
$link->query("SELECT @myvar"); 
$link- >query("SELECT * FROM test"); 
$link->query("INSERT INTO test(id) VALUES (1)"); 
$link->query("ROLLBACK"); 
var_dump($link->query("SELECT * FROM test")->fetch_all());

in transaction 0 - SET AUTOCOMMIT=0
in transaction 0 - DROP TABLE IF EXISTS test
in transaction 0 - CREATE TABLE test(id INT) ENGINE=InnoDB
in transaction 0 - SET @myvar=1
in transaction 0 - SELECT @myvar
in transaction 1 - SELECT * FROM test
in transaction 1 - INSERT INTO test(id) VALUES (1)
in transaction 0 - ROLLBACK
in transaction 1 - SELECT * FROM test
array(0) {
}

The test script turns off autocommit using the $link->autocommit() API call. The mysqlnd library makes the SQL statement SET AUTOCOMMIT=0 from it. The server does not set SERVER_IN_TRANS flag in his reply to the client. The script continues and creates a table. The server still does not announce that we are in a transaction, because some statements cause an implicit commit. It takes until after the execution of SELECT * FROM test before the server sets the protocol flag to announce a running transaction.

If the plugin would base its transaction aware load balancing on the protocol flag, it would send SET AUTOCOMMIT=0, DROP TABLE IF EXISTS test, CREATE TABLE test(id INT) ENGINE=InnoDB, SET @myvar=1 to the master, run SELECT @myvar and SELECT * FROM test on a slave before the plugin learns that the previous statement was part of a transaction and the master gets used for INSERT INTO test(id) VALUES (1).

The plugin works better

With todays plugin implementation, everything after $link->autocommit(false) will be considered part of a transaction. Everything will be run on the master until $link->autocommit(true) is run.

The plugin has limits

You can fool the plugin executing SQL to start a transaction such as BEGIN, START TRANSACTION or SET AUTOCOMMIT=0. However, I still prefer that limitation over the protocol flag.

The protocol flag is not suited for transaction aware load balancing. For other applications it may shine. For example, its fantastic for Global Transaction ID injection.