問題描述

我已經(jīng)閱讀了在線 php 手冊，但我仍然不確定這兩個函數(shù)的工作方式: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("...");

然后我通過執(zhí)行以下操作提交由這 3 個查詢組成的事務(wù):

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

$mysqli->commit();

但是，在這些查詢之一不起作用的不幸情況下，是所有 3 個查詢都被取消還是我必須自己調(diào)用回滾?我希望所有 3 個查詢都是原子的，并且只被視為一個查詢.如果一個查詢失敗，那么所有 3 個查詢都應(yīng)該失敗并且沒有任何效果.

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 會調(diào)用回滾.

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

我認(rèn)為這段代碼是錯誤的，因為如果任何查詢失敗并且 $all_query_ok==false 那么你不需要回滾，因為事務(wù)沒有被處理.我說得對嗎?

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?

推薦答案

我認(rèn)為這段代碼是錯誤的，因為如果任何查詢失敗并且$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 語句失敗，事務(wù)不會跟蹤.

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

如果單個 SQL 語句失敗，則 語句 將回滾(如@eggyal 的回答中所述) - 但 事務(wù) 仍處于打開狀態(tài).如果您現(xiàn)在調(diào)用 commit，則不會回滾成功的語句，您只是將損壞的"數(shù)據(jù)插入到數(shù)據(jù)庫中.您可以輕松重現(xiàn)這一點:

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 調(diào)用的結(jié)果，如果失敗，則調(diào)用 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.

強(qiáng)制 MySQL 回滾事務(wù)的唯一錯誤是事務(wù)死鎖"(這是特定于 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).

這篇關(guān)于如何 mysqli::commit &mysqli::回滾工作?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！