Use the new PHP plug-in to implement MySQL-based transactions

Welcome to the Linux community forum, and interact with 2 million technical staff. Transaction processing support has been the wish of most MySQL developers for a long time. With the release of MySQL4.0, this wish was finally realized. Shortly after MySQL4.0, PHP5.x with a new MySQL plug-in was also released. This new plug-in, MySQLImproved

Welcome to the Linux community forum and interact with 2 million technical staff> transaction processing support has been the wish of most MySQL developers for a long time. With the release of MySQL 4.0, this wish was finally realized. Shortly after MySQL 4.0, PHP 5.x with a new MySQL plug-in was also released. This new plug-in, MySQL Improved

Welcome to the Linux community forum and interact with 2 million technicians>

Transaction processing support has been the wish of most MySQL developers for a long time. With the release of MySQL 4.0, this wish was finally realized. Shortly after MySQL 4.0, PHP 5.x with a new MySQL plug-in was also released. This new plug-in, MySQL Improved, enables PHP developers to use local PHP functions to obtain these new transaction processing capabilities. This short tutorial will show you how to use these new MySQLi functions to implement MySQL-based transactions using PHP.

Summary

If you do not know, I can tell you that transactions are just a set of SQL statements, usually because they are mutually dependent, therefore, it must be executed in all-or-nothing mode. A transaction is successful only when all the statements are successfully executed. Failure in any statement should cause the system to roll back to its previous state, to avoid data connection/crash issues.

The transfer between two bank accounts is a good example. At the database level, such transfer involves two steps: first, deduct the transfer amount from the source account and add it to the target account. If an error occurs in step 2, the first step must be canceled to avoid inconsistencies (with angry customers ). The transaction security system automatically removes the snapshot from the system ".

Most databases (including MySQL) use a combination of commands to accomplish this:

* The start transaction command marks the START of a new TRANSACTION group. It is followed by a series of SQL commands.

* The COMMIT command indicates the end of a transaction group, indicating that all changes made during the transaction should be committed or made permanent.

* The ROLLBACK command indicates the end of a transaction group, indicating that all changes made during the transaction should be undone.

Transaction processing functions in PHP

The MySQLi plug-in PHP introduces new functions to help developers use MySQL's transaction processing capabilities. Essentially, these functions are called SQL START TRANSACTION, COMMIT, and ROLLBACK commands. List A shows you an example:

List

　　

// Connect to database

$ Dbh = mysqli_connect ($ host, $ user, $ pass, $ db );

// Turn off auto-commit

Mysqli_autocommit ($ dbh, FALSE );

// Run query 1

$ Result = mysqli_query ($ dbh, $ query1 );

If ($ result! = TRUE ){

Mysqli_rollback ($ dbh); // if error, roll back transaction

}

// Run query 2

$ Result = mysqli_query ($ dbh, $ query2 );

If ($ result! = TRUE ){

Mysqli_rollback ($ dbh); // if error, roll back transaction

}

// And so on...

// Assuming no errors, commit transaction

Mysqli_commit ($ dbh );

// Close connection

Mysqli_close ($ dbh );

?>

There are three basic steps for executing a transaction in PHP:

* The first step is to always turn off the "auto-commit" of the database, which essentially means that the system will save them when you make changes. This is very important, because in a transaction processing environment, you should be only after you have determined that all the transaction processing units have been completed successfully, save your changes. You can use the mysqli_autocommit () function to disable automatic database submission.

* Next, use the mysqli_query () function to continue to perform INSERT, UPDATE, and/or DELETE queries using the common method. It is important to check the value returned by each query to determine whether the query is successful. If any query fails, the mysqli_rollback () function is used to return the system to the status before the transaction is executed.

* If all the commands in the transaction group are successfully executed, you must use the mysqli_commit () function to save the changes to the database system. Please note that once this function is called, the transaction cannot be undone.

[1] [2] [3]