mysqli::rollback

mysqli_rollback

(PHP 5, PHP 7, PHP 8)

mysqli::rollback -- mysqli_rollback — Revierte la transacción actual

Descripción

Estilo orientado a objetos

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

Estilo por procedimientos

mysqli_rollback(mysqli $link, int $flags = ?, string $name = ?): bool

Revierte la transacción actual de la base de datos.

Parámetros

link: Sólo estilo por procediminetos: Un identificador de enlace devuelto por mysqli_connect() o mysqli_init()
flags: Una máscara de bits de constantes MYSQLI_TRANS_COR_*.
name: Si se proporciona, se ejecuta ROLLBACK/*name*/.

Valores devueltos

Devuelve true en caso de éxito o false en caso de error.

Historial de cambios

Versión	Descripción
5.5.0	Se añadieron los parámetros `flags` y `name`.

Ejemplos

Ejemplo #1 Ejemplo de mysqli::rollback()

Estilo orientado a objetos

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* verificar la conexión */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* deshabilitar autocommit */
$mysqli->autocommit(FALSE);

$mysqli->query("CREATE TABLE myCity LIKE City");
$mysqli->query("ALTER TABLE myCity Type=InnoDB");
$mysqli->query("INSERT INTO myCity SELECT * FROM City LIMIT 50");

/* insertar commit */
$mysqli->commit();

/* borrar todas las filas */
$mysqli->query("DELETE FROM myCity");

if ($result = $mysqli->query("SELECT COUNT(*) FROM myCity")) {
    $row = $result->fetch_row();
    printf("%d rows in table myCity.\n", $row[0]);
    /* Liberar resultado */
    $result->close();
}

/* Revertir */
$mysqli->rollback();

if ($result = $mysqli->query("SELECT COUNT(*) FROM myCity")) {
    $row = $result->fetch_row();
    printf("%d rows in table myCity (after rollback).\n", $row[0]);
    /* Liberar resultado */
    $result->close();
}

/* Eliminar la tabla myCity */
$mysqli->query("DROP TABLE myCity");

$mysqli->close();
?>

Estilo por procedimientos

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* verificar la conexión */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* deshabilitar autocommit */
mysqli_autocommit($link, FALSE);

mysqli_query($link, "CREATE TABLE myCity LIKE City");
mysqli_query($link, "ALTER TABLE myCity Type=InnoDB");
mysqli_query($link, "INSERT INTO myCity SELECT * FROM City LIMIT 50");

/* insertar commit */
mysqli_commit($link);

/* borrar todas las filas */
mysqli_query($link, "DELETE FROM myCity");

if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
    $row = mysqli_fetch_row($result);
    printf("%d rows in table myCity.\n", $row[0]);
    /* Liberar resultado */
    mysqli_free_result($result);
}

/* Revertir */
mysqli_rollback($link);

if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
    $row = mysqli_fetch_row($result);
    printf("%d rows in table myCity (after rollback).\n", $row[0]);
    /* Liberar resultado */
    mysqli_free_result($result);
}

/* Eliminar la tabla myCity */
mysqli_query($link, "DROP TABLE myCity");

mysqli_close($link);
?>

El resultado de los ejemplos sería:

0 rows in table myCity.
50 rows in table myCity (after rollback).

Ver también

mysqli_begin_transaction() - Inicia una transacción
mysqli_commit() - Consigna la transacción actual
mysqli_autocommit() - Activa o desactiva las modificaciones de la base de datos autoconsignadas
mysqli_release_savepoint() - Elimina el punto salvado con nombre del conjunto de puntos salvados de la transacción actual

Improve This Page

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 4 notes

down

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

down

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!

down

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`");

down

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.

＋add a note