Re: PHP and Transactions with InnoDB

In article <5.0.2.1.0.20020829121546.01bca5d8@pop1.ns.sympatico.ca>, Miles Thompson <milesthompson@ns.sympatico.ca> 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.