Why a 5 ms SQL Query Holds a DB Connection for 500 ms – The Hidden Transaction Issue

While troubleshooting an API, the author noticed that the endpoint and the SQL query were both fast, yet the HikariCP connection pool quickly reached its limit. The initial suspicion was that the pool size was too small, leading many to increase maximum-pool-size, but the real cause lay elsewhere.

The root problem is that code inside a @Transactional method can hold a database connection before any SQL is executed. For example, a method that validates the request, performs risk checks, and makes remote calls may take several hundred milliseconds, while the actual orderRepository.save(...) runs only for a few milliseconds. During this time the transaction has already obtained a connection, so the connection remains occupied.

Spring Boot 4.1 introduces a new property spring.datasource.connection-fetch: lazy. When enabled, Spring wraps the auto‑configured DataSource with LazyConnectionDataSourceProxy, delaying the physical connection acquisition until the first JDBC Statement is created.

Before the change the flow was:

@Transactional
  ↓
  get Connection
  ↓
  business logic (validation, remote calls, cache checks)
  ↓
  execute SQL

After enabling lazy fetching the flow becomes:

@Transactional
  ↓
  create Connection proxy
  ↓
  business logic (validation, remote calls, cache checks)
  ↓
  prepare to execute SQL
  ↓
  obtain real Connection from HikariCP
  ↓
  execute SQL

A small demo project (Spring Boot 4.1.1, HikariCP with maximum-pool-size: 10) defines a product table and uses JdbcTemplate directly. The service method is annotated with @Transactional(readOnly = true) and deliberately sleeps for 500 ms before calling productRepository.findById(id) to simulate cache checks, RPC calls, and other pre‑SQL work.

A HikariPoolProbe component prints active, idle, total connections and waiting threads via HikariPoolMXBean. By toggling the configuration between connection-fetch: eager (default) and connection-fetch: lazy, the author shows that with eager fetching the active connection count rises immediately when the transaction starts, whereas with lazy fetching it stays unchanged until the query runs.

Concurrent load is generated with a simple shell command ( seq 1 50 | xargs -P50 curl …). Monitoring HikariCP metrics, MySQL Threads_connected, and SQL QPS reveals that many requests can occupy connections even when the database work is negligible, because the connection is held for the whole transaction duration.

The article also highlights “empty transactions”: methods annotated with @Transactional that return cached data without executing any SQL still acquire a connection under eager fetching, wasting pool resources. Lazy fetching prevents this by only obtaining a connection when a JDBC statement is actually needed.

However, lazy fetching does not solve overly large transaction scopes. If a transaction performs a remote call or a long Thread.sleep after the first SQL, the connection remains held for the entire duration. The recommended practice is to keep transaction boundaries tight—only wrap the database‑related logic—and split business logic into separate services when necessary.

Projects that benefit most from the lazy‑fetch setting are those with many @Transactional methods, high cache‑hit rates, frequent RPC/HTTP calls, and a connection pool that often shows pending threads while the database itself is not busy.

In summary, before enlarging the HikariCP pool, examine where connections are acquired. Enabling spring.datasource.connection-fetch: lazy in Spring Boot 4.1 provides a low‑cost safeguard that postpones connection acquisition, reducing unnecessary pool pressure without code changes or new frameworks.