如何 mysqli::commit &mysqli::回滚工作?

问题描述

我已经阅读了在线 php 手册，但我仍然不确定这两个函数的工作方式:mysqli::commit &mysqli::回滚.

I've read the online php manual but I'm still not sure of the way these two functions work: mysqli::commit & mysqli::rollback.

我要做的第一件事是:

$mysqli->autocommit(FALSE);

然后我提出一些疑问:

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

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

Then I commit the transaction consisting of these 3 queries by doing:

$mysqli->commit();

但是，在这些查询之一不起作用的不幸情况下，是所有 3 个查询都被取消还是我必须自己调用回滚?我希望所有 3 个查询都是原子的，并且只被视为一个查询.如果一个查询失败，那么所有 3 个查询都应该失败并且没有任何效果.

BUT in the unfortunate case in which one of these queries does not work, do all 3 queries get cancelled or do I have to call a rollback myself? I want all 3 queries to be atomic and be considered as only one query. If one query fails then all 3 should fail and have no effect.

我问这个是因为在我在手册页上看到的评论中:http://php.net/manual/en/mysqli.commit.php如果其中一个查询失败，用户 Lorenzo 会调用回滚.

I'm asking this because in the comments I've seen on the manual page: http://php.net/manual/en/mysqli.commit.php the user Lorenzo calls a rollback if one of the queries failed.

如果 3 个查询是原子的，回滚有什么好处?我不明白.

What's a rollback good for if the 3 queries are atomic? I don't understand.

这是我怀疑的代码示例:

This is the code example I am doubtful about:

<?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 那么你不需要回滚，因为事务没有被处理.我说得对吗?

I think this code is wrong because if any of the queries failed and $all_query_ok==false then you don't need to do a rollback because the transaction was not processed. Am I right?

推荐答案

我认为这段代码是错误的，因为如果任何查询失败并且$all_query_ok==false 那么你不需要回滚，因为交易未处理.我说得对吗?

I think this code is wrong because if any of the queries failed and $all_query_ok==false then you don't need to do a rollback because the transaction was not processed. Am I right?

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

No, the transaction does not keep track if a single SQL-Statement fails.

如果单个 SQL 语句失败，则 语句 将回滚(如@eggyal 的回答中所述) - 但 事务 仍处于打开状态.如果您现在调用 commit，则不会回滚成功的语句，您只是将损坏的"数据插入到数据库中.您可以轻松重现这一点:

If a single SQL-Statement fails the statement is rolled back (like it is described in @eggyal's Answer) - but the transaction is still open. If you call commit now, there is no rollback of the successful statements and you just inserted "corrupted" data into your database. You can reproduce this easily:

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 值已被错误查询增加.

You see that the insertion of 'foo' and 'bar' were successful although the second SQL-statement failed - you can even see that the AUTO_INCREMENT-value has been increased by the faulty query.

因此您必须检查每个 query 调用的结果，如果失败，则调用 rollback 以撤消否则成功的查询.所以洛伦佐在 PHP 手册中的代码是有道理的.

So you have to check the results of each query-call and if one fails, call rollback to undo the otherwise successful queries. So Lorenzo's code in the PHP-manual makes sense.

强制 MySQL 回滚事务的唯一错误是事务死锁"(这是特定于 InnoDB 的，其他存储引擎可能会以不同的方式处理这些错误).

The only error which forces MySQL to roll back the transaction is a "transaction deadlock" (and this is specific to InnoDB, other storage engines may handle those errors differently).

这篇关于如何 mysqli::commit &mysqli::回滚工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！