mysqli :: commit& mysqli :: rollback工作? [英] How does mysqli::commit & mysqli::rollback work?

问题描述

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

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个查询都是原子查询，并且仅被视为一个查询.如果一个查询失败，则所有三个查询都将失败，并且无效.

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)

尽管第二个SQL语句失败，但是您看到'foo'和'bar'的插入是成功的-您甚至可以看到由于错误的查询，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撤消否则成功的查询.因此，Lorenzo在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 :: rollback工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！