Why does an app report "connection pool exhausted" while the database sits mostly idle?

package com.bank.service;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class LoanApprovalService {

    private final LoanRepository loanRepository;
    private final CreditBureauClient creditBureauClient;

    public LoanApprovalService(LoanRepository loanRepository,
                                CreditBureauClient creditBureauClient) {
        this.loanRepository = loanRepository;
        this.creditBureauClient = creditBureauClient;
    }

    // PROBLEM: @Transactional holds the connection for the ENTIRE method
    @Transactional
    public void approveLoan(String accountId, BigDecimal amount) {
        // Step 1: Database work (10ms) - connection is doing real work
        loanRepository.createLoanApplication(accountId, amount);

        // Step 2: External HTTP call (2-5 seconds!) - connection is IDLE
        int creditScore = creditBureauClient.getScore(accountId);
        // The connection is held inside an open transaction
        // doing NOTHING on the database for 2-5 seconds

        // Step 3: Database work (10ms) - connection is doing real work again
        loanRepository.updateLoanWithScore(accountId, creditScore);
    }
}

interface LoanRepository {
    void createLoanApplication(String accountId, BigDecimal amount);
    void updateLoanWithScore(String accountId, int score);
}

interface CreditBureauClient {
    int getScore(String accountId);
}

Timeline with pool size 10 and 20 concurrent loan approvals:

Time 0.0s: Requests 1-10 borrow connections C1-C10
Time 0.01s: Each does 10ms DB work (create loan)
Time 0.01s: Each starts credit bureau HTTP call (holds 3 seconds)

Time 0.01s: Requests 11-20 arrive, need connections
            Pool is empty! All 10 connections held by HTTP calls
            Requests 11-20 wait...

Time 3.01s: Requests 1-10 finish HTTP call, do 10ms DB work
Time 3.02s: Requests 1-10 return connections
            Requests 11-20 finally get connections (3 seconds late)

Actual DB work per request: 20ms
Connection hold time per request: 3020ms (150x longer than needed)

-- Find sessions that are idle inside a transaction
SELECT pid,
       state,
       now() - state_change AS idle_duration,
       query AS last_query,
       application_name
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_duration DESC;

-- Expected output during the problem:
-- pid  | state               | idle_duration | last_query
-- 1234 | idle in transaction | 00:00:03.2    | INSERT INTO loan_applications ...
-- 1235 | idle in transaction | 00:00:02.8    | INSERT INTO loan_applications ...
-- 1236 | idle in transaction | 00:00:02.5    | INSERT INTO loan_applications ...
-- All connections sitting idle after the INSERT, waiting for the HTTP call

-- Set the timeout (PostgreSQL 9.6+)
ALTER DATABASE nbi_ledger
    SET idle_in_transaction_session_timeout = '10s';

-- What happens when a transaction is idle for 10 seconds:
-- 1. PostgreSQL terminates the backend
-- 2. The transaction is rolled back
-- 3. The connection is freed on the server side
-- 4. The application receives an error:
--    PSQLException: terminating connection due to idle-in-transaction timeout
-- 5. HikariCP detects the dead connection and removes it from the pool

package com.bank.config;

public class TimeoutConfig {

    // Database-side safety nets:
    // idle_in_transaction_session_timeout = 10s
    //   → kills transactions idle inside a transaction for > 10 seconds
    // statement_timeout = 5s
    //   → kills individual queries running longer than 5 seconds

    // Application-side safety nets:
    // HikariCP leakDetectionThreshold = 5000
    //   → warns if a connection is held > 5 seconds
    // @Transactional(timeout = 3)
    //   → rolls back if the transaction exceeds 3 seconds
}

package com.bank.service;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class LoanApprovalServiceFixed {

    private final LoanRepository loanRepository;
    private final CreditBureauClient creditBureauClient;

    public LoanApprovalServiceFixed(LoanRepository loanRepository,
                                     CreditBureauClient creditBureauClient) {
        this.loanRepository = loanRepository;
        this.creditBureauClient = creditBureauClient;
    }

    // CORRECT: external call OUTSIDE the transaction
    public void approveLoan(String accountId, BigDecimal amount) {
        // Step 1: External call first, NO connection held
        int creditScore = creditBureauClient.getScore(accountId);

        // Step 2: Short transaction for DB work only
        saveLoanWithScore(accountId, amount, creditScore);
    }

    @Transactional(timeout = 2)  // 2 second safety timeout
    public void saveLoanWithScore(String accountId, BigDecimal amount, int score) {
        loanRepository.createLoanApplication(accountId, amount);
        loanRepository.updateLoanWithScore(accountId, score);
        // Connection held for ~20ms (two fast DB operations)
        // NOT 3 seconds (DB + HTTP call)
    }
}

package com.bank.service;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;

public class OtherCauses {

    // Cause 2: Connection leak (forgot to close)
    public void leakyMethod(DataSource ds) throws SQLException {
        Connection conn = ds.getConnection();
        conn.prepareStatement("SELECT 1").executeQuery();
        // OOPS: no conn.close()! Connection never returned to pool
        // Pool silently shrinks by 1 each time this method is called
    }

    // Cause 3: Missing commit/rollback on error path
    public void missingRollback(DataSource ds) throws SQLException {
        Connection conn = ds.getConnection();
        conn.setAutoCommit(false);
        try {
            conn.prepareStatement("INSERT INTO loans ...").executeUpdate();
            // Exception thrown here!
            throw new RuntimeException("Validation failed");
        } catch (RuntimeException e) {
            // OOPS: no conn.rollback()!
            // Transaction left open, connection idle in transaction
        }
    }

    // Cause 4: Long-running report holding a connection
    // A report query that takes 30 seconds holds its connection
    // Mitigation: run reports on a read replica with its own pool
}

Rules for transaction health:

1. Never make external HTTP calls inside @Transactional
2. Never wait on message queues inside @Transactional
3. Never do file I/O inside @Transactional
4. Always use try-with-resources for connections
5. Always commit or rollback on every code path
6. Set @Transactional(timeout = N) on every transactional method
7. Run long reports on a read replica with a separate pool
8. Enable HikariCP leak detection in non-production environments
9. Set idle_in_transaction_session_timeout as a database safety net
10. Monitor pg_stat_activity for 'idle in transaction' sessions

package com.bank.config;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class DefenseInDepthConfig {

    public DataSource createPool() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/nbi_ledger");
        config.setMaximumPoolSize(10);
        config.setConnectionTimeout(3000);
        config.setLeakDetectionThreshold(5000);  // Layer 1: app leak detection

        // Layer 2: database-level timeouts (set in PostgreSQL)
        // idle_in_transaction_session_timeout = 10s
        // statement_timeout = 5s

        return new HikariDataSource(config);
    }
}