database transaction rollback processing in PHP

Question

try
{
  $con->beginTransaction();

  $this->doSave($con);

  $con->commit();
}
catch (Exception $e)
{
  $con->rollBack();

  throw $e;
}

The code above is quite standard an approach to deal with transactions,

but my question is:what if $con->rollBack() also fails?

It may cause db lock,right?If so,what's the perfect way to go?

Answer 1

MySQL handles an error situation by rolling back the transaction itself.

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 MySQL documentation covers your "what if case"

If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

Further:

Both commit and rollback release all InnoDB locks that were set during the current transaction.

Edit: I set up a test for your proposed situation. Using an innoDB table with MySQL 5.

$db = new DB();
$db->beginTransaction();
$db->exec("INSERT INTO `table` (`val`) VALUES('commit?')");
sleep(30);
$db->commit();

This works as I've described below in the comments. If allowed to complete, the transaction is committed to the table after 30 seconds.

If I kill the script before allowing it to complete, the transactions is rolled back and the table is left in a clean state - as expected.

Answer 2

Use a transactional database engine, such as InnoDB. This will guarantee data integrity in the event of a query failure during a write. MySQL defaults to using the MyISAM engine, which is faster and is not transactional. All you need to do is change the storage engine when creating your database.

Answer 3

When using transactions, the basic idea is that nothing will actually get permanently written to the database until commit is called.

A couple of situations :

• If you :
  • begin a transaction
  • execute some queries
  • rollback
  • Then the queries are rolled-back ; which means their result is not written to the database.
• If you :
  • begin a transaction
  • execute some queries
  • disconnect (which happens if your PHP script ends -- for instance, because of a Fatal Error or a die)
  • Then, the queries are not commited -- which means they are not written to the database ; which means it's the same as if there had been a rollback.

To makes things simple : starting from the begin transaction, nothing gets permanently written to the database, until you send a commit. If there is no commit... Nothing is permanently written, and when your PHP script disconnects from the DB, the DB "cancels" what that PHP script did not commit.

Of course, this is exactly the same if you are using something else than PHP to connect to your database -- like the command-line MySQL client, for instance.

Answer 4

I think the above comment posted by Drew about MyISAM being faster is very misleeding and has in the past caused me to choose MyISAM over InnoDB over transactional functionality because I thought I was going to be losing alot of speed!

In regards to the original question... I find myself still learning PHP/MySQL and as my app has grown bigger I've needed to wrap things in a transaction... the code posted above is quite useful and it may mean I need to rethink about how everything is coded up to yet! I used to have begin/commit within functions but when these functions call other functions that may need to be in a transaction... it doesn't work that well :s