Why Did Successful Payments Lose Their Orders? A MyBatis Connection‑Pool Pitfall Explained

Incident Overview

During a payment operation the service reported success to the user, but no rows were inserted into the order table. DBAs observed lock‑wait timeouts and several transactions that never committed, leaving rows locked.

Business code finished normally with no exceptions and normal log output.

Log showed commit was called but the data was absent.

Intermittent success – most attempts failed, a few succeeded.

Root Cause

A newly deployed service contained an early return that skipped the commit call, leaving the transaction open:

@Service
public class SomeService {
    public void handleSpecialCase() {
        // open transaction
        sqlSession.connection.setAutoCommit(false);
        // execute SQL
        mapper.insert(data);
        // special case: forgot to commit!
        if (specialCondition) {
            return; // early return, commit never executed
        }
        sqlSession.commit();
    }
}

The open transaction kept the ConnectionHolder marked as active. When the next request invoked PaymentService.createOrder, Spring’s transaction manager retrieved the same connection from the pool, considered it an existing transaction, and therefore did not perform a real commit:

@Service
public class PaymentService {
    public void createOrder(Order order) {
        TransactionStatus status = transactionManager.getTransaction(new DefaultTransactionDefinition());
        try {
            orderMapper.insert(order);
            transactionManager.commit(status);
        } catch (Exception e) {
            transactionManager.rollback(status);
            throw e;
        }
    }
}

Debugging Process

Breakpoint on getTransaction revealed the flow:

public final TransactionStatus getTransaction(@Nullable TransactionDefinition definition) throws TransactionException {
    TransactionDefinition def = (definition != null ? definition : TransactionDefinition.withDefaults());
    Object transaction = doGetTransaction();
    if (isExistingTransaction(transaction)) {
        return handleExistingTransaction(def, transaction, logger.isDebugEnabled());
    }
    // create new transaction …
}

doGetTransaction

obtains the current connection holder from TransactionSynchronizationManager:

protected Object doGetTransaction() {
    DataSourceTransactionObject txObject = new DataSourceTransactionObject();
    txObject.setSavepointAllowed(this.isNestedTransactionAllowed());
    ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(this.obtainDataSource());
    txObject.setConnectionHolder(conHolder, false);
    return txObject;
}

The helper isExistingTransaction checks whether the holder reports an active transaction:

protected boolean isExistingTransaction(Object transaction) {
    DataSourceTransactionObject txObject = (DataSourceTransactionObject) transaction;
    return txObject.hasConnectionHolder() && txObject.getConnectionHolder().isTransactionActive();
}

Because the previous SomeService call left isTransactionActive() true, the subsequent PaymentService call treated the connection as part of an existing transaction. In processCommit Spring only commits when status.isNewTransaction() is true, so the actual JDBC connection.commit() was skipped:

private void processCommit(DefaultTransactionStatus status) throws TransactionException {
    // …
    if (status.isNewTransaction()) {
        // real DB commit
        doCommit(status);
    }
    // else: nothing is done
    // …
}

Non‑Deterministic Behavior

TransactionSynchronizationManager

is implemented with ThreadLocal. Each thread holds its own resources. If a request was handled by a thread that had a clean connection, the insert succeeded; if the thread reused the polluted connection, the insert failed. This explains the intermittent success.

Fix Implemented

The missing commit was added before the early return and a proper try/catch with rollback was introduced:

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

After redeployment the payment function worked consistently.

Preventive Measures

Connection‑pool health checks : configure a validation query and reset autocommit on each checkout.

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

Database‑level monitoring : alert on long‑running transactions (>30 s) and lock waits.

-- Find transactions running longer than 30 seconds
SELECT * FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30;

Explicit transaction handling : ensure commit is in the try block’s end and rollback in the catch block.

Full‑stack monitoring : monitor slow queries, long transactions, lock waits, and connection counts, not only application logs.

Debug‑first approach : when encountering mysterious issues, set breakpoints on getTransaction and inspect the isExistingTransaction logic.

Key Takeaways

Connection pools reuse connection state; an uncommitted transaction can pollute the pool and affect unrelated services.

Manual transaction code must always include both commit and rollback paths.

Application‑level logs may appear normal while the database layer holds the real problem; database‑level alerts are essential.

Understanding Spring’s transaction flow ( getTransaction → doGetTransaction → isExistingTransaction → processCommit) is crucial for diagnosing similar issues.