Why Did My Payment Service Lose Data? Uncovering Hidden Transaction Bugs in Spring

Incident Overview

During a recent deployment the online payment service succeeded in charging users, but the corresponding order rows were not inserted into the database. Occasionally lock‑wait timeouts were also observed on the order table.

Abnormal Symptoms

Business code executed normally without any exception or error log.

Log shows transaction committed but the data is missing in the DB.

Most attempts fail while a few succeed sporadically.

Emergency Handling

The quickest way to restore service was to restart the application, which forced the stale connections to be released. After the restart the payment flow worked, but the root cause still needed to be identified.

Reviewing the recent releases revealed a new service that opened a transaction, performed an insert, and returned early without committing.

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

Adding the missing commit and redeploying fixed the immediate symptom.

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

Root Cause Analysis

The missing commit in the above service polluted the connection held by Spring’s TransactionSynchronizationManager. Because the connection’s isTransactionActive() flag remained true, subsequent requests that obtained the same connection were treated as part of an existing transaction.

Key Spring Methods

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

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

If isExistingTransaction returns true, Spring assumes an existing transaction and skips creating a new one. The processCommit method then checks status.isNewTransaction(); when it is false the actual connection.commit() is never executed.

private void processCommit(DefaultTransactionStatus status) throws TransactionException {
    if (status.isNewTransaction()) {
        // real DB commit
        doCommit(status);
    } else {
        // not a new transaction, nothing happens
    }
    cleanupAfterCompletion(status);
}

Because the connection was returned to the pool with the transaction flag still set, the next request (e.g., PaymentService.createOrder) fetched the same polluted connection, was considered part of the previous transaction, and the commit was skipped. The order never reached the database, and the polluted connection remained in the manager, affecting later requests.

Why It Was Intermittent

Spring stores the ConnectionHolder in a ThreadLocal. If a request runs on a clean thread, it receives an unpolluted connection and works correctly; if it runs on a thread that previously handled the buggy service, it inherits the stale transaction state and fails.

Prevention Measures

Connection‑pool health checks – configure the pool to validate and reset connections before use:

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

Monitor long‑running transactions – alert on transactions longer than a threshold:

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

Explicit transaction handling – always place commit in the try block, rollback in the catch block, and resource cleanup in a finally block.

Debug source code – set breakpoints in getTransaction and isExistingTransaction to verify the actual flow and connection state.

Takeaways

Connection pools can propagate transaction state; they are not just a performance optimization.

Always commit or roll back manually managed transactions.

Database‑level monitoring is essential because application logs may appear normal.

When encountering obscure bugs, use a debugger to inspect the transaction lifecycle rather than relying solely on logs.