API Latency Spike to 8s: Not Slow SQL, But Long Transactions Holding DB Connections

Incident Overview

A production API that normally responded in ~200ms suddenly exhibited latencies of 3–8 seconds. Monitoring showed HikariCP errors: Connection is not available, request timed out after 30000ms. The team initially suspected slow SQL or database issues because the endpoint performed several MySQL queries.

Initial Investigation

Slow query log review: no obvious problems.

Key SQL statements executed manually: ~10–20 ms each.

DBA checked CPU, I/O, lock waits: database was idle.

Despite healthy database metrics, the application threw connection‑timeout exceptions. The HikariCP configuration was:

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      connection-timeout: 30000

Temporary Mitigation and Its Limits

Increasing maximum-pool-size to 50 in a test environment improved throughput under moderate load, but under higher concurrency the pool still saturated. The database showed a spike in active connections, confirming that connections were not being released quickly enough.

Root Cause: Long‑Running Transaction with External Call

The problematic code was an @Transactional method that:

Queried the database for an order ( findById).

Called an external payment service ( paymentClient.query) over HTTP.

Updated the order status based on the response.

@Service
@RequiredArgsConstructor
public class OrderSyncService {
    private final OrderRepository orderRepository;
    private final PaymentClient paymentClient;

    @Transactional
    public void syncPaymentStatus(Long orderId) {
        Order order = orderRepository.findById(orderId).orElseThrow();
        PaymentResult result = paymentClient.query(order.getPaymentNo());
        if (result.isPaid()) {
            order.markPaid(result.getPaidTime());
        }
        orderRepository.save(order);
    }
}

Because the entire method ran inside a single transaction, the database connection was acquired at findById and held until the transaction committed after save. While waiting for the payment service (normally 50–100 ms, but that night 2–3 seconds), the connection sat idle in the pool.

With a pool of 20 connections, 20 concurrent requests each holding a connection for 3 seconds exhausted the pool. The 21st request timed out waiting for a connection, even though the database itself was barely loaded.

Monitoring the Connection Pool

Spring Boot Actuator metrics for HikariCP were enabled:

management:
  endpoints:
    web:
      exposure:
        include:
          - health
          - metrics
          - prometheus

Key metrics watched:

/actuator/metrics/hikaricp.connections.active

/actuator/metrics/hikaricp.connections.idle

/actuator/metrics/hikaricp.connections.pending

During the incident, active quickly hit the maximum pool size, idle dropped to near zero, and pending grew continuously — a clear signature of connections being held too long, not of a slow database.

Solution: Split the Transaction

The fix moved the external HTTP call outside the transaction boundary:

@Service
@RequiredArgsConstructor
public class OrderSyncService {
    private final OrderRepository orderRepository;
    private final PaymentClient paymentClient;
    private final OrderTransactionService transactionService;

    public void syncPaymentStatus(Long orderId) {
        Order order = orderRepository.findById(orderId).orElseThrow();
        PaymentResult result = paymentClient.query(order.getPaymentNo());
        transactionService.updatePaymentStatus(orderId, result);
    }
}

@Service
@RequiredArgsConstructor
public class OrderTransactionService {
    private final OrderRepository orderRepository;

    @Transactional
    public void updatePaymentStatus(Long orderId, PaymentResult result) {
        Order order = orderRepository.findById(orderId).orElseThrow();
        if (order.getStatus() != OrderStatus.WAIT_PAY) {
            return;
        }
        if (!result.isPaid()) {
            return;
        }
        order.markPaid(result.getPaidTime());
    }
}

Now the flow is:

Read required data (short DB access).

Call payment service (no DB connection held).

Start a short transaction, re‑read the order, update status, commit.

Handling Concurrency After the Split

Because several seconds may elapse between the initial read and the update, the order could be modified by another request. The solution re‑reads the entity inside the short transaction and checks its current state:

@Transactional
public void updatePaymentStatus(Long orderId, PaymentResult result) {
    Order order = orderRepository.findById(orderId).orElseThrow();
    if (order.getStatus() != OrderStatus.WAIT_PAY) {
        return;
    }
    if (!result.isPaid()) {
        return;
    }
    order.markPaid(result.getPaidTime());
}

The Order entity uses @Version for optimistic locking:

@Entity
public class Order {
    @Id
    private Long id;
    @Version
    private Long version;
    // ...
}

For simpler state transitions, a conditional update query avoids the re‑read entirely:

@Modifying
@Query("""
    update Order o
    set o.status = :newStatus
    where o.id = :orderId
    and o.status = :oldStatus
""")
int updateStatus(@Param("orderId") Long orderId,
                 @Param("oldStatus") OrderStatus oldStatus,
                 @Param("newStatus") OrderStatus newStatus);

Usage:

int updated = orderRepository.updateStatus(orderId, OrderStatus.WAIT_PAY, OrderStatus.PAID);
if (updated == 0) {
    log.info("订单状态已经发生变化, orderId={}", orderId);
}

Broader Codebase Audit

The author scanned other @Transactional methods and found similar patterns, e.g.:

@Transactional
public void createOrder(CreateOrderRequest request) {
    Order order = saveOrder(request);
    inventoryClient.lock(order.getId(), request.getItems());
    couponClient.useCoupon(request.getCouponId());
    messageClient.send(...);
}

Each remote call (inventory, coupon, messaging) extended the transaction duration. Under load, hundreds of milliseconds per call multiplied by hundreds of concurrent requests caused connection pool pressure.

Checklist for Transaction Scope Review

When evaluating a transaction, look for these operations inside its boundary:

HTTP calls

RPC calls

Third‑party SDKs

File uploads / object storage

Email / SMS sending

Slow local computations

These are not forbidden, but they must be justified by strict consistency requirements. The danger is gradual scope creep: a method that started with one save accumulates remote calls over years while the @Transactional annotation remains unchanged.

Leak Detection as a Diagnostic Aid

HikariCP's leak-detection-threshold was temporarily enabled:

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      connection-timeout: 3000
      leak-detection-threshold: 5000

This logs a stack trace when a connection is held longer than the threshold (5 seconds). It flags not only true leaks but also legitimate long‑holding patterns like the one above, making it a valuable troubleshooting tool. The threshold should be tuned to normal business latency to avoid noise.

Long‑Term Monitoring Strategy

Permanent dashboards now track:

Active, idle, and pending connections

Connection acquisition latency

When active approaches max and pending rises, the investigation starts with the call chain: look for HTTP/RPC inside transactions, then slow SQL, lock contention, large result sets, or genuine resource leaks.

Key Takeaway

An API jumping from 200 ms to 8 seconds does not imply the SQL slowed from 20 ms to 8 seconds. Often the SQL still takes 20 ms; the remaining 7+ seconds are spent waiting for an external service while a database connection is held hostage. The next time HikariPool - Connection is not available appears, search globally for @Transactional and examine what non‑database work lives inside those transactions.