c3p0.properties構成によるcom.mchange.v2.resourcepool.TimeoutException

40095 ワード

c3p0.properties構成によるcom.mchange.v2.resourcepool.TimeoutException


最近、顧客の業務量の増加により、会社は分散処理方式を用いて顧客の反応の処理速度が遅い問題を解決することにした.本博は主にプロジェクトで出会った問題を説明し、もし間違いがあれば批判して指摘してください.くだらないことを言わないで直接干物に行く.まず,ビジネスロジックを再整理し,分散(単純分散)を考慮するために,関係のないビジネスと同時発生の可能性のあるロジックを抽出した.そこで、私は単独で実行できるプロジェクトを1台のサーバに配置し、コピーセットを別のサーバに配置しました.2台のサーバが同時に処理メッセージを実行します.結果...
org.hibernate.exception.GenericJDBCException: Could not open connection
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:304)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169)
    at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.extractPhysicalConnection(ConnectionProxyHandler.java:82)
    at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at com.sun.proxy.$Proxy104.prepareStatement(Unknown Source)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1854)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1831)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
    at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:311)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:2111)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:82)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:72)
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3887)
    at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:458)
    at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:427)
    at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:204)
    at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:260)
    at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:148)
    at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1078)
    at org.hibernate.internal.SessionImpl.access$2000(SessionImpl.java:175)
    at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2424)
    at org.hibernate.internal.SessionImpl.get(SessionImpl.java:974)
    at com.ygjt.bcb.base.AbstractBaseDAO.get(AbstractBaseDAO.java:40)
    at com.ygjt.bcb.base.AbstractCommonDAO.findById(AbstractCommonDAO.java:65)
    at com.ygjt.bcb.cmn.dao.impl.CustomsDAOImpl.findById(CustomsDAOImpl.java:19)
    at com.ygjt.bcb.cmn.dao.impl.CustomsDAOImpl.findById(CustomsDAOImpl.java:1)
    at sun.reflect.GeneratedMethodAccessor513.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:80)
    at com.google.code.ssm.aop.SingleReadCacheAdvice.cache(SingleReadCacheAdvice.java:78)
    at com.google.code.ssm.aop.ReadThroughSingleCacheAdvice.cacheGetSingle(ReadThroughSingleCacheAdvice.java:52)
    at sun.reflect.GeneratedMethodAccessor509.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
    at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at com.sun.proxy.$Proxy46.findById(Unknown Source)
    at com.ygjt.bcb.base.BaseBO.findById(BaseBO.java:27)
    at sun.reflect.GeneratedMethodAccessor508.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at com.sun.proxy.$Proxy47.findById(Unknown Source)
    at com.ygjt.bcb.datainterface.proc.impl.EAutoCheck.check(EAutoCheck.java:172)
    at com.ygjt.bcb.datainterface.proc.impl.EAutoCheck.eede(EAutoCheck.java:124)
    at com.ygjt.bcb.datainterface.proc.impl.EdeMessageProcessorImpl.process(EdeMessageProcessorImpl.java:116)
    at com.ygjt.bcb.datainterface.proc.impl.DataProcessImpl.process(DataProcessImpl.java:19)
    at com.ygjt.bcb.datainterface.DataProcThread.run(DataProcThread.java:27)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:527)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
    at org.hibernate.service.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:141)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:297)
    ... 70 more
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@5f927daf -- timeout at awaitAvailable()
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1317)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
    ... 74 more
``` google , c3p0 bug, c3p0.properties ( ) :




"se-preview-section-delimiter">

The size of c3p0’s global PreparedStatement cache


simple.c3p0.maxStatements=500




<div class="se-preview-section-delimiter">div>

```ruby
#The size of c3p0's global PreparedStatement cache
simple.c3p0.maxStatements=0

後でc 3 p 0を見てみました.propertiesの説明は、以前に問題があった構成を貼り付けます.
# C3P0 setup
# how many connections at a time c3p0 will try to acquire when the pool is exhausted
simple.c3p0.acquireIncrement=3
# Number of Connections a pool will try to acquire upon startup
simple.c3p0.initialPoolSize=2
#If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds
simple.c3p0.idleConnectionTestPeriod=10
#Minimum number of Connections a pool will maintain at any given time
simple.c3p0.minPoolSize=2
#Maximum number of Connections a pool will maintain at any given time
simple.c3p0.maxPoolSize=200
#c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. 
#Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously. 
simple.c3p0.numHelperThreads=10
#Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire
simple.c3p0.maxIdleTime=10
#The size of c3p0's global PreparedStatement cache
simple.c3p0.maxStatements=500
#the max size for every connection
simple.c3p0.maxStatementsPerConnection=20
# how many times c3p0 will try to acquire a new Connection from the database before giving up
simple.c3p0.acquireRetryAttempts=30
# a pooled DataSource will declare itself broken and be permanently closed if a Connection cannot be obtained from the database after making 
# to acquire one.
simple.c3p0.breakAfterAcquireFailure=false
#time c3p0 will wait between acquire attempts
simple.c3p0.acquireRetryDelay=1000
#The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted
simple.c3p0.checkoutTimeout=10000
#The JDBC spec is unforgivably silent on what should happen to unresolved, pending transactions on Connection close
simple.c3p0.autoCommitOnClose=false

これは後で自分で研究した構成です.
# C3P0 setup
# how many connections at a time c3p0 will try to acquire when the pool is exhausted
simple.c3p0.acquireIncrement=5
# Number of Connections a pool will try to acquire upon startup
simple.c3p0.initialPoolSize=2
#If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds
simple.c3p0.idleConnectionTestPeriod=10
#Minimum number of Connections a pool will maintain at any given time
simple.c3p0.minPoolSize=2
#Maximum number of Connections a pool will maintain at any given time
simple.c3p0.maxPoolSize=200
#c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. 
#Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously. 
simple.c3p0.numHelperThreads=20
#Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire
simple.c3p0.maxIdleTime=10
#The size of c3p0's global PreparedStatement cache
#HC change
simple.c3p0.maxStatements=10
#the max size for every connection
simple.c3p0.maxStatementsPerConnection=20
# how many times c3p0 will try to acquire a new Connection from the database before giving up
simple.c3p0.acquireRetryAttempts=30
# a pooled DataSource will declare itself broken and be permanently closed if a Connection cannot be obtained from the database after making 
# to acquire one.
simple.c3p0.breakAfterAcquireFailure=false
#time c3p0 will wait between acquire attempts
simple.c3p0.acquireRetryDelay=1000
#The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted
simple.c3p0.checkoutTimeout=10000

解析:最大接続数200、maxStatementsは、データ・ソースにロードされるPreparedStatementsの数を制御します.ただし、プリキャッシュされたstatementsは、接続プール全体ではなく単一のconnectionに属します.したがって,このパラメータを設定するには多方面の要因を考慮する必要がある.配置が大きすぎると、逆に文章の開始時の異常が発生します.