How to Split and Optimize Large Database Transactions for Faster APIs

Introduction

As backend developers we often write APIs that involve complex business logic such as queries, remote calls, updates, inserts, and calculations. When these operations are wrapped in a single database transaction, the execution time grows with data volume and degrades API response efficiency.

What Is a Large Transaction?

For example, an interface that generates a receipt based on submitted data may contain many steps—validation, remote service calls, database inserts, and status updates—all placed in one transaction, making the transaction long and inefficient.

Problems Caused by Large Transactions

Inconsistent Concurrent Data

Without locking, a second request may modify data before the first request finishes, causing the first request to write stale information.

Locking Leads to Blocking

Locking prevents inconsistency but long‑running transactions can cause lock timeouts and block other operations, severely hurting performance.

Undo Log Overhead

Large transactions generate massive undo logs, slowing log queries and rollback speed.

Database Pressure

High concurrency puts heavy read/write load on the database, creating many waiting threads.

How to Optimize Large Transactions

Avoid Remote RPC Inside Transactions

Remote calls inside a transaction without a distributed‑transaction framework can cause inconsistency and slow responses; use asynchronous calls instead.

Programmatic Transactions for Flexibility

Declarative @Transactional applies to the whole method, which may be too broad. Using TransactionTemplate allows you to wrap only the necessary update/insert operations.

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;
}

Programmatic transactions let you keep read operations outside the database transaction.

Batch Data Processing

Split large insert/update batches into smaller chunks (e.g., 50 records per batch) to reduce transaction size and improve throughput.

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

Split a Large Transaction into Smaller Ones

Break a monolithic transaction into several focused services—e.g., separate receipt generation, amount write‑back, and third‑party calls—so each handles a single responsibility.

Asynchronous Parallel Processing

If remote calls cannot be avoided, execute them asynchronously. CompletableFuture can orchestrate parallel tasks and combine results after all complete.

CompletableFuture<Object> task1 = CompletableFuture.supplyAsync(() -> {
    System.out.println("Order check thread " + Thread.currentThread().getId());
    return "Bill entity";
}, executor);
CompletableFuture<Object> task2 = CompletableFuture.supplyAsync(() -> {
    System.out.println("Receipt generation thread " + Thread.currentThread().getId());
    try {
        Thread.sleep(3000);
        System.out.println("Task2 finished");
        return "Bill number";
    } catch (InterruptedException e) {
        e.printStackTrace();
        return null;
    }
}, executor);
CompletableFuture<Boolean> future = task1.thenCombineAsync(task2, (t1, t2) -> {
    System.out.println("Amount write‑back thread " + Thread.currentThread().getId());
    return true;
}, executor);

Conclusion

Large transactions are a major cause of API inefficiency. Recognizing and refactoring them—by reducing scope, batching, splitting, and using asynchronous processing—can dramatically improve performance and scalability.