Home >Backend Development >PHP Tutorial >Implement MySQL-based transactions with new PHP plug-in_PHP tutorial
Implement MySQL-based transactions with new PHP plug-in_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:35:13818browse
事务处理支持很长时间以来一直是大多数MySQL开发者的心愿，随着MySQL 4.0的发布，这个心愿最后终于得以实现。MySQL 4.0后不久，拥有一个新的MySQL插件的PHP 5.x也发布了。这个新插件，MySQL Improved，使得PHP开发者通过利用本地的PHP函数，获得了这些新的事务处理能力。这篇简短的教程将向你说明怎样利用这些新的MySQLi函数，用PHP实现以MySQL为基础的事务。 概要 如果你还不知道，那么我可以告诉你，事务只是一组SQL语句，通常因为它们是彼此相互依赖的，所以要在全有或全无(all-or-nothing)的模式下执行。只有当所有组成的语句都执行成功了，一个事务才算是成功了；任何一个语句中的失败应该都会导致系统“回滚”到它先前的状态，以避免数据连接／崩溃问题。 对于这一点，两个银行帐户间的转帐是一个很好的例子。在数据库级，这样的转帐包括两个步骤：首先，从源帐户中扣除转帐的金额，然后将其加到目标帐户中。如果在第二步中发生了错误，那么第一步就必须被取消，以避免不相符的情况(和愤怒的客户聚众滋事)。事务安全系统将自动地撤到系统先前的“快照”。 大多数数据库(包括MySQL)通过一个命令的组合来完成这个： * START TRANSACTION命令标志着一个新的事务组的开始。它后面常接一系列的SQL命令。 * COMMIT命令标志着一个事务组的结束，表示事务期间做的所有改变应该被提交或者使之永久化。 * ROLLBACK命令标志着一个事务组的结束，表示事务期间所做的所有改变应该被撤消。 PHP中的事务处理函数 PHP中的MySQLi插件引进了新的函数，帮助开发者利用MySQL的事务处理能力。实质上，这些函数对等地被叫做SQL START TRANSACTION，COMMIT和 ROLLBACK命令。列表A为你展示了一个例子,列表A: // 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); ?> 在PHP 中执行一项事务有三个基本的步骤： * 第一步是始终关掉数据库的“auto-commit”，它实质上意味着系统在你作出改变时就保存它们。这一点是很重要的，因为在一个事务处理环境中，你应该只有在确定了所有事务处理的“unit”都成功完成了以后，才保存你所做的改变。你可以通过mysqli_autocommit()函数关掉数据库的自动提交。 * 接下来，通过mysqli_query()函数，继续用通常的方法进行INSERT、UPDATE和／或DELETE查询。检验每一个查询返回的值，弄清楚它是否成功了是很重要的。如果其中任何一个查询失败了，mysqli_rollback()函数就会被用来将系统返回到事务进行之前的状态。 * 假设组成事务组的所有命令都成功执行了，就要用mysqli_commit()函数将变化保存到数据库系统。请注意，一旦这个函数被调用，事务就不能被撤消了。 工作实例 要了解这个在实践中是怎么工作的，让我们回到前面讨论过的银行转帐的例子.我们假设你的任务是建立一个简单的Web应用程序，让用户在他们的银行帐户间转帐。我们再进一步假设一个单独用户的帐户存储在一个MySQL数据库中，如下所示： +----+------------+---------+ | id | label | balance | +----+------------+---------+ | 1 | Savings #1 | 1000 | | 2 | Current #1 | 2000 | | 3 | Current #2 | 3000 | +----+------------+---------+ 3 rows in set (0.34 sec) Now, we need to build a simple interface that allows users to enter a cash amount to transfer money from one account to another. The actual "transaction" will be performed with two UPDATE statements, one to take the transfer amount out of the source account, i.e., debit, and the other to credit the transfer amount to the destination account, i.e., credit. Assuming what we are doing is transferring money between accounts, the total available balance across all accounts ($6000) should always remain the same. Listing B shows possible codes, Listing B: // connect to database $dbh = mysqli_connect("localhost", "user", "pass", "test") // turn off auto-commit mysqli_autocommit($dbh, FALSE); // look for a transfer if ($_POST[submit] && is_numeric($_POST[amt])) { // add $$ to target account $result = mysqli_query($dbh, "UPDATE accounts SET if ($result !== TRUE) { mysqli_rollback($dbh); // if error, roll back transaction } // subtract $$ from source account $result = mysqli_query($dbh, "UPDATE accounts if ($result !== TRUE) { mysqli_rollback($dbh); // if error, roll back transaction } // assuming no errors, commit transaction mysqli_commit($dbh); } // get account balances // save in array, use to generate form $result = mysqli_query($dbh, "SELECT * FROM accounts"); while ($row = mysqli_fetch_assoc($result)) { $accounts[] = $row; } // close connection mysqli_close($dbh); ?> Once the form is completed and submitted, the two UPDATE queries start actually performing the debit and credit operations. Note that each query has a mysqli_rollback() at the end, which will be activated if the query fails. Assuming no query fails, the new income and expenditure table is stored in the database by calling mysqli_commit(). At that point the database connection is closed. You can try it yourself and transfer $500 from Savings #1 to Current #2. Once you perform the transfer, you will see the new balance sheet results as shown in Exhibit B. Tip: Of course, this is just a simple two-command transaction. Usually, you can use this transaction model when there are many SQL statements to be executed together, and the failure of one statement has a cascading effect on other statements. In these cases, you may find it easier to condense the calls to mysqli_query() and mysqli_rollback() into a single user-defined function that can be called when needed. As you can see, implementing a transaction processing model with PHP and MySQL can make your MySQL database more robust to query execution errors. However, before you start rewriting code and using this model, it is worth noting that transactions do increase the cost of system performance management, so it is always a good idea to do a cost-benefit analysis before implementing this model.
mysql> SELECT * FROM accounts;
or die("Cannot connect");
balance = balance + " . $_POST[amt] . " WHERE id = " . $_POST[to]);
SET balance = balance - " . $_POST[amt] .
" WHERE id = " . $_POST[from]);
As you can see, the script starts by connecting to the database and turning off auto-commit. Then execute a SELECT query to retrieve the cash receipts and payments for all accounts, and then construct a table with a drop-down interface to select the source/target account to be used for the transaction. Exhibit A shows the original form.
Initial form
The status after the transaction is completed.