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();