[Python] Roll back a commit?

Note: This question focuses on web apps utilising MySQL's transactions - commit and rollback. Even though the code samples below use Python, the problem itself is not limited to the choice of programming language building the web app.

Imagine I have two files: main.py and my_dao.py. main.py acts as the starting point of my application and it simply triggers the methods in class MyDAO:

main.py

import my_dao

MyDAO.run_a()
...
MyDAO.run_b()
...


my_dao.py defines a DAO-like class with two methods, each does something to the database:

import mysql.connector

class MyDAO:
conn = mysql.connector.connect(...)

@classmethod
def run_a(cls):
try:
do_something_1()
cursor = cls.cursor()
cursor.execute('Query A 1')
cursor.execute('Query A 2')
do_something_2()
cursor.close()
conn.commit()
except Error as e:
conn.rollback()
log(...)

@classmethod
def run_b(cls):
try:
do_something_1()
cursor = cls.cursor()
cursor.execute('Query B 1')
# calling cls.run_a() here
cls.run_a()
cursor.execute('Query B 2')
do_something_2()
cursor.close()
conn.commit()
except Error as e:
conn.rollback()
log(...)



As you can see, both methods have their own commits and rollbacks. run_a() basically runs a bunch of queries and then commit. run_b() is similar except that it calls run_a() in between its queries.


Problem

If everything works, this seems fine. However, if run_b() fails after successfully running run_a() inside, this would cause a problem because run_a() has already committed and no matter how run_b() rollbacks, it will not rollback to the point before run_b() was called.

I understand that MySQL doesn't support nested transactions. How can I redesign the above so that run_b() can rollback successfully including the commit used by run_a() within it?


My thoughts:

Not sure if the above is a bad design, but I have wrapped each method with try...except and commit/rollback where needed so that each method can be called independently outside the class.

I am aware of savepoint but I think rewriting the above using savepoint would be quite messy, and run_a() would also lose its 'independentness' as it doesn't know whether it should commit within the method itself.

Alternatively, I have also thought of extracting the inner part of run_a() into a common function, but it looks quite clumsy to me:

import mysql.connector

class MyDAO:
conn = mysql.connector.connect(...)

@classmethod
def _real_run_a(cls, cursor):
cursor.execute('Query A 1')
cursor.execute('Query A 2')

@classmethod
def run_a(cls):
try:
do_something_1()
cursor = cls.cursor()
cls._real_run_a(cursor)
do_something_2()
cursor.close()
conn.commit()
except Error as e:
conn.rollback()
log(...)

@classmethod
def run_b(cls):
try:
do_something_1()
cursor = cls.cursor()
cursor.execute('Query B 1')
cls._real_run_a(cursor)
cursor.execute('Query B 2')
do_something_2()
cursor.close()
conn.commit()
except Error as e:
conn.rollback()
log(...)

Continue reading...