Why Large Database Transactions Slow Your API and How to Fix Them

1. Introduction

Backend developers often implement a single API endpoint that performs a series of complex operations such as queries, remote calls, updates, inserts, and calculations. Because the whole flow runs inside one database transaction, each database interaction creates a transaction record, and the overall response time grows with data volume.

2. What Is a "Large Transaction"?

For example, the author describes an interface that generates a receivable bill based on user‑submitted data. All steps—validation, third‑party calls, status updates, and database writes—are placed in one method, resulting in a long‑running transaction that is inefficient and hard to maintain.

3. Problems Caused by Large Transactions

Concurrent data inconsistency : Without locking, a second request may modify the same data before the first request finishes, leading to stale writes when the first request finally returns.

Lock blocking : Adding a lock prevents inconsistency but can cause lock time‑outs or block many rows, dramatically reducing throughput.

Undo‑log bloat : A long transaction generates massive undo‑log entries, slowing log queries and rollback performance.

Database pressure : High concurrency on a large transaction creates a heavy read/write load, causing thread queues to build up.

4. How to Optimize Large Transactions

4.1 Avoid Remote RPC Inside a Transaction

Remote calls without a distributed‑transaction framework can cause inconsistency and make rollback impossible. The author recommends converting such calls to asynchronous operations.

4.2 Use Programmatic Transactions for Flexibility

Declarative @Transactional wraps the entire method, including read operations, which is often unnecessary. By using TransactionTemplate, only the write steps are enclosed, allowing reads to execute outside the transaction.

public Boolean transactionCommit(String userName) {
    // Query user
    SysUser sysUser = userMapper.selectUserByUserName(userName, null);

    transactionTemplate.execute(new TransactionCallbackWithoutResult() {
        @Override
        protected void doInTransactionWithoutResult(TransactionStatus transactionStatus) {
            try {
                if (sysUser != null) {
                    // Update status to 1
                    userMapper.updateStatus(userName);
                }
            } catch (Exception e) {
                // Rollback
                transactionStatus.setRollbackOnly();
            }
        }
    });
    // Query again
    SysUser sysUser1 = userMapper.selectUserByUserName(userName, "1");
    log.info("User with status 1: " + JSON.toJSONString(sysUser1));
    return true;
}

The programmatic approach lets the initial query run outside the MySQL transaction table, reducing lock time.

4.3 Batch Data Processing

When the front end sends bulk data, splitting it into smaller batches limits each transaction’s size. For example, processing 50 records per batch with MyBatis batch updates dramatically improves throughput.

List<List<ReceivableFeeSaveDTO>> partition = Lists.partition(receivableFeeSaveDTOList, 50);

4.4 Split a Large Transaction into Multiple Small Ones

Each logical step—such as amount write‑back, third‑party call, and result persistence—can be extracted into its own tiny transaction. This mirrors breaking a complex task into sequential, manageable phases.

4.5 Asynchronous Parallel Processing

If remote calls cannot be avoided, they should be executed asynchronously. Using CompletableFuture, two independent tasks run in parallel, and a third task combines their results once both finish.

CompletableFuture<Object> task1 = CompletableFuture.supplyAsync(() -> {
    System.out.println("Check order thread " + Thread.currentThread().getId());
    // Validate order, throw on failure
    return "Order entity";
}, executor);

CompletableFuture<Object> task2 = CompletableFuture.supplyAsync(() -> {
    System.out.println("Generate receipt thread " + Thread.currentThread().getId());
    try {
        // Simulate receipt generation
        Thread.sleep(3000);
        return "Receipt ID";
    } catch (InterruptedException e) {
        e.printStackTrace();
        return null;
    }
}, executor);

CompletableFuture<Boolean> future = task1.thenCombineAsync(task2, (t1, t2) -> {
    System.out.println("Write back amount thread " + Thread.currentThread().getId());
    // Combine results and write back
    return true;
}, executor);

This pattern prevents the main transaction from being blocked by slow remote services while still guaranteeing eventual consistency.

5. Conclusion

Large transactions are a primary cause of API latency. By avoiding remote calls inside transactions, adopting programmatic transaction control, batching data, splitting the work into smaller transactions, and leveraging asynchronous parallelism, developers can dramatically improve response times and system scalability.