How to test MySQL transactions?

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");
}

Now, it often happens that I want to test my transaction, so I change mysq开发者_如何学JAVAl_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?

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).

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...

Maybe you could refactor your first example and use some DB access wrapper class?

In that wrapper class you can have a variable $normalCommit = true; and a method SetCommitMode() which sets that $normalCommit variable. And you have a method Commit() which commits if($normalCommit == true) Or even have a variable $failTransaction which calls mysql_query("ROLLBACK"); if you wish (so you could pass/fail many sequential tests).

Then when you run the test, you can set somewhere in the test code file: $myDBClass->SetCommitMode(false); or $myDBClass->RollBackNextOperation(true); before the operation which you wish to fail, and it will just fail. In such a way the code which you are testing will not contain those fail/commit checks, only the DB class will contain them.

And normally ONLLY the test code (especially if you do unit testing) should call those SetCommitMode and RollBackNextOperation methods, so you accidentally do not leave those calls in the production code.

Or you could pass some crazy data to your method (if you are testing a method), like negative variables to save in UNSIGNED fields, and then your transaction should fail 100% if your code does not do commit after such an SQL error (but it should not).

Generally I use something like (I use pdo for my example):

$db->beginTransaction();
try {
  $db->exec('INSERT/DELETE/UPDATE');
  $db->commit();
}
catch (PDOException $e) {
  $db->rollBack();
  // rethrow the error or
}

Or if you have your own exception handler, use a special clause for your PDOExceptions, where to rollback the execution. Example:

function my_exception_handler($exception) {
  if($exception instanceof PDOException) {
    // assuming you have a registry class
    Registry::get('database')->rollBack();
  }
}