Spring Boot 中的 Postgres 连接已关闭错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29620265/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres connection has been closed error in Spring Boot
提问by drunkenfist
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:
我正在运行一个 Spring Boot 应用程序来创建 REST api。我经常收到一条错误消息，说数据库连接已关闭，之后我无法对应用程序进行任何调用。我正在使用 Postgres 数据库。这是完整的堆栈跟踪：
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.accessspring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1;
spring.datasource.validation-interval=30000
0(ServletInitialHandler.java:76)
at io.undertow.servlet.handlers.ServletInitialHandler.handleRequest(ServletInitialHandler.java:166)
at io.undertow.server.Connectors.executeRootHandler(Connectors.java:197)
at io.undertow.server.HttpServerExchange.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?
当我重新启动应用程序时，它消失了。我认为当我重新启动我的 postgres 数据库时会出现这个问题。为什么会这样？
采纳答案by CuriousMind
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 poolto get the connection. JDBC poolhas options to fine-tune various connection pool settings and log details about whats going on inside pool.
非常有效的问题，很多人通常都会遇到这个问题。异常通常发生在池和数据库之间的网络连接丢失时（大部分时间是由于重新启动）。查看您指定的堆栈跟踪，很明显您正在使用它jdbc pool来获取连接。JDBC pool有选项可以微调各种连接池设置并记录有关池内发生的事情的详细信息。
You can refer to to detailed apache documentation on pool configurationto specify abandon timeout
您可以参考详细的 apache 池配置文档来指定放弃超时
Check for removeAbandoned, removeAbandonedTimeout, logAbandoned parameters
检查 removeAbandoned、removeAbandonedTimeout、logAbandoned 参数
Additionally you can make use of additional properties to further tighten the validation
此外，您可以利用其他属性来进一步加强验证
Use testXXX and validationQuery for connection validity.
使用 testXXX 和 validationQuery 来确定连接有效性。
回答by James Watkins
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.
这是其他帖子的一半回答，我想非常明确。我也想变得更像 Spring-Boot 风格。根据需要随意更改时间间隔。
Option 1: Toss out broken connections from the pool.
选项 1：从池中抛出断开的连接。
Use these properties:
使用这些属性：
spring.datasource.test-while-idle=true
spring.datasource.validation-query=SELECT 1;
spring.datasource.time-between-eviction-runs-millis=60000
Option 2: Keep connections in the pool alive.
选项 2：保持池中的连接处于活动状态。
Use these properties:
使用这些属性：
spring.datasource.remove-abandoned=true
spring.datasource.remove-abandoned-timeout=60
Option 3: Proactively toss out idle connections.
选项 3：主动丢弃空闲连接。
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 Boot 的可靠文档。此外，超时以秒为单位，而不是毫秒）：
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.
Happy booting!
开机快乐！
回答by Jonik
I had the exact same problem, with this setup, also using DataSource from Tomcat(org.apache.tomcat.jdbc.pool) to connect to Heroku Postgres:
我遇到了完全相同的问题，使用此设置，还使用来自 Tomcat( org.apache.tomcat.jdbc.pool) 的DataSource连接到 Heroku Postgres：
dataSource.setTestOnBorrow(true);
dataSource.setTestWhileIdle(true);
dataSource.setTestOnReturn(true);
dataSource.setValidationQuery("SELECT 1");
What solved it for me was adding this to DataSource init code (borrowing from a Grails question):
为我解决的是将其添加到 DataSource init 代码中（从Grails 问题中借用）：
##代码##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.
JavaDocs 阐明了正在发生的事情：参见例如setTestOnBorrow(). 也许有点令人惊讶的是，默认情况下不进行此类测试。
回答by Tai Truong
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.
我遇到了完全相同的问题，但就我而言，上述答案没有帮助。我发现在进行长查询时会出现相同的错误。在我的例子中，我调用了 findAll(Iterable ids) 并传递了一个超过 100'000 个 id 的巨大列表。对列表进行分区（例如，使用 Apache Commons 或 Google Guava 中的 ListUtils）并使用较少的 id 调用 findAll() 就成功了。
