Transactions Across Objects

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
croupier
Forum Newbie
Posts: 2
Joined: Thu Jan 16, 2003 1:29 am

Transactions Across Objects

Post by croupier »

I have been trying to incorporate object oriented code into PHP and all my SQL queries happen through low level data access classes. I use PHPLIB for my database access with persistent connection. I am using the InnoDB handler and have added 3 new functions called begin(), commit() and rollback() to my PHPLIB mysql class.

I am faced with a problem of maintaining transaction thru multiple objects. assuming that my class structure is as follows

class SubClass {
SubClass() {
$sql = "insert into";
$db = new DB;
$res = $db->query($sql);
if(!$res)
$db->rollback();
}
}
class MainClass {
var $obj1;
var $obj2;
MainClass() {
$db = new DB;
$db->begin();
$obj1 = new SubClass;
$obj2 = new SubClass;
$db->commit();
}
}

this is a rough picture, is this strategy bad, if so what could i use to ensure transactions involving multiple object.
More specifically, does a persistent connection mean that the transaction state is also maintained over multiple connections ?
Thanks in advance
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Interesting question, as I didn't think MySQL supported transactions. I take it that this PHPLIB is acting as the transaction manager? i.e. there must be one place where all DB actions report back to - success/failure
to guantee that the series of operations is treated "atomically" - either 100% success or 100% failure & rollback - which in this case I would suspect are either deletes or the state of the affected rows prior to updates are saved and re-applied on rolback.

Interesting!

Phil J.
croupier
Forum Newbie
Posts: 2
Joined: Thu Jan 16, 2003 1:29 am

More Clarifications

Post by croupier »

Well, PHPLIB is just a library and one of the APIs it offers is to access databases, in my case MySQL. Well, yes, MySQL does not offer transaction support thats the reason i am using MySQL+INNODB as the handler. INNODB allows transaction support.
I know this works.. i.e

function error($db)
{
$db->rollback();
exit;
}

$db = new DB;
$db->begin();

if(!$db->query("insert"))
error($db);
if(!$db->query("insert"))
error($db);
if(!$db->query("insert"))
error($db);

$db->commit();

now imagine each query is embedded within an object of a class. can i have a transaction over multiple objects and will the connection retain a transaction state if i am using persistent connections..
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

In that case, if everything is reporting back to INNODB, and INNODB
is handling the transaction, I would think so. INNODB apparantly is what acts as your transaction manager. Don't know a thing about INNODB or the PHPLIB other that how you would decribe it. Thinking that due to the nature of web apps that transactions are probaby best kept to the duration of 1 page's multistep DB operation. Connections are expensive, some other databases by their licensing limit you to N concurrent connections per database. Would certainly be interested in hearing more about this
topic of transactions. We have something called MTS (Microsoft Transaction Server), now called DTC (Ditributed Transaction Coordinator) on the ASP side, but I haven't yet used it's transaction capabilities...

Phil J.
Post Reply