[PHP] PHP and Transactions with InnoDB
I could use some practical tips on doing transactions with InnoDB tables in MySQL. I want to grab and lock a record and do a whole load of related stuff, then make sure everything is updated in a consistent way, something like this skeleton: BEGIN SELECT * from blah WHERE something=n FOR UPDATE //Grab and lock a record SELECT * from t1, t2 where a=1 ... INSERT blah... UPDATE something else... if ($allok) COMMIT else ROLLBACK I don't quite get what I need to do to make sure these are all regarded as part of the same transaction. Does the transation apply to all queries made between the begin and commit (I assume so)? What happens when I have another process doing the same thing at the same time? Do I need to keep the result value I get from the begin query and somehow use it for the commit at the end so MySQL knows they are two ends of the same transaction? Or does MySQL somehow track that for me - i.e. can there only be one transaction in progress per connection? I'm finding the MySQL docs are extremely dry and somewhat short on examples... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP and Transactions with InnoDB
I've not worked with InnoDb, but given that the web is a stateless space and all kinds of things can happen, I'd be very reluctant to lock a record until the very moment I'm updating it. In other words, when I have my batch of updates and inserts ready to go, then I'd begin by transaction, execute them, and do a commit or a rollback. Cdn$ .02 - Miles Thompson At 04:59 PM 8/29/2002 +0200, Geranium wrote: I could use some practical tips on doing transactions with InnoDB tables in MySQL. I want to grab and lock a record and do a whole load of related stuff, then make sure everything is updated in a consistent way, something like this skeleton: BEGIN SELECT * from blah WHERE something=n FOR UPDATE //Grab and lock a record SELECT * from t1, t2 where a=1 ... INSERT blah... UPDATE something else... if ($allok) COMMIT else ROLLBACK I don't quite get what I need to do to make sure these are all regarded as part of the same transaction. Does the transation apply to all queries made between the begin and commit (I assume so)? What happens when I have another process doing the same thing at the same time? Do I need to keep the result value I get from the begin query and somehow use it for the commit at the end so MySQL knows they are two ends of the same transaction? Or does MySQL somehow track that for me - i.e. can there only be one transaction in progress per connection? I'm finding the MySQL docs are extremely dry and somewhat short on examples... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP and Transactions with InnoDB
In article [EMAIL PROTECTED], Miles Thompson [EMAIL PROTECTED] wrote: I've not worked with InnoDb, but given that the web is a stateless space and all kinds of things can happen, I'd be very reluctant to lock a record until the very moment I'm updating it. In other words, when I have my batch of updates and inserts ready to go, then I'd begin by transaction, execute them, and do a commit or a rollback. I should have mentioned: it's not actually running behind a web process but as a stand-alone CLI process in PHP 4.3-cvs, so it's not quite as subject to the vagaries of web accesses. At the moment there's really only one process involved, but I'm intending to expand it later, hence this query. I see what you're saying, but the problem I have is that the results of the intermediate queries are dependent on me having control of the master record, i.e. If I make them without the master record being locked, by the time it comes to posting them back to the database, they may be meaningless, so I need to make sure that the state of the database is consistent throughout all the queries, as if I was the only client. Isn't this the whole point of transactions? Is ther any particular problem with having transactions that take a while, say 10-20 seconds overall? I suspect I'm just not quite clear on exactly the relationship between record locking and transactions. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
