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(...)
I don't do transaction commit or rollback in DAO class methods, because of the nesting problem you identified.
Some people "fake" transactions using a counter. If a nested DAO called by another DAO starts a transaction, it doesn't really start a transaction, it just increments the nesting counter. And it doesn't really commit or rollback if the nesting counter is greater than 0. It just decrements the counter. So only the outermost DAO whose counter is 0 gets to issue a real commit or rollback.
The problem with that supposed solution is that DAO's think they did a commit or rollback, even though they didn't. And the outer code can do the reverse operation without the knowledge of the inner DAO.
For example, an inner DAO encounters an error, and tries to rollback to undo whatever change it id. The rollback is fake and doesn't happen. Then the outer DAO, unaware that an error has occurred, commits the transaction, which is a real commit. Thus the database changes have been committed, when they should have been discarded.
I'm sure you can imagine the reverse situation can happen too. Inner DAO make a proper change, but the outer DAO rolls it back.
Chaos ensues!
The better solution is to refrain from doing start transaction or commit/rollback in any DAO.
Instead, do both start and resolve transaction in the code that calls the DAOs. In an MVC web application, for example, this could be at the Controller level. Whereas all Models should assume a transaction has been started already, and that the transaction will be resolved appropriately by the caller.
If a Model or DAO wants to signal the caller that an error has occurred, then raise an error, which bubbles up to the caller and the caller should handle it by doing a rollback at that level. If no error occurs after the call to the Model or DAO finishes, then commit at that level.