rollback

Introduction

MySQLi is the PHP extension used to talk to MySQL databases. The mysqli_rollback() function (or the $mysqli->rollback() method in object style) undoes every change made since a transaction began. This page explains when and why you need it, how it fits alongside mysqli_commit(), and shows runnable examples in both procedural and object-oriented style.

If you are new to the extension, start with PHP MySQLi and connecting to MySQL.

What is the MySQLi Rollback function?

A transaction is a group of database operations treated as one unit of work: either all of them are applied, or none are. mysqli_rollback() is what makes "none of them" possible — it discards every change made since the transaction started and returns the database to its previous state.

The classic example is a money transfer. Subtracting from one account and adding to another must both happen; if the second query fails, you cannot leave the first one applied. Rolling back guarantees the books stay balanced.

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

It returns true on success and false on failure. The optional $name lets you roll back to a named savepoint instead of the whole transaction.

Autocommit: why you need transactions at all

By default MySQL runs in autocommit mode, meaning every individual statement is committed immediately and permanently — there is nothing to roll back. Calling mysqli_begin_transaction() turns autocommit off for the duration of the transaction, so your changes stay pending until you explicitly commit() or rollback(). See autocommit for the full behaviour.

How the rollback workflow works

A complete transaction follows four steps:

1. Connect to the server.
2. Begin a transaction with mysqli_begin_transaction().
3. Run your queries.
4. Commit if everything succeeded, or roll back if anything failed.

Here is a full procedural example:

<?php
// Create a connection to the MySQL server
$conn = mysqli_connect("localhost", "username", "password", "database");

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Start a transaction
mysqli_begin_transaction($conn);

// Execute queries and make changes to the database
$insert = mysqli_query($conn, "INSERT INTO `my_table` (column1, column2) VALUES ('value1', 'value2')");
$update = mysqli_query($conn, "UPDATE `my_table` SET column1 = 'new_value' WHERE id = 1");

// Check if queries succeeded
if ($insert && $update) {
    // Commit the transaction if all queries succeed
    mysqli_commit($conn);
    echo "Transaction committed successfully.";
} else {
    // Rollback the changes if any query fails
    mysqli_rollback($conn);
    echo "Transaction failed and rolled back: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

A transaction is started, two queries run, and the result decides the outcome: commit on success, roll back on failure. Note that mysqli_query() only returns false when a query fails at the SQL level — it does not throw, so you must check the return value yourself unless you enable exceptions (shown below).

Object-oriented style with prepared statements

Most production code uses the object-oriented API together with prepared statements, which prevent SQL injection by separating the query from its data. Turning on the exception report mode lets you wrap the whole transaction in a single try/catch and roll back from one place:

<?php
// Throw exceptions on any MySQLi error instead of returning false
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$mysqli = new mysqli("localhost", "username", "password", "database");

$mysqli->begin_transaction();

try {
    $stmt = $mysqli->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt->bind_param("di", $amount, $fromId);
    $amount = 100.00;
    $fromId = 1;
    $stmt->execute();

    $stmt = $mysqli->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
    $stmt->bind_param("di", $amount, $toId);
    $toId = 2;
    $stmt->execute();

    // Both updates succeeded — make them permanent
    $mysqli->commit();
    echo "Transfer committed.";
} catch (mysqli_sql_exception $e) {
    // Anything threw — undo everything
    $mysqli->rollback();
    echo "Transfer failed and rolled back: " . $e->getMessage();
}

$mysqli->close();
?>

Because MYSQLI_REPORT_STRICT makes failed statements throw mysqli_sql_exception, you never have to test each query's return value — the catch block handles every error path. See PHP exceptions for more on try/catch.

Rolling back to a savepoint

A savepoint is a named marker inside a transaction. Rolling back to it undoes only the work done after the savepoint, leaving earlier changes pending. This is handy when part of a transaction is optional:

<?php
$mysqli->begin_transaction();

$mysqli->query("INSERT INTO orders (customer_id) VALUES (5)");
$mysqli->savepoint("after_order");

$mysqli->query("INSERT INTO order_items (order_id, sku) VALUES (LAST_INSERT_ID(), 'BAD')");

// Undo only the order_items insert; the order row remains pending
$mysqli->rollback(0, "after_order");

$mysqli->commit(); // commits the order without the bad item
?>

Use Cases for the MySQLi Rollback Function

The MySQLi Rollback function is useful for a variety of scenarios, including:

1. Data Integrity

Maintains database consistency by reverting partial updates when an operation fails, ensuring records remain valid.

2. Error Handling

Provides a clean recovery mechanism. When a failure occurs, rolling back prevents orphaned or corrupted data while allowing the application to log and display meaningful error messages.

3. Transaction Management

Simplifies workflow control by allowing developers to abort a sequence of dependent operations and safely restart or abandon the transaction without manual cleanup.

Advantages of the MySQLi Rollback Function

The MySQLi Rollback function offers several technical benefits for PHP developers:

1. Atomicity

It guarantees that a group of database operations either all succeed or all fail together, preventing partial updates that could corrupt data.

2. Simplified Debugging

When a transaction fails, rolling back restores the database to its previous state, making it easier to isolate and fix the problematic query without manual cleanup.

3. Performance Optimization

Grouping multiple queries into a single transaction reduces the overhead of committing changes individually, leading to faster database operations.

Common gotchas

• Storage engine matters. Only transactional engines support rollback. MySQL's default InnoDB does; the older MyISAM engine silently ignores transactions, so a rollback there changes nothing. Make sure your tables are InnoDB.
• DDL statements auto-commit. Statements like CREATE TABLE, ALTER TABLE, and DROP TABLE implicitly commit the current transaction in MySQL — you cannot roll them back. Keep schema changes out of transactions that you expect to undo.
• Roll back exactly once. After a commit() or a full rollback() the transaction is over. Begin a new one before running more transactional work.
• Connection drops auto-roll-back. If the connection is lost mid-transaction, MySQL rolls back automatically — uncommitted work is never left half-applied.

Conclusion

The mysqli_rollback() function is essential for PHP developers who need to revert database changes during a transaction. It ensures data integrity, simplifies transaction management, and improves error handling. By following the steps in this guide, developers can safely implement rollbacks to maintain reliable database operations.

Practice