The labs on this site need JavaScript to run. The same content is written out below.

Learn › Spring › Data access and transactions

Connection pool and OSIV

Why connections run short even though the queries finish quickly

A request does its database work, then calls an external API, and then responds. Database connections are borrowed from a pool and returned to it. Depending on when a connection is returned (the Open Session in View setting, OSIV for short), the number of requests that a pool of the same size can withstand differs greatly.

Checked against documentation and standards · The default behavior of HikariCP and Hibernate (the numbers are a model)

Concepts in this lab

Connection pool

A technique that creates several database connections in advance and reuses them. Opening a new connection is an expensive operation, so instead of opening and closing a connection for every request, the application borrows one and returns it. If every connection is in use, a new request waits until one is returned, and an exception is thrown if it cannot get one within a set time (connectionTimeout in HikariCP, 30 seconds by default). The default implementation in Spring Boot is HikariCP.

Open Session in View

A setting that keeps the persistence context open after the transaction ends, until request processing finishes (the response is written). It is convenient because lazy fetching also works in controllers and views. However, after the first database access during a request, the request keeps holding the database connection until the response is finished, so a service with many requests can run short of connections. It is on by default in Spring Boot.

Persistence context

The logical space in JPA that holds and manages entities. Entities that you load or save are kept here, and JPA tracks the changes made to them and applies those changes to the database when the transaction ends. It usually shares its lifecycle with one transaction (in a web request with Open Session in View (OSIV) turned on, it lasts until the request ends). Features such as the first-level cache, dirty checking, and transactional write-behind work on top of it.

Bottleneck

The slowest part of a system, which limits the processing performance of the whole. No matter how much you improve the other parts, overall performance does not improve while this part stays the same. Performance improvement starts with finding it by measurement.

Try these

• Default settings — OSIV on. The database work takes 0.5s, but each request holds a connection for 2.5s.
• Turning OSIV off — The connection is returned as soon as the database work ends.
• When more requests arrive — OSIV on, 20 requests. Some requests wait 5s and then fail.
• Making the pool larger — The same 20 requests with a pool of 10. The pool holds up, but there is a limit to the number of connections the database can accept.
• When the query itself is slow — Even with OSIV off, a connection is in use for the 2s of database work.

Running with the default settings

1. 0.0s: Request 1 borrows connection 1 and starts its database work.
2. 0.1s: Request 2 borrows connection 2 and starts its database work.
3. 0.2s: Request 3 borrows connection 3 and starts its database work.
4. 0.3s: Request 4 arrives. All connections in the pool (3) are in use, so it waits.
5. 0.4s: Request 5 arrives. All connections in the pool (3) are in use, so it waits.
6. 0.5s: Request 1 finishes its database work and starts the external API call. OSIV is on, so it does not return connection 1 and holds it until the response is finished.
7. 0.5s: Request 6 arrives. All connections in the pool (3) are in use, so it waits.
8. 0.6s: Request 2 finishes its database work and starts the external API call. OSIV is on, so it does not return connection 2 and holds it until the response is finished.
9. 0.6s: Request 7 arrives. All connections in the pool (3) are in use, so it waits.
10. 0.7s: Request 3 finishes its database work and starts the external API call. OSIV is on, so it does not return connection 3 and holds it until the response is finished.
11. 0.7s: Request 8 arrives. All connections in the pool (3) are in use, so it waits.
12. 2.5s: Request 1 finishes its response. It now returns connection 1.
13. 2.5s: Request 4 borrows connection 1 and starts its database work. It waited 2.2s.
14. 2.6s: Request 2 finishes its response. It now returns connection 2.
15. 2.6s: Request 5 borrows connection 2 and starts its database work. It waited 2.2s.
16. 2.7s: Request 3 finishes its response. It now returns connection 3.
17. 2.7s: Request 6 borrows connection 3 and starts its database work. It waited 2.2s.
18. 3.0s: Request 4 finishes its database work and starts the external API call. OSIV is on, so it does not return connection 1 and holds it until the response is finished.
19. 3.1s: Request 5 finishes its database work and starts the external API call. OSIV is on, so it does not return connection 2 and holds it until the response is finished.
20. 3.2s: Request 6 finishes its database work and starts the external API call. OSIV is on, so it does not return connection 3 and holds it until the response is finished.
21. 5.0s: Request 4 finishes its response. It now returns connection 1.
22. 5.0s: Request 7 borrows connection 1 and starts its database work. It waited 4.4s.
23. 5.1s: Request 5 finishes its response. It now returns connection 2.
24. 5.1s: Request 8 borrows connection 2 and starts its database work. It waited 4.4s.
25. 5.2s: Request 6 finishes its response. It now returns connection 3.
26. 5.5s: Request 7 finishes its database work and starts the external API call. OSIV is on, so it does not return connection 1 and holds it until the response is finished.
27. 5.6s: Request 8 finishes its database work and starts the external API call. OSIV is on, so it does not return connection 2 and holds it until the response is finished.
28. 7.5s: Request 7 finishes its response. It now returns connection 1.
29. 7.6s: Request 8 finishes its response. It now returns connection 2.

Result of this run

• When OSIV is on, the persistence context stays open until the response is finished, and the database connection is held along with it for that time. The connection stays occupied even during the wait for the external API, which does not use the database, so on an API server with heavy traffic the connections soon run short. When OSIV is off, the connection is returned when the transaction ends.

Code

# application.yml
spring.datasource.hikari.maximum-pool-size: 3
spring.datasource.hikari.connection-timeout: 5000   # fail after waiting 5s
spring.jpa.open-in-view: true

// controller
OrderDto get(long id) {
    Order order = orderService.find(id);       // @Transactional, database work 0.5s
    Delivery d = deliveryApi.track(order);     // external API 2s (no database use)
    return OrderDto.of(order, d);
}

Assumptions of this model

The model assumes that requests arrive 0.1s apart and that there are enough server threads to handle them (200 by default in Tomcat). The limit on waiting for a connection is fixed at 5s (the HikariCP default is 30s). When OSIV is on, the model takes the connection to be borrowed at the first database access and returned when the response is finished, which follows the default behavior of Hibernate in a Spring environment, where it keeps the connection until the session is closed. The numbers are a model meant to show the relationships and are not real measurements.

Previous lab: JdbcTemplate and exception translation · Next lab: Servlets