Examples
Local transactions
Local transactions
Local transactions
The current version of the plugin is not
transaction safe by default, because it is not aware of running
transactions in all cases. SQL transactions are units of work to be
run on a single server. The plugin does not always know when the
unit of work starts and when it ends. Therefore, the plugin may
decide to switch connections in the middle of a transaction.
No kind of MySQL load balancer can detect
transaction boundaries without any kind of hint from the
application.
You can either use SQL hints to work around this
limitation. Alternatively, you can activate transaction API call
monitoring. In the latter case you must use API calls only to
control transactions, see below.
Example #1 Plugin config with one slave and one
master
[myapp]
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "192.168.2.27",
"port": "3306"
}
}
}
}
Example #2 Using SQL hints for transactions
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (!$mysqli) {
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
}
/* Not a SELECT, will use master */
if (!$mysqli->query("START TRANSACTION")) {
/* Please use better error handling in your code */
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* Prevent connection switch! */
if (!$mysqli->query(sprintf("/*%s*/INSERT INTO test(id) VALUES (1)", MYSQLND_MS_LAST_USED_SWITCH))) {
/* Please do proper ROLLBACK in your code, don't just die */
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if ($res = $mysqli->query(sprintf("/*%s*/SELECT COUNT(*) AS _num FROM test", MYSQLND_MS_LAST_USED_SWITCH))) {
$row = $res->fetch_assoc();
$res->close();
if ($row['_num'] > 1000) {
if (!$mysqli->query(sprintf("/*%s*/INSERT INTO events(task) VALUES ('cleanup')", MYSQLND_MS_LAST_USED_SWITCH))) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
}
} else {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if (!$mysqli->query(sprintf("/*%s*/UPDATE log SET last_update = NOW()", MYSQLND_MS_LAST_USED_SWITCH))) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if (!$mysqli->query(sprintf("/*%s*/COMMIT", MYSQLND_MS_LAST_USED_SWITCH))) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
$mysqli->close();
?>Starting with PHP 5.4.0, the mysqlnd
library allows the plugin to monitor the status of the
autocommit mode, if the mode is set by API calls instead
of using SQL statements such as SET AUTOCOMMIT=0. This
makes it possible for the plugin to become transaction aware. In
this case, you do not need to use SQL hints.
If using PHP 5.4.0 or newer, API calls that enable
autocommit mode, and when setting the plugin configuration
option trx_stickiness=master, the plugin can
automatically disable load balancing and connection switches for
SQL transactions. In this configuration, the plugin stops load
balancing if autocommit is disabled and directs all
statements to the master. This prevents connection switches in the
middle of a transaction. Once autocommit is re-enabled,
the plugin starts to load balance statements again.
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().
Example #3 Transaction aware load balancing:
trx_stickiness setting
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "127.0.0.1",
"port": "3306"
}
},
"trx_stickiness": "master"
}
}
Example #4 Transaction aware
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (!$mysqli) {
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
}
/* Disable autocommit, plugin will run all statements on the master */
$mysqli->autocommit(false);
if (!
$mysqli->query("INSERT INTO test(id) VALUES (1)")) {
/* Please do proper ROLLBACK in your code, don't just die */
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if ($res = $mysqli->query("SELECT COUNT(*) AS _num FROM test")) {
$row = $res->fetch_assoc();
$res->close();
if ($row['_num'] > 1000) {
if (!$mysqli->query("INSERT INTO events(task) VALUES ('cleanup')")) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
}
} else {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if (!$mysqli->query("UPDATE log SET last_update = NOW()")) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if (!$mysqli->commit()) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* Plugin assumes that the transaction has ended and starts load balancing again */
$mysqli->autocommit(true);
$mysqli->close();
?>Note: Version
requirementThe plugin configuration option trx_stickiness=master requires PHP 5.4.0 or
newer.
Please note the restrictions outlined in the
transaction
handling concepts section.