Why Successful Payments Vanished: MyBatis Connection‑Pool Pitfalls Explained

Incident Overview

During a recent online release the payment service reported successful payments while the order table remained empty. Occasionally the operation succeeded, but most of the time it failed without any error logs or exceptions.

Initial Diagnosis

DBA inspection revealed several long‑running transactions holding locks on the order table. The root cause was identified as a business interface that forgot to commit its transaction.

Emergency Fix

The quickest mitigation was to restart the application, which forced the connection pool to release the stuck connections and restored payment functionality temporarily.

Code Issue

@Service
public class SomeService {
    public void handleSpecialCase() {
        // open transaction
        sqlSession.connection.setAutoCommit(false);
        mapper.insert(data);
        // special branch forgets commit!
        if (specialCondition) {
            return; // commit never executed
        }
        sqlSession.commit();
    }
}

The return in the special branch prevented commit from being called, leaving the transaction open.

Quick Fix

@Service
public class SomeService {
    public void handleSpecialCase() {
        try {
            sqlSession.connection.setAutoCommit(false);
            mapper.insert(data);
            if (specialCondition) {
                sqlSession.commit(); // added missing commit
                return;
            }
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
            throw e;
        }
    }
}

After redeploying the corrected code, the issue disappeared.

Post‑mortem Analysis

Even though the failing service logged a normal commit, the underlying connection remained marked as active because Spring’s doGetTransaction reuses the same ConnectionHolder from TransactionSynchronizationManager. When a previous transaction is left uncommitted, the connection stays in a “transaction‑active” state.

Subsequent services (e.g., PaymentService.createOrder) obtained this polluted connection, causing isExistingTransaction to return true. Spring then treated the call as joining an existing transaction rather than starting a new one, and processCommit skipped the actual doCommit because status.isNewTransaction() was false.

Why Did It Occasionally Succeed?

TransactionSynchronizationManager

is ThreadLocal; if a request was dispatched to a thread with a clean connection, the transaction completed normally. Requests that hit a thread holding the polluted connection failed, explaining the non‑deterministic behavior.

Preventive Measures

Connection‑pool health checks : configure validation query and connection‑init‑sql to reset connection state before reuse.

spring:
  datasource:
    hikari:
      connection-test-query: SELECT 1
      validation-timeout: 3000
      connection-init-sql: SET autocommit=1

Database‑level monitoring : add alerts for long‑running transactions (>30 s).

SELECT * FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30;

Explicit transaction handling : always place commit at the end of the try block and rollback in the catch block, with resources closed in finally.

Debugging strategy : when facing inexplicable issues, set breakpoints in getTransaction and trace the ConnectionHolder state rather than relying solely on logs.

Key Takeaways

Connection pools are not just performance optimizers; they also recycle connection state, which can propagate transaction bugs.

Manual transaction management must be crystal‑clear about commit/rollback paths.

Application‑level logs may appear normal while the database layer hides problems; comprehensive DB monitoring is essential.

ThreadLocal‑based transaction resources can cause intermittent failures if previous transactions are left open.

Documenting the incident, root cause, fix, and preventive steps helped the team avoid repeating the same pitfall.