mysqli::commit 和 mysqli::rollback 如何工作？

Question

我已阅读在线PHP手册，但仍不确定这两个函数的工作方式：mysqli::commit和mysqli::rollback。

我首先要做的事情是：

$mysqli->autocommit(FALSE);

然后我进行一些查询：

$mysqli->query("...");
$mysqli->query("...");
$mysqli->query("...");

然后，我通过执行以下操作提交由这3个查询组成的事务:

$mysqli->commit();

但是，如果其中一个查询失败了，所有的3个查询都会被取消吗？还是我必须自己调用回滚？我希望所有的3个查询都是原子性的，并且被视为一个查询。如果一个查询失败，则所有的3个查询都应该失败，并且没有任何影响。

我之所以问这个问题，是因为在手册页面的评论中：http://php.net/manual/en/mysqli.commit.php，用户Lorenzo在一个查询失败时调用了回滚。

如果3个查询都是原子性的，那么回滚有什么用呢？我不明白。

编辑：这是我怀疑的代码示例：

<?php 
$all_query_ok=true; // our control variable 
$mysqli->autocommit(false);
//we make 4 inserts, the last one generates an error 
//if at least one query returns an error we change our control variable 
$mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : $all_query_ok=false; 
$mysqli->query("INSERT INTO myCity (id) VALUES (200)") ? null : $all_query_ok=false; 
$mysqli->query("INSERT INTO myCity (id) VALUES (300)") ? null : $all_query_ok=false; 
$mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : $all_query_ok=false; //duplicated PRIMARY KEY VALUE 

//now let's test our control variable 
$all_query_ok ? $mysqli->commit() : $mysqli->rollback(); 

$mysqli->close(); 
?>

我认为这段代码有问题，因为如果任何查询失败并且$all_query_ok==false，那么您不需要执行回滚，因为事务没有被处理。我说的对吗？

- Ariel

---

如果您将SQL语句放在BEGIN TRANSACTION END块中，则此方法有效。因此，如果事务块中的任何语句失败，则会回滚事务。 - DevZer0

@N.B. 我想我知道什么是自动提交：如果它被打开，那么我调用的每个查询都会立即由数据库处理；如果它被关闭，那么查询只会在调用 $mysqli->commit(); 时发送到数据库。 - Ariel

@eggyal - 很抱歉，我无法看出你的评论如何帮助OP理解事务或有效地使用mysqli。 - N.B.

为什么不将这四个插入操作作为批量插入 INSERT INTO myCity (id) VALUES (100), (200), (300), (100); 进行呢？这样要么所有值都成功，要么所有值都失败。 - eggyal

@N.B.: 没有任何问题...我只是不明白为什么你要用一些奇怪的关于BEGIN WORK的讨论来分散这个有效问题，这完全是不必要的，只会让事情更加混乱。我的前几条评论只是想解释这一点。如果你感到冒犯了，我很抱歉。 - eggyal

Answer 1

我认为这段代码是错误的，因为如果任何一个查询失败且$all_query_ok==false，则不需要回滚，因为事务未被处理。我对吗？

不，如果单个SQL语句失败，则事务不会跟踪它。

如果单个SQL语句失败，则语句将被回滚（如@eggyal的答案所述）- 但事务仍然处于打开状态。 如果现在调用commit，则成功的语句不会回滚，并且您只是向数据库中插入了“损坏”的数据。 您可以轻松地重现此操作：

m> CREATE TABLE transtest (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL DEFAULT '',
 CONSTRAINT UNIQUE KEY `uq_transtest_name` (name)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

m> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

m> INSERT INTO transtest (name) VALUE ('foo');
Query OK, 1 row affected (0.00 sec)

m> INSERT INTO transtest (name) VALUE ('foo');
ERROR 1062 (23000): Duplicate entry 'foo' for key 'uq_transtest_name'

m> INSERT INTO transtest (name) VALUE ('bar');
Query OK, 1 row affected (0.00 sec)

m> COMMIT;
Query OK, 0 rows affected (0.02 sec)

m> SELECT * FROM transtest;
+----+------+
| id | name |
+----+------+
|  3 | bar  |
|  1 | foo  |
+----+------+
2 rows in set (0.00 sec)

你会发现插入'foo'和'bar'是成功的，虽然第二个SQL语句失败了 - 你甚至可以看到 AUTO_INCREMENT 值已经被错误的查询增加了。

所以你必须检查每个query调用的结果，如果有一个失败，就调用rollback撤消其他成功的查询。 所以Lorenzo在PHP手册中的代码是有意义的。

唯一强制MySQL回滚事务的错误是 "事务死锁"（仅针对InnoDB，其他存储引擎可能会以不同的方式处理这些错误）。

- vstm

---

太好了！谢谢！正是我想知道的。所以我猜我必须验证每个语句，并在任何查询出错时执行回滚。 - Ariel

它有效了，谢谢兄弟。@vstm感谢你提出这个问题，它节省了我的时间。#Ariel - Prasad Patel

Answer 2

如 InnoDB 错误处理 所述：

InnoDB的错误处理并不总是与SQL标准中指定的相同。根据标准，任何SQL语句中的错误都应该导致该语句回滚。 InnoDB有时仅回滚语句的一部分，或整个事务。以下内容描述了InnoDB如何执行错误处理：

• 如果在表空间中用尽了文件空间，则会发生MySQL Table is full错误，InnoDB会回滚SQL语句。

• 事务死锁会导致InnoDB回滚整个事务。当这种情况发生时，请重试整个事务。

  锁等待超时会导致InnoDB仅回滚正在等待锁并遇到超时的单个语句。（要使整个事务回滚，请使用{{link5：--innodb_rollback_on_timeout}}选项启动服务器。）如果使用当前行为，请重试该语句，如果使用{{link5：--innodb_rollback_on_timeout}}，请重试整个事务。

  在繁忙的服务器上，死锁和锁等待超时是正常的，应用程序必须知道它们可能会发生，并通过重试来处理它们。可以通过在事务期间进行尽可能少的工作以及在提交时尽可能短地保持锁定并使行数最小来减少它们发生的可能性。有时将工作分为不同的事务可能是实用和有帮助的。

  当由于死锁或锁等待超时而发生事务回滚时，它会取消事务中的语句效果。但是，如果start-transaction语句是{{link7：START TRANSACTION}}或{{link8：BEGIN}}语句，则回滚不会取消该语句。进一步的SQL语句将成为事务的一部分，直到出现{{link9：COMMIT}}、{{link10：ROLLBACK}}或导致隐式提交的某个SQL语句。

• 如果您没有在语句中指定IGNORE选项，则重复键错误会回滚SQL语句。

• row too long error会回滚SQL语句。

• 其他错误大多由MySQL代码层（高于InnoDB存储引擎层）检测到，它们会回滚相应的SQL语句。锁定在单个SQL语句的回滚中不会释放。

- eggyal