Our DevOps team reported that a specific RESTful service had suddenly started throwing a large number of exceptions.
Upon checking the logs, it was mainly the below exception -
Caused by: java.sql.SQLRecoverableException: The connection is closed
at oracle.ucp.jdbc.proxy.oracle.ConnectionProxy.pre(ConnectionProxy.java:237)
at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1ConnectionProxy$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy.prepareStatement(Unknown Source)
at oracle.jdbc.OracleConnectionWrapper.prepareStatement(OracleConnectionWrapper.java:161)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:94)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:183)
The database connections were getting closed unexpectedly while the requests (events) were still being processed.
We were using Oracle Universal Connection Pool (UCP). The PoolDataSource in UCP has a property abandonedConnectionTimeout. This timeout determines how long a borrowed connection can remain unused before it is considered as abandoned and reclaimed by the connection pool.
In our case, while processing an event, the service was borrowing a connection from the pool, but it was remaining unused for longer than the abandonedConnectionTimeout. Hence the above exception.
This exposed another flaw in the service’s design - the service was holding database connections for too long, even when it was not performing any database operations. Ideally, connections should be used only during the database transactions and then promptly returned to the pool for reuse. This ensures efficient resource management.
Prototyping
To make debugging easier and try various cases, I built a small application that replicated the same issue of closed connections.
You can find the source code in a GitHub repository here.
The EmployeeController has an endpoint /employees/bulk that creates and saves two employees in the database. However, there’s a 60-second delay between saving each employee. This delay represents a long running process in our production application that does not perform any database transactions.
This delay of 60-seconds is greater than the abandoned-connection-timeout of 20 seconds configured inside application.yml and DataSourceConfig.java files.
The createEmployee method in the EmployeeService class is a straightforward implementation. It simply takes the received Employee object and saves it using EmployeeRepository.
To monitor database connections and transaction activities behind the scenes, logging is enabled for the org.springframework.orm.jpa.JpaTransactionManager class in application.yml.
When you send a POST request to the endpoint /employees/bulk, you see that the application throws below exception —
java.sql.SQLRecoverableException: The connection is closed
at oracle.ucp.jdbc.proxy.oracle.ConnectionProxy.pre(ConnectionProxy.java:246) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1ConnectionProxy$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy.setAutoCommit(Unknown Source) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:72) ~[hibernate-core-6.6.8.Final.jar:6.6.8.Final]
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:295) ~[hibernate-core-6.6.8.Final.jar:6.6.8.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:232) ~[hibernate-core-6.6.8.Final.jar:6.6.8.Final]
at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:83) ~[hibernate-core-6.6.8.Final.jar:6.6.8.Final]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:176) ~[spring-orm-6.2.3.jar:6.2.3]
at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:420) ~[spring-orm-6.2.3.jar:6.2.3]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:532) ~[spring-tx-6.2.3.jar:6.2.3]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:405) ~[spring-tx-6.2.3.jar:6.2.3]
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:639) ~[spring-tx-6.2.3.jar:6.2.3]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:374) ~[spring-tx-6.2.3.jar:6.2.3]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.2.3.jar:6.2.3]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.3.jar:6.2.3]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:727) ~[spring-aop-6.2.3.jar:6.2.3]
at com.ucp.example.service.EmployeeService$$SpringCGLIB$$0.createEmployee(<generated>) ~[main/:na]
at com.ucp.example.controller.EmployeeController.createEmployees(EmployeeController.java:46) ~[main/:na]
Since the abandoned-connection-timeout property was set to 20 seconds, the database connection used by the EntityManager was closed after remaining unused for 20 seconds.
More importantly - and crucially - this reveals that the same EntityManager and the same database connection were used, even though the two database operations occurred in separate transactions.
In the log console, you will see the logs Found thread-bound EntityManager. This indicates that Spring creates EntityManager at the beginning of request and the application uses same EntityManager for saving both the employees.
c.u.e.controller.EmployeeController : saving first employee ...
o.s.orm.jpa.JpaTransactionManager : Found thread-bound EntityManager [SessionImpl(910391533<open>)] for JPA transaction
o.s.orm.jpa.JpaTransactionManager : Creating new transaction with name [com.ucp.example.service.EmployeeService.createEmployee]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
o.s.orm.jpa.JpaTransactionManager : Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@3b531f40]
o.s.orm.jpa.JpaTransactionManager : Found thread-bound EntityManager [SessionImpl(910391533<open>)] for JPA transaction
o.s.orm.jpa.JpaTransactionManager : Participating in existing transaction
o.s.orm.jpa.JpaTransactionManager : Initiating transaction commit
o.s.orm.jpa.JpaTransactionManager : Committing JPA transaction on EntityManager [SessionImpl(910391533<open>)]
o.s.orm.jpa.JpaTransactionManager : Not closing pre-bound JPA EntityManager after transaction
c.u.e.controller.EmployeeController : waiting for 60 seconds ...
c.u.e.controller.EmployeeController : saving second employee ...
o.s.orm.jpa.JpaTransactionManager : Found thread-bound EntityManager [SessionImpl(910391533<open>)] for JPA transaction
o.s.orm.jpa.JpaTransactionManager : Creating new transaction with name [com.ucp.example.service.EmployeeService.createEmployee]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
The database connection was held beyond the duration of the database operations, even when it was not actively in use.
It might happen that in a production system, for example, if abandoned-connection-timeout is set to 10 minutes, and the application performs a database operation followed by a 5 minutes of in-memory processing, the connection remains held for those 5 minutes. This is not an efficient use of database resources.
Spring’s Open Session in View (OSIV)
There are lot of articles on the internet that explains this in greater detail. This article talks the basics.
Spring's Open Session in View (OSIV) keeps a Hibernate Session (or JPA EntityManager) open for the entire request lifecycle, allowing lazy-loaded entities to be fetched even after the transaction has ended.
By default, OSIV is enabled. As we have observed, it can lead to inefficient database connection usage where connections are held without actually being used. This can lead to database connection starvation. To see this in action, simply run the application and send 5 POST requests to an endpoint. Given that max-pool-size is set to 4, the first 4 requests will successfully fetch connections from the pool. The 5th request, however, will be forced to wait for 10 seconds (as per connection-wait-duration). Once the timeout expires and the request is still waiting for connection, the application will throw the following exception:
oracle.ucp.UniversalConnectionPoolException: UCP-45064: All connections in the Universal Connection Pool are in use - [ 4, 4, 4, 0, 0, 0, 1, 0, 4, 4 ]
at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:316) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:62) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:98) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionWithoutCountingRequests(UniversalConnectionPoolImpl.java:520) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidateHelper(UniversalConnectionPoolImpl.java:279) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidate(UniversalConnectionPoolImpl.java:191) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnection(UniversalConnectionPoolImpl.java:157) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
at oracle.ucp.jdbc.JDBCConnectionPool.borrowConnection(JDBCConnectionPool.java:207) ~[ucp-23.3.0.23.09.jar:23.3.0.23.09]
You can disable Open Session in View (OSIV) by setting:
spring:
jpa:
open-in-view: false
When OSIV is disabled, Spring no longer opens an EntityManager at the start of a request. Instead, a new EntityManager is created only when a transaction begins with @Transactional and is closed immediately once the transaction completes. This ensures more efficient database connection usage and prevents connections from being held unnecessarily.
With OSIV disabled, when you send a request to the endpoint, it will be processed successfully. Additionally, if you send 5 requests simultaneously, all 5 requests will be handled without issues.
Key takeaways
Connections should be borrowed only while performing database operations and then promptly returned to the pool for reuse. This ensures efficient resource management.
Executing database operations in separate transactions does not necessarily mean that different database connections will be used.
Consider optimizing database resource usage by disabling Spring OSIV, especially for applications that perform extensive processing outside transactional contexts.