Here's the current line on transactions in MySQL from the 3.22.26a docs:
"""5.3.3 Transactions
Transactions are not supported. MySQL shortly will support atomic operations, which are like transactions without rollback. With atomic operations, you can execute a group of INSERT/SELECT/whatever commands and be guaranteed that no other thread will interfere. In this context, you won't usually need rollback. Currently, you can prevent interference from other threads by using the LOCK TABLES and UNLOCK TABLES commands. See section 7.23 LOCK TABLES/UNLOCK TABLES syntax. """
This is almost useless, perhaps even worse than :-) We REALLY REALLY need rollback capabilities to function correctly. Otherwise, if you raise an exception somewhere, how do you abort it and roll back all changes?
def _begin(self): self.db.query('begin transaction')
This is called at the beginning of all transaction.
def _finish(self): self.db.query('commit transaction')
This is like a "commit" but tells it to do whatever is necessary ot commit the transaction.
def _abort(self): self.db.query('rollback transaction')
Duh :-)
(For a Python DB API-style interface, _begin would pass, _finish would do self.db.commit(), and _abort would do self.db.rollback().)
_begin might or might not pass. It's really dependent on the database :-) Some databases have implicit transaction starts, we don't like them :-)
There are a couple possibilities for implementing transactions in ZMySQLDA:
1) Automatic use of LOCK TABLES.
Excuse me while I lose my lunch. :-) This is so amazingly hackish as to almost be a joke. This *might* work partially in some half-brain-dead fashion in a single-threaded, non concurrent environment, but when you have other situations where you might have two people touching tables at the same time, in some cases dependent on each other... This is where "SELECT * FROM UPDATE" comes from. You have to tell the database that you plan to use some data in a calculation that you will then modify some other table based on, and you don't want someone playing with that core data while you're in the middle of your transaction. This is just the tip of the concurrency iceberg. It's all about keeping your data sane under load. I can write all kinds of hacks that will fix it in low usage situations, but will they hold up with 100+ concurrent users? Not to slam MySQL, but... it has it's place, but I'm not sure it's under heavy update loads. Chris -- | Christopher Petrilli Python Powered Digital Creations, Inc. | petrilli@digicool.com http://www.digicool.com