mysqlnd-ms-php-quickstart-php-transactions-3

  • Quickstart and
    Examples
  • Local transactions

  • 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
    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.