MySQLi rollback Method

PHP

MySQLi rollback - Rollback Transaction

In this tutorial, you will learn how to use the MySQLi rollback method in PHP to undo changes made during a transaction. This is an essential technique for managing database transactions, ensuring data integrity, and handling errors effectively when working with MySQL databases using MySQLi.

Introduction to MySQLi rollback Method

The rollback() method in the MySQLi extension allows you to undo all queries executed since the last commit() or since the transaction started. This is particularly useful when you want to ensure that your database remains consistent if an error occurs during a multi-step transaction.

Rollback reverses changes made in the current transaction, preventing partial or corrupt data updates.

Prerequisites

  • Basic understanding of PHP and MySQL.
  • MySQLi extension installed and enabled.
  • MySQL database server access.
  • Basic knowledge of transactions in databases.
  • A development environment with PHP and MySQL (e.g., XAMPP, WAMP, LAMP).

Setup Steps

  1. Ensure your MySQL table’s storage engine supports transactions (InnoDB recommended).
  2. Connect to the database using MySQLi in PHP.
  3. Disable autocommit mode using autocommit(FALSE) to start a transaction.
  4. Perform your SQL queries.
  5. Use commit() to save changes or rollback() to undo changes if an error occurs.

How to Use MySQLi rollback() Method: Explained Examples

Example 1: Basic rollback in a transaction

<?php
// Step 1: Connect to the database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Step 2: Disable autocommit mode (start transaction)
$mysqli->autocommit(FALSE);

try {
    // Step 3: Execute first query
    $mysqli->query("INSERT INTO accounts (user, balance) VALUES ('Alice', 1000)");

    // Step 4: Execute second query with a deliberate error for demo
    $mysqli->query("INSERT INTO accounts (user, balance) VALUES ('Bob', 'invalid_balance')");

    // Step 5: If all queries succeed, commit changes
    $mysqli->commit();
    echo "Transaction committed successfully.";
} catch (Exception $e) {
    // Step 6: On error, rollback transaction
    $mysqli->rollback();
    echo "Transaction rolled back due to error: " . $e->getMessage();
}

// Step 7: Re-enable autocommit (optional)
$mysqli->autocommit(TRUE);

// Close connection
$mysqli->close();
?>

Explanation: In this example, the transaction is manually controlled by disabling autocommit. If an error occurs during any query, rollback() is called to undo all changes. Otherwise, changes are committed.

Example 2: Rollback with error handling and prepared statements

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$mysqli->autocommit(FALSE);

$stmt = $mysqli->prepare("UPDATE accounts SET balance = balance - ? WHERE user = ?");
if (!$stmt) {
    die("Prepare failed: " . $mysqli->error);
}

try {
    // Deduct from Alice's account
    $amount = 100;
    $user = "Alice";
    $stmt->bind_param("is", $amount, $user);
    if (!$stmt->execute()) {
        throw new Exception("Failed to update Alice's account");
    }

    // Add to Bob's account
    $amount = -100;  // Add (negative deduction)
    $user = "Bob";
    $stmt->bind_param("is", $amount, $user);
    if (!$stmt->execute()) {
        throw new Exception("Failed to update Bob's account");
    }

    $mysqli->commit();
    echo "Transaction completed successfully.";
} catch (Exception $e) {
    $mysqli->rollback();
    echo "Transaction rolled back: " . $e->getMessage();
}

$stmt->close();
$mysqli->autocommit(TRUE);
$mysqli->close();
?>

This example shows how rollback can be combined with prepared statements and proper error handling to safely manage account balances.

Best Practices When Using MySQLi rollback()

  • Always disable autocommit before starting a transaction using autocommit(FALSE).
  • Use try-catch blocks or error handling to detect and manage query failures.
  • Call rollback() immediately when an error is detected to revert partial changes.
  • Use InnoDB storage engine as MyISAM does not support transactions.
  • Close statements and connections to free resources after completing operations.
  • Keep transactions short and efficient to reduce locking and improve performance.

