mysqli-php-quickstart-php-transactions-8

  • Quick start guide
  • API support for transactions

  • API support for transactions
  • API support for transactions

    API support for transactions

    The MySQL server supports transactions depending on
    the storage engine used. Since MySQL 5.5, the default storage
    engine is InnoDB. InnoDB has full ACID transaction support.

    Transactions can either be controlled using SQL or
    API calls. It is recommended to use API calls for enabling and
    disabling the auto commit mode and for committing and rolling back
    transactions.

    Example #1 Setting auto commit mode with SQL and through
    the API

    <?php
    $mysqli 
    = new mysqli("example.com""user""password""database");
    if (
    $mysqli->connect_errno) {
        echo 
    "Failed to connect to MySQL: (" $mysqli->connect_errno ") " $mysqli->connect_error;
    }

    /* Recommended: using API to control transactional settings */
    $mysqli->autocommit(false);

    /* Won't be monitored and recognized by the replication and the load balancing plugin */
    if (!$mysqli->query('SET AUTOCOMMIT = 0')) {
        echo 
    "Query failed: (" $mysqli->errno ") " $mysqli->error;
    }
    ?>

    Optional feature packages, such as the replication
    and load balancing plugin, can easily monitor API calls. The
    replication plugin offers transaction aware load balancing, if
    transactions are controlled with API calls. Transaction aware load
    balancing is not available if SQL statements are used for setting
    auto commit mode, committing or rolling back a transaction.

    Example #2 Commit and rollback

    <?php
    $mysqli 
    = new mysqli("example.com""user""password""database");
    $mysqli->autocommit(false);

    $mysqli->query("INSERT INTO test(id) VALUES (1)");
    $mysqli->rollback();

    $mysqli->query("INSERT INTO test(id) VALUES (2)");
    $mysqli->commit();
    ?>

    Please note, that the MySQL server cannot roll back
    all statements. Some statements cause an implicit commit.

    See also