Postgres connection has been closed error in Spring Boot

Question

I am running a Spring Boot application to create REST apis. Often I get an error saying that the database connection is closed, and after that I cannot make any calls to the application. I'm using Postgres DB. This is the complete stack trace:

org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed: 
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:457)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy91.findByUriMoniker(Unknown Source)
    at com.mypkg.businessobjects.OrderInfoBO.getOrderInfo(OrderInfoBO.java:76)
    at com.mypkg.controller.OrderInfoController.getOrderInfo(OrderInfoController.java:78)
    at sun.reflect.GeneratedMethodAccessor104.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:777)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:706)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:85)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:130)
    at com.mypkg.config.CORSFilter.doFilter(CORSFilter.java:39)
    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:132)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:132)
    at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:85)
    at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:61)
    at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
    at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131)
    at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:56)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:45)
    at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:63)
    at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:58)
    at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:70)
    at io.undertow.security.handlers.SecurityInitialHandler.handleRequest(SecurityInitialHandler.java:76)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:261)
    at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:247)
    at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:76)
    at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:166)
    at io.undertow.server.Connectors.executeRootHandler(Connectors.java:197)
    at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:759)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed: 
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1771)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:64)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:159)
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)
    ... 56 more
Caused by: org.hibernate.TransactionException: JDBC begin transaction failed: 
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:76)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162)
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1435)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:61)
    ... 58 more
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:833)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.getAutoCommit(AbstractJdbc2Connection.java:794)
    at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
    at com.sun.proxy.$Proxy56.getAutoCommit(Unknown Source)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:68)
    ... 61 more

When I restart the application, it goes away. I think this problem occurs when I restart my postgres DB. Why is this happening?

score 44 · Answer 1 · answered Feb 13 '16 at 14:50

This is kind of half-answered by the other posts and I wanted to be very explicit. Also I wanted to be more Spring-Boot-esque. Feel free to change the time intervals as necessary.

Option 1: Toss out broken connections from the pool.

Use these properties:

spring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1;
spring.datasource.validation-interval=30000

Option 2: Keep connections in the pool alive.

Use these properties:

spring.datasource.test-while-idle=true
spring.datasource.validation-query=SELECT 1;
spring.datasource.time-between-eviction-runs-millis=60000

Option 3: Proactively toss out idle connections.

Use these properties (Note: I was not able to find reliable documentation on this one for Spring Boot. Also the timeout is in seconds not milliseconds):

spring.datasource.remove-abandoned=true
spring.datasource.remove-abandoned-timeout=60

Happy booting!

Spring Boot 1.4 makes it a bit more explicit that these settings are specific to the pooling implementation, as they need to be prefixed properly (e.g. `spring.datasource.tomcat`, `spring.datasource.dbcp`, `spring.datasource.dbcp2`, or `spring.datasource.hikari`) instead of just `spring.datasource`. Spring's relaxed binding will then just pass them through to the underlying connection pool implementation. Check the docs for the specific pool you are using for what the correct configuration parameters are... the above look ok for tomcat and dbcp but hikari is a bit different. — sworisbreathing, Jan 23 '17 at 05:40

score 14 · Accepted Answer · answered Apr 14 '15 at 06:21

14

Very valid question and this problem is usually faced by many. The exception generally occurs, when network connection is lost between pool and database (most of the time due to restart). Looking at the stack trace you have specified, it is quite clear that you are using jdbc pool to get the connection. JDBC pool has options to fine-tune various connection pool settings and log details about whats going on inside pool.

You can refer to to detailed apache documentation on pool configuration to specify abandon timeout

Check for removeAbandoned, removeAbandonedTimeout, logAbandoned parameters

Additionally you can make use of additional properties to further tighten the validation

Use testXXX and validationQuery for connection validity.

answered Apr 14 '15 at 06:21

CuriousMind

3,143
3
29
54

4

