On May 22, 2008, at 7:25 PM, Chris wrote:
Philip Thompson wrote:
Hi all.
I'm currently working on a project that's in its beta stage. Due to
time
restraints, we were unable to build in transactions from the
beginning.
Well, we're getting to the point where we can now put in
transactions.
Basically, I'm curious to find out your opinion on the best way to
accomplish this.
Currently, my thoughts for doing this are:
<?php
function someFunctionThatNeedsTransactions ()
{
$this->db->begin(); // Start transaction
$this->db->query("SELECT..."); // Run my queries here
$this->db->query("UPDATE...");
// Do some PHP logic here
$this->db->query("SELECT..."); // Run more queries
$this->db->query("INSERT...");
$this->db->commit(); // Commit transaction
}
?>
If there was a failure in one of the queries, that would be caught in
the database class ('db' instance) and <?php $this->rollback(); ?>
would
be called. Note that all the tables are InnoDB and the above
code/functionality works.
Ideally, I would have liked to use stored procedures, but that
decision
was not mine. So are there any complications I'm potentially missing?
I'd get your db class to handle nested transactions, so if you had
something like this:
$db->begin();
....
someFunctionThatNeedsTransactions();
...
.. oops, need to rollback.
$db->rollback();
the bits between "someFunction" and the "rollback" will be committed
(by
the function) and can't be rolled back.
See http://dev.mysql.com/doc/refman/5.0/en/savepoints.html
The situation might not come up but it can't hurt to have it already
built in "just in case".
This doesn't appear deal with *nested transactions.* It appears that
it will use a single transaction and you can just save up to a certain
point upon failure. As of this point, it's all or nothing b/c we are
dealing with fairly crucial information. However, I will look into
building savepoints into the class so that I'll have that option in
the future.
It seems like I've used savepoints or nested transactions in MSSQL in
the past (in stored procedures). I hope MySQL will support nested
transactions in future releases.
So, am I taking the best approach (at this point) for dealing with
transactions? I will basically have to evaluate every situation in the
program where a failure would occur and put in a $db->rollback();
call....?
What about try/catch blocks? I'm not currently using them, but maybe
it would be the best way to handle exceptions when dealing with
multiple queries. If there's *any* query failure, explode!
Thanks!
~Philip
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
