Implementing SQL transactions

I have always used MySQL’s MyISAM database engine in my projects so far, but I am looking at another project which will have to update several related tables simultaneously when the user presses a button.

It seems obvious that I will have to use the Innodb engine, which supports transactions, but I am having problems implementing a solution. I tried the following code:

try: fpmi.db.runUpdateQuery("START TRANSACTION") fpmi.db.runUpdateQuery("INSERT INTO MyDatabase1 SET Number=%d, Value='OK'" % (number)) fpmi.db.runUpdateQuery("INSERT INTO MyDatabase2 SET Current_Number=%d" % (number)) fpmi.db.runUpdateQuery("COMMIT") print "Successful" except: fpmi.db.runUpdateQuery("ROLLBACK") print "Rolled back"
The problem is that errors from runUpdateQuery cannot be trapped (see this post), meaning that it is possible to end up with partial updates.

Is this the right way to go about transactional updates (and I’ll just have to wait for the new feature to be implemented), or am I approaching things the wrong way?

Actually, this approach won’t work, because each call to fpmi.db.runUpdateQuery runs in its own auto-commit transaction. We’ll have to think about the best way to accommodate better transactional support within FactoryPMI.

It is possible to turn off auto commit for the Innodb engine - does this change anything?

No, the auto-commit we’re talking about here is on the JDBC side on the FactoryPMI Gateway.