Common Mistakes to Avoid

  • Not disabling autocommit — queries get committed immediately, making rollback ineffective.
  • Mixing transactional and non-transactional tables (like MyISAM with InnoDB) — rollback won’t work on non-transactional tables.
  • Failing to check for errors before committing the transaction.
  • Forgetting to call autocommit(TRUE) after the transaction, affecting subsequent queries.
  • Using rollback outside of a transaction context (no effect).

Interview Questions

Junior Level Questions

  • Q1: What is the purpose of the MySQLi rollback() method?
    A: It undoes all changes made in the current transaction since the last commit.
  • Q2: How do you start a transaction in MySQLi before using rollback?
    A: By disabling autocommit with $mysqli->autocommit(FALSE);.
  • Q3: Which storage engine supports transactions and rollback?
    A: InnoDB supports transactions and rollback.
  • Q4: What happens if you call rollback() when no transaction is active?
    A: It has no effect because there is nothing to rollback.
  • Q5: How do you commit changes in MySQLi after a successful transaction?
    A: By calling $mysqli->commit();.

Mid Level Questions

  • Q1: Explain why it is important to use rollback in transactions.
    A: To maintain data integrity by undoing partial changes if an error occurs.
  • Q2: How do you handle errors in MySQLi transactions to decide if rollback is needed?
    A: Use try-catch blocks or check query success, and call rollback if any query fails.
  • Q3: Can you rollback changes on MyISAM tables? Why or why not?
    A: No, because MyISAM does not support transactions.
  • Q4: After rollback, what should you do with autocommit mode?
    A: Re-enable autocommit with $mysqli->autocommit(TRUE);.
  • Q5: What are the consequences of leaving autocommit disabled by mistake?
    A: Subsequent queries will be part of the transaction and may remain uncommitted, causing locking and inconsistent state.

Senior Level Questions

  • Q1: How would you implement robust transaction management using MySQLi rollback and commit methods across multiple queries?
    A: Disable autocommit, execute queries inside try-catch, commit if all succeed, rollback on any failure, then re-enable autocommit.
  • Q2: How do you combine prepared statements with rollback for secure and reliable database transactions?
    A: Use prepared statements for all queries inside a transaction; check execution success; rollback if any statement fails.
  • Q3: What strategies can help minimize deadlocks when using MySQLi transactions with rollback?
    A: Keep transactions short, access tables in consistent order, index keys properly, and handle errors with rollback and retry logic.
  • Q4: Explain common pitfalls in multi-connection transaction handling with rollback in MySQLi.
    A: Rollback affects only the current connection’s transaction; mixing connections can lead to inconsistent states if not managed carefully.
  • Q5: How can you detect and handle deadlocks or transaction conflicts while using MySQLi rollback?
    A: Catch MySQL deadlock errors, perform rollback, and implement retry logic with exponential backoff for the transaction.

Frequently Asked Questions (FAQ)

What is the difference between commit() and rollback()?

commit() saves all changes made in the current transaction. rollback() undoes those changes.

Can I use rollback without disabling autocommit?

No. You must disable autocommit (autocommit(FALSE)) to start a transaction; otherwise, queries are committed immediately, and rollback has no effect.

Does rollback work with MyISAM tables?

No. MyISAM does not support transactions, so rollback cannot undo changes on MyISAM tables.

How to check if rollback was successful?

rollback() returns TRUE on success, but generally you check for query errors beforehand and rely on that to decide rollback.

Is it necessary to re-enable autocommit after rollback?

While not mandatory, it is best practice to re-enable autocommit after transaction completion to restore normal query behavior.

Conclusion

The MySQLi rollback() method is a powerful tool for managing transactions and maintaining database integrity. By understanding how to control transactions with autocommit, use rollback to undo changes on errors, and commit to save successful operations, you can build more reliable PHP applications that interact with MySQL databases. Implementing robust error handling and following best practices will ensure your data remains consistent even in unexpected failure situations.