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?

Answer 1

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!

Answer 2

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.

Answer 3

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.

Answer 4

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
         }
    }
}