ConFoo 2025

mysqli::rollback

mysqli_rollback

(PHP 5, PHP 7, PHP 8)

mysqli::rollback -- mysqli_rollbackAnnule la transaction courante

Description

Style orienté objet

public mysqli::rollback(int $flags = 0, ?string $name = null): bool

Style procédural

mysqli_rollback(mysqli $mysql, int $flags = 0, ?string $name = null): bool

Annule la transaction courante pour la base de données.

Liste de paramètres

mysql

Seulement en style procédural : Un objet mysqli retourné par la fonction mysqli_connect() ou mysqli_init().

flags

Un masque de constantes MYSQLI_TRANS_COR_*.

name

Si fourni, alors ROLLBACK/*name*/ est exécuté.

Valeurs de retour

Cette fonction retourne true en cas de succès ou false si une erreur survient.

Erreurs / Exceptions

Si le rapport d'erreurs mysqli est activé (MYSQLI_REPORT_ERROR) et que l'opération demandée échoue, un avertissement est généré. Si, en plus, le mode est défini sur MYSQLI_REPORT_STRICT, une mysqli_sql_exception est lancée à la place.

Historique

Version Description
8.0.0 name est désormais nullable.

Exemples

Voir l'exemple se trouvant dans la documentation de la méthode mysqli::begin_transaction().

Notes

Note:

Cette fonction ne fonctionne pas avec les types de table non transactionnelle (comme MyISAM ou ISAM).

Voir aussi

add a note

User Contributed Notes 4 notes

up
44
Steven McCoy
12 years ago
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
up
34
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!
up
3
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`");
up
15
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.
To Top