Re: PHP + MySQL transactions

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