Definition
- Connection holding -- a
@Transactionalmethod checks out a connection from HikariCP at begin and does not release it until commit or rollback, tying the connection to the entire method body. - Idle waste -- if you make a 3-second REST call inside the TX, the connection sits idle on the database side but is counted as in-use by the pool.
- Thread-pool mismatch -- 200 Tomcat worker threads competing for 10 HikariCP connections means at most 10 threads do DB work at once while 190 block inside
getConnection(). - False bottleneck -- SQL runs in 5 ms but latency reaches 500 ms because the time is spent waiting for a free connection, not running queries.
- Wrong fix -- increasing the pool to 200 pushes 200 concurrent sessions onto the database, overwhelming its CPU and lock manager.
At National Bank of India, each teller window has its own cash drawer (connection). Rajesh is at a window processing a transfer for Priya, but in the middle he picks up the phone and makes a 3-minute call to an insurance company. His window is blocked the whole time even though the cash drawer is idle. Meanwhile, 190 customers stand in line. The cash drawers are fine, the vault is fast, but service is terrible because windows are occupied by people on the phone. Adding more windows (connections) just means more tellers on the phone at once, overwhelming the vault.
Understanding the Concepts
What is connection pool exhaustion?
Connection pool exhaustion happens when every connection in the pool is checked out and no connection is available for new requests. In Spring applications that use HikariCP, a @Transactional method acquires a connection at the start of the transaction and holds it until the transaction commits or rolls back. If the method body includes slow work that is not database work, the connection is held idle, and other threads waiting for a connection are blocked.
The diagnostic sign is the HikariCP log message: "Connection is not available, request timed out after 30000ms." The metric hikaricp.connections.pending being greater than zero confirms threads are waiting.
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class TransferService {
private final AccountRepository accountRepo;
private final ExternalPaymentClient paymentClient;
public TransferService(AccountRepository accountRepo,
ExternalPaymentClient paymentClient) {
this.accountRepo = accountRepo;
this.paymentClient = paymentClient;
}
// BAD: connection held during the entire REST call
@Transactional
public void transferWithExternalCall(String fromId, String toId, BigDecimal amount) {
accountRepo.debit(fromId, amount); // 5 ms
paymentClient.notifyPartner(toId); // 3000 ms -- connection idle!
accountRepo.credit(toId, amount); // 5 ms
}
}
The connection is held for 3010 ms total, but only 10 ms is actual database work. The remaining 3000 ms is wasted.
Why does a slow network call inside a TX cause exhaustion?
A @Transactional method binds a connection to the current thread via TransactionSynchronizationManager. The connection stays bound until the transaction completes. Any non-DB work in the middle -- a REST call, an SMTP send, a file upload -- does not use the connection, but the connection cannot be returned to the pool because the transaction is still open.
With 20 connections and each transaction holding a connection for 3 seconds due to a REST call, the maximum throughput of the pool is 20 divided by 3, which equals roughly 7 requests per second. If your traffic exceeds that, threads start queuing.
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class StatementService {
private final AccountRepository accountRepo;
private final PdfGeneratorClient pdfClient;
public StatementService(AccountRepository accountRepo,
PdfGeneratorClient pdfClient) {
this.accountRepo = accountRepo;
this.pdfClient = pdfClient;
}
// BAD: PDF generation is a 5-second call holding the connection
@Transactional
public void generateStatement(String accountId) {
var txns = accountRepo.findRecentTransactions(accountId); // 10 ms
pdfClient.generate(txns); // 5000 ms -- connection wasted
accountRepo.markStatementGenerated(accountId); // 5 ms
}
}
Timeline for one request:
0 ms -- connection acquired
10 ms -- SELECT completes
5010 ms -- PDF generation completes (connection idle for 5000 ms)
5015 ms -- UPDATE completes
5015 ms -- connection released
Why does 200 Tomcat threads + 10 HikariCP connections equal slow?
Tomcat handles incoming HTTP requests with a thread pool, typically configured at 200 threads. HikariCP manages database connections, often set to 10 or 20. When 200 requests arrive simultaneously, at most 10 can acquire a connection. The other 190 threads block inside HikariDataSource.getConnection() waiting for a connection to become available.
The SQL itself runs in 5 milliseconds, but the user-visible latency is dominated by connection wait time. If each thread holds a connection for 100 ms, a thread at the back of the queue might wait 1900 ms before getting a connection.
package com.bank.config;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.hikari")
public HikariDataSource dataSource() {
HikariDataSource ds = new HikariDataSource();
ds.setMaximumPoolSize(10); // only 10 connections
ds.setConnectionTimeout(30000); // wait 30s before timeout
ds.setMetricRegistry(meterRegistry); // expose pending count
return ds;
}
}
# application.yml showing the mismatch
server:
tomcat:
max-threads: 200 # 200 request threads
spring:
datasource:
hikari:
maximum-pool-size: 10 # only 10 connections
connection-timeout: 30000
Result when 200 requests arrive at once:
10 threads -- executing SQL (5 ms each)
190 threads -- blocked in getConnection()
Avg latency -- 500 ms (495 ms waiting, 5 ms SQL)
HikariCP log -- "Connection is not available, request timed out"
Why is increasing the pool size the wrong fix?
The instinctive reaction is to increase maximum-pool-size from 10 to 200 so every thread gets a connection. This is wrong because it moves the bottleneck from the application to the database. A PostgreSQL server with 4 CPU cores can efficiently handle 10 to 20 concurrent sessions. At 200 concurrent sessions the database spends more time on context switching, lock contention, and buffer pool pressure than on actual query execution.
The HikariCP documentation quotes the PostgreSQL team: "a formula of connections = (core_count * 2) + effective_spindle_count" is often optimal. For a 4-core server with SSD, that is roughly 10 connections.
package com.bank.config;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class WrongFixConfig {
// WRONG: matching pool to Tomcat threads
@Bean
public HikariDataSource dataSource() {
HikariDataSource ds = new HikariDataSource();
ds.setMaximumPoolSize(200); // 200 concurrent DB sessions!
return ds;
}
// Result: DB CPU spikes, lock contention rises,
// queries that took 5 ms now take 200 ms
}
Before (10 connections):
App latency: 500 ms (495 ms wait + 5 ms SQL)
DB CPU: 15%
After (200 connections):
App latency: 200 ms (0 ms wait + 200 ms SQL)
DB CPU: 95% -- thrashing
You traded "app slow" for "DB slow"
What is the correct fix?
The correct approach has four parts: keep transactions short with only DB operations inside them, move external calls outside the transaction, cache hot data, and size the pool to the database's real capacity.
package com.bank.service;
import org.springframework.context.ApplicationEventPublisher;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class TransferServiceFixed {
private final AccountRepository accountRepo;
private final ApplicationEventPublisher eventPublisher;
public TransferServiceFixed(AccountRepository accountRepo,
ApplicationEventPublisher eventPublisher) {
this.accountRepo = accountRepo;
this.eventPublisher = eventPublisher;
}
// CORRECT: TX contains only DB work
@Transactional
public void transfer(String fromId, String toId, BigDecimal amount) {
accountRepo.debit(fromId, amount); // 5 ms
accountRepo.credit(toId, amount); // 5 ms
// Publish event -- external call happens AFTER commit
eventPublisher.publishEvent(
new TransferCompletedEvent(fromId, toId, amount));
}
// Connection held for only 10 ms
// External notification runs in @TransactionalEventListener(AFTER_COMMIT)
}
Before fix: connection held 3010 ms (3000 ms idle)
After fix: connection held 10 ms
Throughput: 10 connections / 0.01s = 1000 req/s (was 7 req/s)
How do you diagnose connection pool exhaustion?
HikariCP exposes metrics through Micrometer that reveal the problem. The key metrics are hikaricp.connections.pending (threads waiting for a connection), hikaricp.connections.active (connections currently in use), and hikaricp.connections.usage (how long each connection is checked out).
package com.bank.monitoring;
import io.micrometer.core.instrument.MeterRegistry;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
@Component
public class ConnectionPoolMonitor {
private final MeterRegistry meterRegistry;
public ConnectionPoolMonitor(MeterRegistry meterRegistry) {
this.meterRegistry = meterRegistry;
}
@Scheduled(fixedRate = 10000)
public void logPoolMetrics() {
double pending = meterRegistry.get("hikaricp.connections.pending")
.gauge().value();
double active = meterRegistry.get("hikaricp.connections.active")
.gauge().value();
if (pending > 0) {
System.out.println("WARNING: " + (int) pending
+ " threads waiting for a connection. "
+ (int) active + " connections active.");
}
}
}
Healthy output:
hikaricp.connections.pending = 0
hikaricp.connections.active = 3
Exhausted output:
WARNING: 47 threads waiting for a connection. 10 connections active.
What are common mistakes?
The most common mistake is placing an external HTTP call inside a @Transactional method. The second most common mistake is using a transaction where none is needed, such as wrapping a read-only query in a write transaction.
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class LoanServiceMistakes {
private final LoanRepository loanRepo;
private final CreditBureauClient creditClient;
public LoanServiceMistakes(LoanRepository loanRepo,
CreditBureauClient creditClient) {
this.loanRepo = loanRepo;
this.creditClient = creditClient;
}
// WRONG: credit check is a 2-second HTTP call inside TX
@Transactional
public void applyForLoan(String customerId, BigDecimal amount) {
var application = loanRepo.createApplication(customerId, amount);
boolean approved = creditClient.checkScore(customerId); // 2000 ms!
if (approved) {
loanRepo.approve(application.getId());
}
}
// CORRECT: split into two methods
public void applyForLoanFixed(String customerId, BigDecimal amount) {
var application = createApplication(customerId, amount); // TX 1
boolean approved = creditClient.checkScore(customerId); // no TX
if (approved) {
approveLoan(application.getId()); // TX 2
}
}
@Transactional
public Long createApplication(String customerId, BigDecimal amount) {
return loanRepo.createApplication(customerId, amount).getId();
}
@Transactional
public void approveLoan(Long applicationId) {
loanRepo.approve(applicationId);
}
}
WRONG version: connection held 2010 ms (2000 ms idle)
CORRECT version: connection held 5 ms + 5 ms = 10 ms total
What should a senior engineer know?
A senior engineer should understand the relationship between pool size, database capacity, and application throughput. The optimal pool size is driven by the database, not by the application's thread count. HikariCP's formula for PostgreSQL is connections = (core_count * 2) + effective_spindle_count.
Senior engineers also know that @Transactional(readOnly = true) on read-only methods avoids acquiring a write connection, and that Spring's LazyConnectionDataSourceProxy defers connection acquisition until the first SQL statement, which helps when a transactional method sometimes exits early without touching the database.
package com.bank.config;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class LazyConnectionConfig {
@Bean
public DataSource lazyDataSource(DataSource actualDataSource) {
// Connection is not acquired until first SQL statement
return new LazyConnectionDataSourceProxy(actualDataSource);
}
}
What are common interview follow-up questions?
- How do you choose the right pool size for PostgreSQL vs MySQL?
- What is the difference between
connectionTimeoutandidleTimeoutin HikariCP? - How does
LazyConnectionDataSourceProxyhelp with pool exhaustion? - What happens when
connectionTimeoutexpires and no connection is available? - How do you monitor connection pool health in production using Micrometer and Grafana?
Visual Overview
200 Tomcat Threads HikariCP Pool (10 connections)
┌─────────────┐ ┌──────────────────────┐
│ Thread 1 │────acquire────────▶│ Connection 1 [busy] │
│ Thread 2 │────acquire────────▶│ Connection 2 [busy] │
│ Thread 3 │────acquire────────▶│ Connection 3 [busy] │
│ ... │────acquire────────▶│ ... │
│ Thread 10 │────acquire────────▶│ Connection 10 [busy] │
│ Thread 11 │────WAIT ──────────▶│ (none available) │
│ Thread 12 │────WAIT ──────────▶│ (none available) │
│ ... │ │ │
│ Thread 200 │────WAIT ──────────▶│ (none available) │
└─────────────┘ └──────────────────────┘
│
▼
190 threads blocked in getConnection()
SQL = 5 ms, but user sees 500 ms
WRONG: TX wraps external call ┌──────────────────────────────────────────────┐ │ @Transactional │ │ ┌──────────┐ ┌──────────────┐ ┌────────┐ │ │ │ debit() │ │ REST call │ │credit()│ │ │ │ 5 ms │ │ 3000 ms │ │ 5 ms │ │ │ └──────────┘ └──────────────┘ └────────┘ │ │ Connection held: 3010 ms │ └──────────────────────────────────────────────┘ CORRECT: TX contains only DB work ┌─────────────────────────┐ │ @Transactional │ ┌──────────────────────┐ │ ┌──────┐ ┌──────┐ │ │ AFTER_COMMIT listener │ │ │debit │ │credit│ │──▶│ REST call: 3000 ms │ │ │ 5 ms │ │ 5 ms │ │ └──────────────────────┘ │ └──────┘ └──────┘ │ │ Connection held: 10ms │ └─────────────────────────┘
Key Takeaway
A database connection is held for the entire duration of a @Transactional method. If you place a 3-second REST call inside the transaction, the connection sits idle on the database side for 3 seconds while being counted as in-use by the pool. With 10 connections and 3-second calls, maximum throughput drops to roughly 3 requests per second. The classic symptom is the "DB fast but app slow" mystery: SQL takes 5 milliseconds but the user sees 500 milliseconds because 495 milliseconds is spent waiting for a free connection from an exhausted pool.
The instinctive fix of increasing the pool size is wrong. Matching the pool to 200 Tomcat threads pushes 200 concurrent sessions onto the database, overwhelming its CPU and lock manager. You trade "app slow" for "DB slow." The HikariCP documentation quotes the PostgreSQL team's formula: connections equal core count times two plus effective spindle count, which often lands at 10 to 20 connections.
The correct fix is keeping transactions short with only database operations inside them. Move external calls outside the transaction using Spring events or the outbox pattern. Use LazyConnectionDataSourceProxy to defer connection acquisition until the first SQL statement. Monitor hikaricp.connections.pending in Grafana and alert when threads start waiting.
Remember one sentence: keep transactions short and network-free, because the connection is held for the entire method, not just the SQL.
---