Hibernate C3p0 configuration

shaiksameeulla · **Joined:** Tue Sep 23, 2014 7:10 am **Posts:** 2

Hi ,
in my project am using Spring & Hibernate with C3p0 connection pooling.
we are Mysql DB, many applications are accessing to this DB.
Max connections are 800 and 200 per user connections. there are atleat 5 user who access this DB for DML operations.

As per logs recently am getting transaction timeout exception and it's happening in production environment

jdbc.properties file

Code:

##########################Connection Pool Setting #######################
#######Basic Pool Configuration######
initialPoolSize=25
minPoolSize=25
maxPoolSize=250
maxIdleTime=3600
acquireIncrement=25

#######Configuring Connection Testing######
idleConnectionTestPeriod=3600
maxIdleTimeExcessConnections=3600
maxConnectionAge=3600
preferredTestQuery=select 1

#######Configuring Statement Pooling#######
maxStatements=200
numHelperThreads=5

#######Configuring Recovery From Database Outages#######
acquireRetryAttempts=3
AcquireRetryDelay=3000
BreakAfterAcquireFailure=false

#######Other DataSource Configuration#############
checkoutTimeout=3600
unreturnedConnectionTimeout=800
debugUnreturnedConnectionStackTraces=true
########################################################################

##########################Hibernate properties #######################
hibernate.mysql.dialect=org.hibernate.dialect.MySQLDialect
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.connection.autocommit=false
hibernate.connection.release_mode=auto

and in the C3p0 logs says

Code:

DEBUG - trace com.mchange.v2.resourcepool.BasicResourcePool@5f7208e2 [managed: 41, unused: 28, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@2afca462)
DEBUG - checkoutStatement: com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache stats -- total size: 50; checked out: 6; num connections: 20; num keys: 50

[C3P0PooledConnectionPoolManager-Helper Thread-#1] DEBUG - com.mchange.v2.c3p0.impl.NewPooledConnection@32db0feb closed by a client.
java.lang.Exception: DEBUG -- CLOSE BY CLIENT STACK TRACE
   at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:627)
   at com.mchange.v2.c3p0.impl.NewPooledConnection.closeMaybeCheckedOut(NewPooledConnection.java:241)
   at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:571)
   at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:1012)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:635)

please help me identify the root cause for this..
Thanks in advance

sanne.grinovero · **Posted:** Tue Sep 23, 2014 5:46 pm

[moved topic to correct section]

Please always include versions of relevant libraries: in this case I'd need to know at least the version of Hibernate ORM and the version of C3P0 to see if I can find something.

Why do you think a "transaction timeout" is related with your connection pool? Sometimes you might simply be doing a too long operation on the database.