How Do I Start and End Transactions in MySQLi?

Start and End Transaction in MySQLi

When using MySQL, understanding how transactions work is essential. Transactions serve as a way to group multiple queries and ensure they are executed atomically, meaning either all or none of the queries succeed.

Starting a Transaction

In MySQLi, a transaction is initiated by deactivating the autocommit mode using the autocommit(FALSE) statement. Autocommit is the default behavior of MySQL, which means every query is executed independently and immediately. By disabling autocommit, you enable transaction control, allowing you to group multiple queries and execute them as a single unit.

Ending a Transaction

After grouping the desired queries within the transaction, you must explicitly end it. There are two ways to end a transaction:

1. Commit: If all the queries within the transaction executed successfully without errors, you can complete the transaction using the commit() method. This will save all the changes made during the transaction permanently to the database.
2. Rollback: If any of the queries within the transaction failed, you can cancel the transaction using the rollback() method. This will discard all changes made during the transaction, reverting the database to its state before the transaction began.

Example

Consider the following PHP code snippet:

<?php
// Database connection
$mysqli = new mysqli('localhost', 'username', 'password', 'database');

// Start transaction
$mysqli->autocommit(FALSE);

// Queries within the transaction
$mysqli->query('UPDATE `table` SET `column` = 1');
$mysqli->query('UPDATE `table1` SET `column1` = 2');

// Commit or rollback
if ($allQueriesExecutedSuccessfully) {
  $mysqli->commit();
} else {
  $mysqli->rollback();
}
?>

In this example, the autocommit mode is disabled using autocommit(FALSE), initiating a transaction. Multiple queries are executed within the transaction. Based on the execution status, the transaction is either committed or rolled back.

The above is the detailed content of How Do I Start and End Transactions in MySQLi?. For more information, please follow other related articles on the PHP Chinese website!