PHP and MySQL ACID Program design

a while ago I completely recoded my application so that MySQL would perform in an ACID way.

At the very top level of all functions I do something like this:

        try{
            $db->begin();
            dosomething($_SESSION['userid']);
            $db->commit();
     开发者_开发百科   }catch(advException $e){
            $eCode = $e->getCode();
            $eMessage = $e->getMessage();
            # Success
            if ($eCode == 0){
                $db->commit();
            }else{
                $db->rollback();
            }
        }

Within the function 'dosomething' I have the Exceptions thrown to users like:

throw new Exception('There was a problem.',1);

or

throw new Exception('You have successfully done that!', 0);

So that I can control the flow of the program. If theres a problem then roll back everything that happened and if everything was good then commit it. It's all worked quite great but there's just one flaw that I've come across so far. I added Exception logging so I can see when there are issues that users face. But the problem is, if the table that logs the errors is InnoDB then it's also included in the transaction and will rollback if theres a problem so no errors are stored. To get around this I basically just made the Error logging table MyISAM so when a rollback is done, the changes are still there.

Now I'm thinking of other bits I'd like to keep out of the transaction, like sending a mail within my application to the admin to help alert of problems.

Is there any sort of way for me to not include a database insert within the parent transaction? Have I taken a bad route in terms of Application/DB design and is there any other way I could have handled this?

Thanks, Dominic

It is not good idea to throw an Exception on success.

You have to do DB insert after previous rollback was called.

catch (Exception $e) {
  $db->rollback();
  Log::insert('Error: ' . $e->getMessage());
}

Try to use Logger to control your program. It is more flexible way.

Use return codes for successful operation, and Exceptions to specify exceptional situations (such as severe errors, etc.). As for your specific issue, I'd recommend having a separate database for logging if you choose to use the rollback strategy.