11. DB connection pool

 

DB connection pool

Where the Connection Pool Lives

• MySQL does not have a built-in connection pool.
• Spring Boot (via HikariCP) manages the connection pool on the application side.

How It Works

1. When your app starts, HikariCP opens a set of real connections to MySQL (e.g., 10).

2. When a query runs, Spring Boot:

•    Takes a connection from the HikariCP pool
•   Runs the query using that connection
•    Returns the connection to the pool (not closed)

3. These connections are reused for other queries.

Spring automatically closes the connection at the end of the transaction or method scope.

This is handled by: AOP proxies, @Transactional boundary (explained below)

Important: Spring doesn’t actually close the connection — it returns it to the pool.

Connection vs Session

HikariCP manages physical database connections.

Each connection maps to one database session (in MySQL, PostgreSQL, etc.).

When Hikari reuses a connection, it also reuses the associated session — unless the database or app resets it.   

Important Annotation: `@Transactional`

@Service
class UserService {
    @Transactional
    public void createUser() {
        // gets a connection from pool
        // executes DB operations
        // releases connection at method end
    }
}

 Ensures:

•    The method runs in a single transaction
•    Connection is reused properly
•    Rollback happens if an error occurs 

Without `@Transactional`, Spring may not manage the connection properly — leading to resource leaks or improper commits. 

@Service
class UserService {
    @Transactional
    public void m() {
        // SELECT data A
        // UPDATE data A
        // SELECT data A again
        // Business logic
    }
}

 

What happens If we have used @Transactional:

Spring opens one database connection from the pool and then It starts a single transaction.

All DB operations (SELECT, UPDATE, DELETE) use that same connection and same transaction context.

At the end:

• If everything is fine → commits the transaction.
• If any exception occurs → rolls back the entire transaction.

Connection is then returned to the pool.

 

Important Note

Others (outside this transaction) won’t see your updates until you commit.

But you (inside this method) will see all changes you made, even before commit. This means if you update a value and then fetch it again within the same transaction, you'll get the updated value—even though it's not committed..

But If we have not used @Transactional:

Each DB operation may get its own connection (depending on how they're executed)

Each operation is auto-committed by default (in JDBC, autocommit = true).So UPDATE and DELETE will commit immediately after executing.

There’s no rollback across operations if something fails later.

 

 

Connection Pool Monitoring: Spring Boot exposes metrics with Actuator:

http://localhost:8080/actuator/metrics/hikaricp.connections.active

 

Important Tips

MySQL Limits: MySQL has a setting called `max_connections` (default = 151).You can change it:

Temporarily: SET GLOBAL max_connections = 300; 

Permanently: via `my.cnf` or `my.ini` config file

 

Don’t make your HikariCP pool size larger than MySQL’s `max_connections`.

Too many MySQL connections = more memory used = can slow down or crash the server.

Use connection pooling to avoid opening/closing a connection on every query.

Spring Boot doesn’t manage the pool manually — it uses a pooling library like:HikariCP (default), C3P0, Apache DBCP2

Since Spring Boot 2.x, HikariCP is the default connection pool.