How to start and end transaction in mysqli?

In MySQLi, transaction processing is a crucial feature that ensures data integrity and consistency. Transactions involve grouping multiple steps into a single unit of work. If any step fails, the entire transaction rolls back, and all previous operations have no effect on the database. Conversely, if all steps succeed, all modifications are saved to the database in a single operation.

Starting Transactions

In MySQLi, starting a transaction is typically done by calling the autocommit method and setting its parameter to false. This prevents MySQL from automatically committing SQL operations, which is essential for transaction processing.

php
// Create a mysqli object
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');

// Check if the connection is successful
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// Disable automatic commit
$mysqli->autocommit(FALSE);

Executing Operations Within a Transaction

Within a transaction, you can execute multiple database operations, such as INSERT, UPDATE, or DELETE.

php
try {
    // Execute database operations
    $mysqli->query("INSERT INTO Account (user, amount) VALUES ('User1', 100)");
    $mysqli->query("UPDATE Account SET amount = amount - 100 WHERE user = 'User2'");

    // Commit the transaction if no exceptions occur
    $mysqli->commit();
} catch (Exception $e) {
    // Roll back all operations if an error occurs
    $mysqli->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

In the above code, the commit() method saves all changes made to the database since the transaction began. If any command fails, the exception handling mechanism catches it, and all changes are rolled back using the rollback() method to restore the database to its state before the transaction began.

Ending Transactions

After ending transaction processing, you should re-enable MySQL's automatic commit feature by setting the autocommit method back to true.

php
// Re-enable automatic commit
$mysqli->autocommit(TRUE);

This completes the transaction processing. Ensure that the database connection is closed when the script ends:

php
// Close the connection
$mysqli->close();

By following these steps, you can effectively manage transactions in MySQLi, ensuring atomicity and consistency of data operations.