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 requirement
The plugin configuration option trx_stickiness=master requires PHP 5.4.0 or newer.
Please note the restrictions outlined in the transaction handling concepts section.
English translation
You have asked to visit this site in English. For now, only the interface is translated, but not all the content yet.If you want to help me in translations, your contribution is welcome. All you need to do is register on the site, and send me a message asking me to add you to the group of translators, which will give you the opportunity to translate the pages you want. A link at the bottom of each translated page indicates that you are the translator, and has a link to your profile.
Thank you in advance.
Document created the 30/01/2003, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/php-rf-mysqlnd-ms.quickstart.transactions.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.
