Java 基础知识 - 对休眠/JDBC 连接池问题进行故障排除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4107275/
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
Basics - Troubleshooting Hibernate / JDBC Connection Pool Issue
提问by BuddyJoe
What is Hibernate's responsibility in regards to database connections it gets from an underlying connection pool. Does it test to see if a connection is closed before it uses it? and if so get another connection from the pool?
Hibernate 对它从底层连接池获得的数据库连接的责任是什么。它是否会在使用连接之前进行测试以查看连接是否已关闭？如果是这样，从池中获得另一个连接？
I've included error and confirmation info below. Any ideas of where I can start to troubleshoot this would be very helpful. And any advice on the SQL Server driver settings we are using.
我在下面包含了错误和确认信息。我可以从哪里开始解决这个问题的任何想法都会非常有帮助。以及关于我们正在使用的 SQL Server 驱动程序设置的任何建议。
from the Catalina log:
来自卡特琳娜日志：
04-Nov-2010 21:54:52.691 WARNING org.apache.tomcat.jdbc.pool.ConnectionPool.abandon Connection has been abandoned PooledConnection[ConnectionID:8]:java.lang.Exception
at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:926)
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:681)
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:545)
at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:166)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:106)
from our application log:
从我们的应用程序日志：
2010-11-04 21:54:52,705 [tomcat-http--18] WARN util.JDBCExceptionReporter - SQL Error: 0, SQLState: 08S01
2010-11-04 21:54:52,707 [tomcat-http--18] ERROR util.JDBCExceptionReporter - Socket closed
2010-11-04 21:54:52,708 [tomcat-http--18] ERROR transaction.JDBCTransaction - JDBC rollback failed
java.sql.SQLException: Connection has already been closed.
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:112)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:94)
at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:71)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:94)
at org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.invoke(ConnectionState.java:132)
at $Proxy38.rollback(Unknown Source)
at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:217)
at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:196)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:676)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:845)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:822)
at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:412)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:111)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:625)
The configuration:
配置：
<Resource defaultAutoCommit="false" defaultReadOnly="false"
defaultTransactionIsolation="SERIALIZABLE"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
fairQueue="false" initialSize="10"
jdbcInterceptors="ConnectionState;StatementFinalizer"
jmxEnabled="true" logAbandoned="true" maxActive="100"
maxIdle="10" maxWait="30000"
minEvictableIdleTimeMillis="10000" minIdle="10"
name="com.ourcompany.ap.shoppingcart/datasource"
password="somePassword" removeAbandoned="true"
removeAbandonedTimeout="60" testOnBorrow="true"
testOnReturn="false" testWhileIdle="false"
timeBetweenEvictionRunsMillis="5000"
type="javax.sql.DataSource"
url="jdbc:sqlserver://approd\approd;databaseName=prod"
useEquals="false" username="AccessPointNet"
validationInterval="30000" validationQuery="SELECT 1"/>`
采纳答案by Pascal Thivent
What is Hibernate's responsibility in regards to database connections it gets from an underlying connection pool.
Hibernate 对它从底层连接池获取的数据库连接的责任是什么。
Not much, releasing it when the Sessiongets closed.
不多，当Session关闭时释放它。
Does it test to see if a connection is closed before it uses it? and if so get another connection from the pool?
它是否会在使用连接之前进行测试以查看连接是否已关闭？如果是这样，从池中获得另一个连接？
No, Hibernate doesn't, checking the validity of connection(s) is the responsibility of a connection pool if you want to.
不，Hibernate 没有，如果您愿意，检查连接的有效性是连接池的责任。
I've included error and confirmation info below. Any ideas of where I can start to troubleshoot this would be very helpful.
我在下面包含了错误和确认信息。我可以从哪里开始解决这个问题的任何想法都会非常有帮助。
What kind of process are you running exactly? A long transaction? Does it timeout? What does the Caused by:say? About the trace:
你到底在运行什么样的进程？长交易？是否超时？什么是Caused by:说什么？关于跟踪：
2010-11-04 21:54:52,705 [tomcat-http--18] WARN util.JDBCExceptionReporter - SQL Error: 0, SQLState: 08S01 2010-11-04 21:54:52,707 [tomcat-http--18] ERROR util.JDBCExceptionReporter - Socket closed 2010-11-04 21:54:52,708 [tomcat-http--18] ERROR transaction.JDBCTransaction - JDBC rollback failed java.sql.SQLException: Connection has already been closed.
Can you reproduce it in a deterministic way? Any networking problem?
你能以一种确定性的方式重现它吗？有什么网络问题吗？
And any advice on the SQL Server driver settings we are using.
以及关于我们正在使用的 SQL Server 驱动程序设置的任何建议。
I've added a great resource about Tomcat and connection pool configuration below. Not specific to SQL Server though.
我在下面添加了一个关于 Tomcat 和连接池配置的很好的资源。虽然不是特定于 SQL Server。
Resources
资源
回答by Jeremy
I worked on an issue in the past where we weren't returning connections back to the pool correctly. So, when a connection was used and not returned, making a database call when it was timing out would throw an exception.
我过去曾解决过一个问题，即我们没有正确地将连接返回到池中。所以，当一个连接被使用但没有返回时，在超时时进行数据库调用会抛出异常。
We were able to reproduce the issue by making a call to the database, waited 8 hours (postgres' default time out) and tried to make a call to the database again. It throw the same exception every time. Our solution was to rethink (or better yet, add) a connection management strategy.
我们能够通过调用数据库来重现该问题，等待 8 小时（postgres 的默认超时）并尝试再次调用数据库。它每次都抛出相同的异常。我们的解决方案是重新考虑（或者更好的是，添加）连接管理策略。
So, to sum up, are you actually returning your connections to the pool by closing the Session?
所以，总而言之，您实际上是通过关闭会话将连接返回到池中吗？
回答by meriton
We usually work around this by using dbcp, and providing a validationQuery when definining our data source. Then, dbcp will verify the usability of pooled connections by issuing that query (and transparently recreate the connection should it no longer work), prior to returning them to the application.
我们通常通过使用 dbcp 来解决这个问题，并在定义我们的数据源时提供一个验证查询。然后，在将连接返回给应用程序之前，dbcp 将通过发出该查询（并在连接不再工作时透明地重新创建连接）来验证池连接的可用性。
Check out http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.htmlfor more details.
查看 http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html了解更多详情。
回答by Cid
I had a similar problem which was solved by increasing the removeAbandonedTimeoutvalue to a higher number. The problem we faced was due to the query which took longer time that the above mentioned timeout.
我有一个类似的问题，通过将removeAbandonedTimeout值增加到更高的数字来解决。我们面临的问题是由于查询花费了比上述超时更长的时间。
回答by mjj1409
I am currently using liquibase(v1.9)in my project, and when the changeSets run against a blank schema it always takes longer than 60 seconds which results in the thread being marked abandoned I'm not thrilled with increasing the removeAbandonedTimeoutvalue, but this is the only solution I've been able to find to prevent this issue; however, after the initial schema population is complete this is seldom a problem so I set the value back to 60 seconds.
我目前正在liquibase(v1.9)我的项目中使用，当更改集针对空白模式运行时，它总是需要超过 60 秒，这导致线程被标记为已放弃我对增加removeAbandonedTimeout值并不感到兴奋，但这是我唯一的解决方案已经能够找到防止这个问题；但是，在初始模式填充完成后，这很少成为问题，因此我将该值设置回 60 秒。
回答by mjj1409
I got the solution for the above exception. Just close the instance of session factory as well while closing the session .
我得到了上述异常的解决方案。只需在关闭会话的同时关闭会话工厂的实例。
Look at the below Code:
看下面的代码：
public class HibernateUtil {
private static final SessionFactory sessionFactory = buildSessionFactory();
private static SessionFactory buildSessionFactory() {
try {
// Create the SessionFactory from hibernate.cfg.xml
return new Configuration().configure("hibernate.cfg.xml").buildSessionFactory();
}
catch (Throwable ex) {
ex.printStackTrace();
// Make sure you log the exception, as it might be swallowed
System.err.println("Initial SessionFactory creation failed." + ex);
throw new ExceptionInInitializerError(ex);
}
}
public static SessionFactory getSessionfactory() {
return sessionFactory;
}
public static Session getSession() {
Session session=sessionFactory.openSession();
session.getTransaction().begin();
return session;
}
public static void closeSession(Session session) {
if(session!=null )
{
if(session.getTransaction().isActive())
{
session.getTransaction().commit();
}
session.close();
getSessionfactory().close();
}
}
}
just call the method HibernateUtil.closeSession(). This will solve the problem.
只需调用方法HibernateUtil.closeSession()。这将解决问题。
