問題：
系統(tǒng)采用Spring MVC 2.5 + Spring 2.5 + Hibernate 3.2架構(gòu)，其中數(shù)據(jù)源連接池采用的是Apache commons DBCP。問題是這樣的，系統(tǒng)運行一段時間后（大致每隔8小時），訪問系統(tǒng)會出現(xiàn)如下錯誤，再次訪問恢復正常。
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.GenericJDBCException: Cannot release connection
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:583)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
Caused by: org.hibernate.exception.GenericJDBCException: Cannot release connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at org.hibernate.jdbc.ConnectionManager.closeConnection(ConnectionManager.java:449)
at org.hibernate.jdbc.ConnectionManager.cleanup(ConnectionManager.java:379)
at org.hibernate.jdbc.ConnectionManager.manualDisconnect(ConnectionManager.java:333)
at org.hibernate.impl.SessionImpl.disconnect(SessionImpl.java:375)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCleanupAfterCompletion(HibernateTransactionManager.java:744)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.cleanupAfterCompletion(AbstractPlatformTransactionManager.java:989)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:855)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:800)
at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:339)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:421)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:136)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:326)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:313)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
36 more
Caused by: java.sql.SQLException: Already closed.
at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:77)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:180)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.closeConnection(LocalDataSourceConnectionProvider.java:96)
at org.hibernate.jdbc.ConnectionManager.closeConnection(ConnectionManager.java:445)
62 more
解決：
造成Cannot release connection的原因有很多，要具體問題具體分析。從異常分析，造成這個異常 org.hibernate.exception.GenericJDBCException: Cannot release connection 歸根結(jié)底是Caused by: java.sql.SQLException: Already closed. 即連接已關(guān)閉。所以解決的辦法就要從DBCP的參數(shù)配置入手，見下面的參數(shù)配置properties文件。
#### :: Apache DBCP :: ####
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@10.165.153.9:1521:PRDC
jdbc.username=guser
jdbc.password=guser
#初始化連接
jdbc.initialSize=0
#連接池的最大活動個數(shù)
jdbc.maxActive=20
#沒有人用連接的時候，最大閑置的連接個數(shù)。
jdbc.maxIdle=100
#沒有人用連接的時候，最小閑置的連接個數(shù)。
jdbc.minIdle=0
#超時等待時間以毫秒為單位
jdbc.maxWait=10000
#是否自動回收超時連接
jdbc.removeAbandoned=true
#設置被遺棄的連接的超時的時間（以秒數(shù)為單位），即當一個連接被遺棄的時間超過設置的時間，則它會自動轉(zhuǎn)換成可利用的連接。默認的超時時間是300秒。
jdbc.removeAbandonedTimeout=60
#是否在自動回收超時連接的時候打印連接的超時錯誤
jdbc.logAbandoned = true
#給出一條簡單的sql語句進行驗證
jdbc.validationQuery=select 1 from dual
#在取出連接時進行有效驗證
jdbc.testOnBorrow=true
其中標紅的兩個參數(shù)的作用是對池化連接進行驗證，This will ensure that DBCP only hands out good connections to Hibernate. 故加上這兩個參數(shù)后，這個異常就不會再出現(xiàn)了。在Spring中指定數(shù)據(jù)源如下：
<!-- 數(shù)據(jù)源定義 使用Apache DBCP連接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="maxActive" value="${jdbc.maxActive}" />
<property name="maxIdle" value="${jdbc.maxIdle}" />
<property name="maxWait" value="${jdbc.maxWait}" />
<property name="removeAbandoned" value="${jdbc.removeAbandoned}" />
<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}" />
<property name="logAbandoned" value="${jdbc.logAbandoned}" />
<property name="validationQuery" value="${jdbc.validationQuery}" />
<property name="testOnBorrow" value="${jdbc.testOnBorrow}" />
</bean>
問題解決了。
另附
DBCP參數(shù)詳細說明（部分參考了 http://justin-ray.javaeye.com/blog/413591 ）：
在配置時，大部分參數(shù)一目了然，不再贅述，有不清楚的話，可以參考下面的詳細說明。主要難以理解的參數(shù)主要有：removeAbandoned 、logAbandoned、removeAbandonedTimeout、maxWait這四個參數(shù)，設置了rmoveAbandoned=true那么在getNumActive()快要到getMaxActive()的時候，系統(tǒng)會進行無效的Connection的回收，回收的Connection為removeAbandonedTimeout(默認300秒)中設置的秒數(shù)后沒有使用的Connection，激活回收機制大致為getNumActive()=getMaxActive()-3。logAbandoned=true的話，將會在回收事件后，在log中打印出回收Connection的錯誤信息，包括在哪個地方用了Connection卻忘記關(guān)閉了，在調(diào)試的時候很有用。在這里個人建議maxWait的時間不要設得太長，maxWait如果設置太長那么客戶端會等待很久才激發(fā)回收事件。
Parameters
| Parameter |
Description |
| username |
The connection username to be passed to our JDBC driver to establish a connection. |
| password |
The connection password to be passed to our JDBC driver to establish a connection. |
| url |
The connection URL to be passed to our JDBC driver to establish a connection. |
| driverClassName |
The fully qualified Java class name of the JDBC driver to be used. |
| connectionProperties |
The connection properties that will be sent to our JDBC driver when establishing new connections.
Format of the string must be [propertyName=property;]*
NOTE - The "user" and "password" properties will be passed explicitly, so they do not need to be included here. |
| Parameter |
Default |
Description |
| defaultAutoCommit |
true |
The default auto-commit state of connections created by this pool. |
| defaultReadOnly |
driver default |
The default read-only state of connections created by this pool. If not set then the setReadOnly method will not be called. (Some drivers don't support read only mode, ex: Informix) |
| defaultTransactionIsolation |
driver default |
The default TransactionIsolation state of connections created by this pool. One of the following: (see javadoc)
- NONE
- READ_COMMITTED
- READ_UNCOMMITTED
- REPEATABLE_READ
- SERIALIZABLE
|
| defaultCatalog |
|
The default catalog of connections created by this pool. |
| Parameter |
Default |
Description |
| initialSize |
0 |
The initial number of connections that are created when the pool is started.
Since: 1.2 |
| maxActive |
8 |
The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit. |
| maxIdle |
8 |
The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit. |
| minIdle |
0 |
The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none. |
| maxWait |
indefinitely |
The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely. |
NOTE: If maxIdle is set too low on heavily loaded systems it is possible you will see connections being closed and almost immediately new connections being opened. This is a result of the active threads momentarily closing connections faster than they are opening them, causing the number of idle connections to rise above maxIdle. The best value for maxIdle for heavily loaded system will vary but the default is a good starting point.
| Parameter |
Default |
Description |
| validationQuery |
|
The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row. |
| testOnBorrow |
true |
The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.
NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. |
| testOnReturn |
false |
The indication of whether objects will be validated before being returned to the pool.
NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. |
| testWhileIdle |
false |
The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool.
NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. |
| timeBetweenEvictionRunsMillis |
-1 |
The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run. |
| numTestsPerEvictionRun |
3 |
The number of objects to examine during each run of the idle object evictor thread (if any). |
| minEvictableIdleTimeMillis |
1000 * 60 * 30 |
The minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any). |
| connectionInitSqls |
null |
A Collection of SQL statements that will be used to initialize physical connections when they are first created. These statements are executed only once - when the configured connection factory creates the connection. |
| Parameter |
Default |
Description |
| poolPreparedStatements |
false |
Enable prepared statement pooling for this pool. |
| maxOpenPreparedStatements |
unlimited |
The maximum number of open statements that can be allocated from the statement pool at the same time, or zero for no limit. |
This component has also the ability to pool PreparedStatements. When enabled a statement pool will be created for each Connection and PreparedStatements created by one of the following methods will be pooled:
- public PreparedStatement prepareStatement(String sql)
- public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
NOTE - Make sure your connection has some resources left for the other statements. Pooling PreparedStatements may keep their cursors open in the database, causing a connection to run out of cursors, especially if maxOpenPreparedStatements is left at the default (unlimited) and an application opens a large number of different PreparedStatements per connection. To avoid this problem, maxOpenPreparedStatements should be set to a value less than the maximum number of cursors that can be open on a Connection.
| Parameter |
Default |
Description |
| accessToUnderlyingConnectionAllowed |
false |
Controls if the PoolGuard allows access to the underlying connection. |
When allowed you can access the underlying connection using the following construct:
Connection conn = ds.getConnection();
Connection dconn = ((DelegatingConnection) conn).getInnermostDelegate();
...
conn.close()
Default is false, it is a potential dangerous operation and misbehaving programs can do harmfull things. (closing the underlying or continue using it when the guarded connection is already closed) Be carefull and only use when you need direct access to driver specific extentions.
NOTE: Do not close the underlying connection, only the original one.
| Parameter |
Default |
Description |
| removeAbandoned |
false |
Flag to remove abandoned connections if they exceed the removeAbandonedTimout.
If set to true a connection is considered abandoned and eligible for removal if it has been idle longer than the removeAbandonedTimeout. Setting this to true can recover db connections from poorly written applications which fail to close a connection. |
| removeAbandonedTimeout |
300 |
Timeout in seconds before an abandoned connection can be removed. |
| logAbandoned |
false |
Flag to log stack traces for application code which abandoned a Statement or Connection.
Logging of abandoned Statements and Connections adds overhead for every Connection open or new Statement because a stack trace has to be generated. |
If you have enabled "removeAbandoned" then it is possible that a connection is reclaimed by the pool because it is considered to be abandoned. This mechanism is triggered when (getNumIdle() < 2) and (getNumActive() > getMaxActive() - 3)
For example maxActive=20 and 18 active connections and 1 idle connection would trigger the "removeAbandoned". But only the active connections that aren't used for more then "removeAbandonedTimeout" seconds are removed, default (300 sec). Traversing a resultset doesn't count as being used.
THE END
posted on 2010-02-02 16:38
小立飛刀 閱讀(14062)
評論(1) 編輯 收藏 所屬分類:
Hibernate