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:
fpmi.db.runUpdateQuery("INSERT INTO MyDatabase1 SET Number=%d, Value='OK'" % (number))
fpmi.db.runUpdateQuery("INSERT INTO MyDatabase2 SET Current_Number=%d" % (number))
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?