php - 为什么我使用事务时 multi_query 不起作用？

Question

这是一个例子。

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

两个查询都没有错误，但是调用 commit() 时这些值不存储在数据库中。如果拆分为单独的查询并通过 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.

以上等同于:

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

无论你放什么 $mysqli->query("..."); ，它将导致 "Commands out of sync"错误，即使是简单的 SELECT 1 ;

这个错误的原因是因为 ->commit()操作运行单个查询 ( commit;)。但是， 结果以前的查询尚未被读取。

当单 query()使用操作时，MySQL 服务器将使用取决于查询语句的响应帧进行响应。

使用 multi_query() 时，以下发生在 MySQL 通信协议(protocol)级别:

发送“请求设置选项”(如 Wireshark 中所示)帧，其中“多语句”标志设置为 ON。

包含传输到 multi_query() 的整个字符串的帧就像要求的一样。

MySQL 服务器以可能包含不同结果集的响应进行响应。调用存储过程时也是如此。

解决方案 1

如果你想使用 multi_query() , 你必须有你的 start transaction/ commit作为其中一部分的操作:

$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());
*/

解决方案 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 引用: "Commands out of sync" .