Did this solution help you? I have a similar problem, but this did not help me. – Mark.ewd Sep 24 '15 at 20:42
@Mark.ewd: if still relevant, you could try http://stackoverflow.com/a/33660586/56285 – Jonik Nov 11 '15 at 21:58
In Spring Boot, if you auto-configure Data Source, the pooling is auto-configured too if a proper Driver class is used (e.g. org.postgresql.Driver). You may adjust the pooling by providing the following properties in application.properties: spring.datasource.test-on-borrow=true, spring.datasource.remove-abandoned=true, spring.datasource.validation-query=SELECT 1; – Rafal Borowiec Dec 09 '15 at 22:16
I see a related issue with this - "Cannot change transaction isolation level in the middle of a transaction .’". I removed these settings mentioned in the comment above and it started working fine. Any ideas why? – Tisha Apr 11 '16 at 02:47
@Tisha, I haven't recommended isolation level setting changes. Please check the database driver manual and check if there are any additional settings that need to be configured to enable abandon connection. – CuriousMind Aug 14 '16 at 15:42

score 2 · Answer 3 · edited May 23 '17 at 11:54

I had the exact same problem, with this setup, also using DataSource from Tomcat (org.apache.tomcat.jdbc.pool) to connect to Heroku Postgres:

org.springframework.transaction.CannotCreateTransactionException: 
    Could not open JPA EntityManager for transaction
org.hibernate.TransactionException: JDBC begin transaction failed: ] 
    with root cause
org.postgresql.util.PSQLException: This connection has been closed.

What solved it for me was adding this to DataSource init code (borrowing from a Grails question):

dataSource.setTestOnBorrow(true);
dataSource.setTestWhileIdle(true);
dataSource.setTestOnReturn(true);
dataSource.setValidationQuery("SELECT 1");

I'm not sure if all these three are needed to get a stable connection—perhaps not—but having all enabled probably doesn't hurt much.

The JavaDocs clarify what's going on: see e.g. setTestOnBorrow(). A little surprising, perhaps, that by default no such tests are made.

V G · Answer 4 · 2020-09-19T10:58:44.457

This exception basically says the JDBC connection was closed, but it doesn't mean that the database server is not running (there is another exception for that). This could happen when the DB server was restarted or after the DB server dropped the connection (eg. because of a timeout). So the question here is why the application does not reconnect to the server on a new HTTP request.

Usually this is a misconfiguration of the connection pool which should validate the connection each time the application "borrows" one. All you need to solve this problem is the following:

spring.datasource.validation-query=SELECT 1;
spring.datasource.test-on-borrow=true

Other configuration parameters (from other answers) are optimzations that are not strictly required for this exception.

But sometimes even if the JDBC pool is properly configured there could be a certain application bug in which the application holds the DB connection without returning it to the JDBC pool after the HTTP request ends. So the JDBC pool does not even have the possibility to validate the DB connection (all it knows is that the DB connection is "ALLOCATED"). The general solution here is to make sure the application returns the connection and "borrow" a new connection on each HTTP request.

One example of such a bug:

@Component
public MyService {
    @Resource
    private EntityManagerFactory emf;
    
    private EntityManager em;
    
    public MyService() {
         em = emf.createEntityManager();//em never return back its JDBC connection to the pool (using em.close())
    }
}

The solution for the bug above is either to use an injected/managed EntityManager (prefered)

@Component
public MyService {
    @PersistenceContext
    private EntityManager em;
}

or if you really need to manage it yourself create an EntityManager for every HTTP request and close it in a try-finally block if you really

@Component
public MyService {
    @Resource
    private EntityManagerFactory emf;
    
    private EntityManager em;
    
    public void myMethod() {
         EntityManager em = emf.createEntityManager();
         try {
         
         } finaly {
            em.close();//do not forget other cleanup operations like rolling back the transaction
         }
    }
}

score 0 · Answer 5 · answered Feb 23 '16 at 16:18

I had exactly the same problem but in my case the afore mentioned answers did not help. I figured out that when doing a long query the same error appears. In my case I called findAll(Iterable ids) and passed a huge list of more than 100'000 ids. Partitioning the list (e.g. using ListUtils from Apache Commons or Google Guava) and calling the findAll() with less ids did the trick.

Nelcon Croos · Answer 6 · 2021-06-24T08:58:35.063

-1

when you write queries in repository try to keep @Repository annotation there in repository

edited Jun 24 '21 at 08:58

answered Jun 16 '21 at 04:22

Nelcon Croos

71
1
5

Postgres connection has been closed error in Spring Boot

6 Answers6

Linked