Why does multi_query not work when I use transactions?

Question

Here is an example.

$mysqli = new mysqli("localhost", "root", "123", "temp");

$mysqli->begin_transaction();

$sql1 = "insert into test (Name) values ('pratik5');";
$sql1 .= "insert into test (Name) values ('pratik6');";

$test = $mysqli->multi_query($sql1);

$mysqli->commit();

There isn't any error in either of the queries, but when calling commit() the values are not stored in the database. The same works perfectly fine if split into separate queries and executed via query().

Answer 1

$mysqli->multi_query($sql1);
$mysqli->commit(); // This will result in a "Commands out of sync; you can't run this command now" error.

The above is identical to:

$mysqli->multi_query($sql1);
$mysqli->query("commit"); // This will result in a "Commands out of sync; you can't run this command now" error.

Whatever you put in $mysqli->query("...");, it WILL result in a "Commands out of sync" error, even with a simple SELECT 1;

The reason for this error is because ->commit() operation runs a single query (commit;). However, the results of the previous queries have not been read.

When a single query() operation is used, the MySQL server will answer with a response frame that depends on the query statement.

When using multi_query(), the following happens at MySQL communication protocol level:

1. A "Request Set Option" (as displayed in Wireshark) frame is sent with "multi statements" flag set to ON.
2. A frame containing the whole string transmitted to multi_query() as request.
3. MySQL server answers with a response that may contain different resultsets. The same is true when calling a stored procedure.

Solution 1

If you want to use multi_query(), you must have your start transaction / commit operations as part of it:

$mysqli = new mysqli("localhost", "root", "123", "temp");

$sql1 = "start transaction;"; // $mysqli->begin_transaction() is a convenience function for simply doing this.
$sql1 .= "insert into test (Name) values ('pratik5');";
$sql1 .= "insert into test (Name) values ('pratik6');";
$sql1 .= "commit;"; // $mysqli->commit() is a convenience function for simply doing this.

$mysqli->multi_query($sql1);

/* As in "Solution 2", if you plan to perform other queries on DB resource
   $mysqli after this, you must consume all the resultsets:
// This loop ensures that all resultsets are processed and consumed:
do {
    $mysqli->use_result();
}
while ($mysqli->next_result());
*/

Solution 2

$mysqli = new mysqli("localhost", "root", "123", "temp");

$mysqli->begin_transaction();

$sql1 = "insert into test (Name) values ('pratik5');";
$sql1 .= "insert into test (Name) values ('pratik6');";

$mysqli->multi_query($sql1);

// This loop ensures that all resultsets are processed and consumed:
do {
    $mysqli->use_result();
}
while ($mysqli->next_result());

// Now that all resultsets are processed, a single query `commit;` can happen:
$mysqli->commit();

MySQL Reference: "Commands out of sync".

Answer 2

You shouldn't use multi query. Rewrite your code as follows

$mysqli->begin_transaction();

$mysqli->query("insert into test (Name) values ('pratik5')");
$mysqli->query("insert into test (Name) values ('pratik6')");

$mysqli->commit();

or, for the real-life inserts,

$mysqli->begin_transaction();

$stmt = $mysqli->prepare("insert into test (Name) values (?)");
$stmt->bind_param("s", $name);
$name = 'pratik5';
$stmt->execute();
$name = 'pratik6';
$stmt->execute();

$mysqli->commit();

Answer 3

First thing first, in the example you have shown you should not be using multi_query(). You have two separate statements which should be executed separately. See Your Common Sense's answer

multi_query() should be rarely used. It should be used only in situations when you already have a string composed of multiple queries, which you absolutely trust. Don't ever allow variable input into multi_query()!

Why commit() doesn't work after multi_query()?

Truth be told MySQL does throw an error on commit(), but mysqli is not able to throw the error as exception. Whether it is a bug or a technical limitation I do not know. You can see the error if you check manually $mysqli->error property. You should see an error as follows:

Commands out of sync; you can't run this command now

However, the exception is thrown correctly once you call use_result() or store_result().

$mysqli->multi_query(/* SQLs */);
$mysqli->commit();
$r = $mysqli->use_result(); // Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now

The reason why MySQL says the commands are out of sync is because you can only execute a single command at a time per each MySQL session. However, multi_query() sends the whole SQL string to MySQL in a single command, and let's MySQL run the queries asynchronously. PHP will initially wait for the first result-set producing non-DML query (not INSERT, UPDATE or DELETE) to finish running before the control is passed back to PHP script. This allows MySQL to run the rest of the SQL on the server and buffer the results, while you can do some other operations in PHP and collect the results later. You can't run another SQL command on the same connection until you iterate over all the results from the previous asynchronous queries.

As pointed out in another answer, commit() will try to execute another command on the same connection. You are getting the out of sync error, because you simply haven't finished processing the multi_query() command.

MySQLi blocking loop

Each asynchronous query should be followed by a blocking loop in mysqli. A blocking loop will iterate over all the executed queries on the server and fetch the results one by one, which you can then process in PHP. Here is an example of such loop:

do {
    $result = $mysqli->use_result();
    if ($result) {
        // process the results here
        $result->free();
    }
} while ($mysqli->next_result()); // Next result will block and wait for next query to finish
$mysqli->store_result(); // Needed to fetch the error as exception

You must have the blocking loop always, even when you know the queries are not going to produce any result-sets.

Solution

Stay away from multi_query()! Most of the time there's a better way of executing SQL files. If you have your queries separate, then don't concatenate them together, and execute each on their own.

If you really need to use multi_query(), and you would like to wrap it in transaction, you must put the commit() after the blocking loop. All the results need to be iterated over before you can execute the COMMIT; command.

$mysqli->begin_transaction();

$sql1 = "insert into test (Name) values ('pratik5');";
$sql1 .= "insert into test (Name) values ('pratik6');";

$test = $mysqli->multi_query($sql1);

// $mysqli->commit();

do {
    $result = $mysqli->use_result();
    if ($result) {
        // process the results here
        $result->free();
    }
} while ($mysqli->next_result());
$mysqli->store_result(); // To fetch the error as exception

$mysqli->commit();

Of course to see any of the mysqli errors you need to enable exception mode. Simply, put this line before new mysqli():

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);