Avoid Half-Committed States in MySQL: Move Commit/Rollback to the Controller and Keep DAOs Composable

MySQL lacks nested transactions. Why inner commits cause half-commits and how demarcation fixes it: move commit/rollback to controllers; keep DAOs composable.

MySQL does not support nested transactions. If you let inner data-access methods commit while they are called by outer methods that also expect to control commit or rollback, you can end up with a half-committed state the caller cannot undo. This guide shows why that happens and how to structure transaction demarcation so that a higher layer owns commit/rollback and your DAOs remain composable.

The setup that backfires

The application triggers DAO operations from a simple entry point. One DAO method calls another, and both manage their own commit/rollback. Everything looks tidy until an error occurs after the inner method has already committed.

main.py

import data_unit

DataAgent.alpha()
...
DataAgent.beta()
...

data_unit.py

import mysql.connector

class DataAgent:
  link = mysql.connector.connect(...)

  @classmethod
  def alpha(cls):
    try:
      pre_op()
      cur = cls.cursor()
      cur.execute('Query A 1')
      cur.execute('Query A 2')
      post_op()
      cur.close()
      link.commit()
    except Error as ex:
      link.rollback()
      write_log(...)

  @classmethod
  def beta(cls):
    try:
      pre_op()
      cur = cls.cursor()
      cur.execute('Query B 1')
      cls.alpha()
      cur.execute('Query B 2')
      post_op()
      cur.close()
      link.commit()
    except Error as ex:
      link.rollback()
      write_log(...)

Why this fails

When DataAgent.beta() calls DataAgent.alpha(), the inner method commits. If beta() later fails and tries to roll back, it cannot undo what alpha() already committed. MySQL does not support nested transactions, so there is no way for an outer rollback to rewind past an inner commit.

A tempting workaround is to “fake” nesting with a counter. Inner DAOs increment a counter instead of starting a real transaction, and they decrement it instead of performing a real commit/rollback. Only the outermost caller performs the actual commit/rollback. The trouble is that inner code believes it completed a commit or rollback even though it did not. That means the outer layer can do the opposite operation without the inner layer realizing it. An inner DAO may try to roll back after an error, but it is a fake rollback; then the unaware outer layer issues a real commit and persists changes that should have been discarded. The reverse can also happen: inner work is valid, but the outer layer rolls it back. Chaos ensues.

The fix: move transaction demarcation out of DAOs

The safer pattern is to avoid start and resolution of transactions in DAOs. Let the caller own the transaction boundaries. In a web application this usually sits at the Controller level. Models/DAOs assume a transaction is already in progress and they never commit or roll back; instead they raise errors. The caller then decides whether to commit or roll back.

This is transaction demarcation. Different stacks provide different ways to express it, but the principle stays the same: the outer layer defines the boundary. If the DAO needs to signal failure, it throws; the caller rolls back. If everything completes, the caller commits.

Refactoring the code

First, make DAO methods free of commit/rollback, and let them surface errors. If you want to share inner logic that is used by multiple methods, extract it into a helper that receives a cursor. This keeps each operation reusable without changing its behavior depending on who calls it.

data_unit.py

import mysql.connector

class DataAgent:
  link = mysql.connector.connect(...)

  @classmethod
  def _alpha_core(cls, cur):
    cur.execute('Query A 1')
    cur.execute('Query A 2')

  @classmethod
  def alpha(cls):
    pre_op()
    cur = cls.cursor()
    cls._alpha_core(cur)
    post_op()
    cur.close()

  @classmethod
  def beta(cls):
    pre_op()
    cur = cls.cursor()
    cur.execute('Query B 1')
    cls._alpha_core(cur)
    cur.execute('Query B 2')
    post_op()
    cur.close()

Now perform commit/rollback only at the call site. The first statement implicitly starts a transaction, and the caller resolves it. Some drivers expose an explicit start method; whether you use it or not, the boundary belongs to the caller.

main.py

from data_unit import DataAgent

try:
  DataAgent.beta()
  DataAgent.link.commit()
except Error:
  DataAgent.link.rollback()
  write_log(...)

Why this matters

Keeping commit and rollback out of DAOs prevents partial commits that callers cannot undo. It also avoids the misleading state created by fake nesting counters. With clear transaction demarcation, your service or controller layer retains full control over consistency. If an error occurs in any inner operation, it propagates and the outer layer rolls back exactly once. If no error occurs, the outer layer commits exactly once. The result is predictable behavior and composable data operations.

Takeaways

Do not commit or roll back inside DAOs when those methods can be composed by other methods. Let a higher layer own the transaction boundary. If inner operations need to be reusable both on their own and inside larger flows, factor their SQL into helpers that accept an existing cursor. On failure, raise an error and let the caller resolve the transaction. If your driver exposes a method to explicitly begin, you can use it; if not, the first operation implicitly starts the transaction and the caller still decides whether to commit or roll back. That is the essence of robust transaction demarcation for web apps that use MySQL.