How to Manage MySQL Database Transactions with PHP's mysqli Extension?

Querying MySQL Database with Transactions

Using Transactions with mysqli

Transactions are a crucial aspect of database management, ensuring that a sequence of database operations is either fully executed or rolled back if any error occurs. With MySQL's mysqli extension for PHP, transactions are handled through two essential functions:

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

Starting a Transaction

To initiate a transaction, you need to call $mysqli->autocommit(FALSE);. This disables autocommit, allowing you to execute multiple queries within the same transaction.

Executing Queries Within a Transaction

Once a transaction is started, you can execute any number of queries inside it. However, these queries will not be permanently applied to the database until you explicitly commit the transaction.

Committing a Transaction

The $mysqli->commit(); function finalizes the transaction and applies all the changes made during that transaction to the database. If there are no errors, the transaction is successful, and the changes become permanent.

Rolling Back a Transaction

If an error occurs during a transaction, or if you need to revert the changes made, you can call mysqli->rollback(); instead of mysqli->commit();. This will discard all the changes made within that transaction, and the database will return to its previous state.

Example

The following code snippet demonstrates how to start and end a transaction in mysqli:

$mysqli->autocommit(FALSE);

$mysqli->query("UPDATE `table` SET `col` = 2");
$mysqli->query("UPDATE `table1` SET `col1` = 3");

$mysqli->commit(); // End transaction

In this example, the two query statements are executed within the same transaction. However, no changes are applied to the database until the transaction is committed. If any error occurs before $mysqli->commit(); is called, the changes can be rolled back by calling $mysqli->rollback();.

The above is the detailed content of How to Manage MySQL Database Transactions with PHP's mysqli Extension?. For more information, please follow other related articles on the PHP Chinese website!