如何测试MySQL交易？ [英] How to test MySQL transactions?

问题描述

我有一个关于在事务中测试查询的问题。我一直在使用MySQL交易一段时间了，每次我这样做，我使用的东西如下：

I have a question about testing the queries in a transaction. I've been using MySQL transactions for quite some time now, and everytime I do this, I use something like:

$doCommit = true;
$error = "";
mysql_query("BEGIN");

/* repeat this part with the different queries in the transaction
   this often involves updating of and inserting in multiple tables */
$query = "SELECT, UPDATE, INSERT, etc";
$result = mysql_query($query);
if(!$result){
    $error .= mysql_error() . " in " . $query . "<BR>";
    $doCommit = false;
}
/* end of repeating part */

if($doCommit){
    mysql_query("COMMIT");
} else {
    echo $error;
    mysql_query("ROLLBACK");
}

现在，经常发生我想测试我的交易，所以我改变 mysql_query（COMMIT）; 到 mysql_query（ROLLBACK）; ，但我可以想象这不是一个非常好的方式来测试这种东西。将每个表复制到temp_table并将其更新并插入到这些表中，然后将其删除（通常是因为表可能非常大）而通常并不真正可行。当然，当代码进入生产时，相关的错误处理（而不仅仅是打印错误）被放置到位。

Now, it often happens that I want to test my transaction, so I change mysql_query("COMMIT"); to mysql_query("ROLLBACK");, but I can imagine this is not a very good way to test this kind of stuff. It's usually not really feasable to copy every table to a temp_table and update and insert into those tables and delete them afterwards (for instance because tables maybe very large). Of course, when the code goes into production relevant error-handling (instead of just printing the error) is put into place.

做这样的事情最好的方法是什么？

What's the best way to do stuff like this?

推荐答案

首先，你的实现有一个错误。如果查询错误，当前事务将自动回滚然后关闭。因此，当您继续执行查询时，它们将不在一个事务中（它们将被提交给数据库）。然后，当您执行回滚时，它会默默地失败。从 MySQL文档：

First of all, there is a bug in your implementation. If a query errors out, the current transaction is automatically rolled back and then closed. So as you continue to execute queries, they will not be within a transaction (they will be commited to the DB). Then, when you execute Rollback, it'll silently fail. From the MySQL docs:

Rolling back can be a slow operation that may occur implicitly without the user 
having explicitly asked for it (for example, when an error occurs).

只能使用显式命令 ROLLBACK 如果您在应用程序中确定需要回滚（出于查询错误以外的原因）。例如，如果您从帐户中扣除资金，您将明确回滚，如果您发现用户没有足够的资金来完成交易...

The explicit command ROLLBACK should only be used if you determine in the application that you need to rollback (for reasons other than a query error). For example, if you're deducting funds from an account, you'd explicitly rollback if you found out the user didn't have enough funds to complete the exchange...

就测试事务而言，我复制数据库。我创建一个新的数据库并安装一组虚拟数据。然后我使用自动化工具运行所有测试。该工具将实际提交事务并强制回滚，并检查在整个测试期间是否维护预期的数据库状态。因为如果事务有未知的输入，因此从事务中编程性地知道结束状态变得更加困难，所以测试关闭实时（甚至是复制的）数据并不容易。你可以做（​​应该），但不要依赖这些结果来确定你的系统是否正常工作。使用这些结果为自动测试仪构建新的测试用例。

As far as testing the transactions, I do copy the database. I create a new database and install a set of "dummy data". Then I run all the tests using an automated tool. The tool will actually commit the transactions and force rollbacks, and check that the expected database state is maintained throughout the tests. Since it's harder to programatically know the end state from a transaction if you have an unknown input to the transaction, testing off of live (or even copied-from-live) data is not going to be easy. You can do it (and should), but don't depend upon those results for determining if your system is working. Use those results to build new test cases for the automated tester...

这篇关于如何测试MySQL交易？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！