Remember that MyISAM tables do not support rollbacks.
I just drove myself crazy for an afternoon trying to figure out what was wrong with my code - meanwhile it was fine all along
mysqli::rollback
mysqli_rollback
(PHP 5, PHP 7, PHP 8)
mysqli::rollback -- mysqli_rollback — 回滚当前事务
说明
面向对象风格
过程化风格
回滚数据库的当前事务。
参数
-
mysql -
仅以过程化样式：由 mysqli_connect() 或 mysqli_init() 返回的 mysqli 对象。
-
flags -
MYSQLI_TRANS_COR_*常量的位掩码。 -
name -
如果提供，则执行
ROLLBACK/*name*/。
返回值
成功时返回 true， 或者在失败时返回 false。
错误／异常
If mysqli error reporting is enabled (MYSQLI_REPORT_ERROR) and the requested operation fails,
a warning is generated. If, in addition, the mode is set to MYSQLI_REPORT_STRICT,
a mysqli_sql_exception is thrown instead.
更新日志
| 版本 | 说明 |
|---|---|
| 8.0.0 |
name 现在可以为 null。
|
注释
注意:
此函数不支持非事务表类型（如 MyISAM 或 ISAM）。
参见
- mysqli_begin_transaction() - Starts a transaction
- mysqli_commit() - 提交当前事务
- mysqli_autocommit() - 打开或关闭本次数据库连接的自动命令提交事务模式
- mysqli_release_savepoint() - 从当前事务的保存点中移除一个命名保存点
add a note
User Contributed Notes 5 notes
Steven McCoy ¶
12 years ago
Lorenzo - webmaster AT 4tour DOT it ¶
15 years ago
This is an example to explain the powerful of the rollback and commit functions.
Let's suppose you want to be sure that all queries have to be executed without errors before writing data on the database.
Here's the code:
<?php
$all_query_ok=true; // our control variable
//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();
?>
hope to be helpful!
xcalibur at xcalibur dot dk ¶
14 years ago
Just a note about auto incremental ids and rollback.
When using transactions and inserting into a table containing a column with auto incremental ids, the id will be incremented even though the transaction is rolled back.
This might occupy a lot of ids if a lot of rollbacks are performed.
Example:
<?php
$mysqli = new mysqli("localhost", "gugbageri", "gugbageri", "gugbageri");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* disable autocommit */
$mysqli->autocommit(FALSE);
/* We just create a test table with one auto incremental primary column and a content column*/
$mysqli->query("CREATE TABLE TestTable ( `id_column` INT NOT NULL AUTO_INCREMENT , `content` INT NOT NULL , PRIMARY KEY ( `id_column` )) ENGINE = InnoDB;");
/* commit newly created table */
$mysqli->commit();
/* we insert a row */
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
/* we commit the inserted row */
$mysqli->commit();
/* we insert another three rows */
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
/* we the rollback */
$mysqli->rollback();
/* we insert a row */
$mysqli->query("INSERT INTO TestTable (content) VALUES (99)");
/* we commit the inserted row */
$mysqli->commit();
if ($result = $mysqli->query("SELECT id_column FROM TestTable")) {
while($row = $result->fetch_row()) {
printf("Id: %d.\n", $row[0]);
}
/* Free result */
$result->close();
}
/* Drop table TestTable */
$mysqli->query("DROP TABLE TestTable");
$mysqli->close();
?>
This will output:
Id: 1.
Id: 5.
Yorick Phoenix ¶
1 year ago
If you use savepoints - eg savepoint($foo) - be wary of trying to rollback to the save point with rollback(0, $foo) as that executes "ROLLBACK /* $foo */" instead of "ROLLBACK TO `$foo`".
The manual page is clear about this, but is easily overlooked.
Instead use: $mysqli->query("ROLLBACK TO `$foo`");
jd at dilltree dot com ¶
15 years ago
Something to consider when using transact is that you should not perform a normal query on the same table (such as a DELETE) immediately after a transaction. If the transaction rolls-back, the DELETE will execute and even show affected rows, but the row can be magically re-inserted even if the rollback() command comes before the DELETE query.
