关于spring+hibernate连接池连接数不够用的解决方案

最近在做一个电商O2O项目，O2O项目大家都知道，会有订单，有订单就会存在订单不能完结的BUG。

所有系统要每天定时时间去处理掉这些没有完结的订单，由于我们完结订单的时候有很多连带业务需要处理，之前部分连带业务是另外一个同事开发的，由于他考虑到他那一块事物的连带性（所有数据变动必须都成功才算成功），

他就用的Transaction tx = beginTransaction();去处理事物，然后在我用晚上用定时器去执行自动完结订单的时候却发现，

只能处理十多个订单数据库连接数就满了，然后去看项目运行日志发现以下错误：

2016-08-02 00:30:38 [org.hibernate.util.JDBCExceptionReporter:233] - SQL Error: 0, SQLState: null
2016-08-02 00:30:38 [org.hibernate.util.JDBCExceptionReporter:234] - An attempt by a client to checkout a Connection has timed out.
2016-08-02 00:30:38 [org.hibernate.util.JDBCExceptionReporter:233] - SQL Error: 0, SQLState: null
2016-08-02 00:30:38 [org.hibernate.event.def.DefaultLoadEventListener:160] - Error performing load command
org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1700) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:801) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.loader.Loader.loadEntity(Loader.java:2037) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:86) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:76) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3294) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:496) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:477) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:227) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:285) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:152) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:1090) [hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.impl.SessionImpl.get(SessionImpl.java:1005) [hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.hibernate.impl.SessionImpl.get(SessionImpl.java:998) [hibernate-core-3.6.10.Final.jar:3.6.10.Final]
at org.springframework.orm.hibernate3.HibernateTemplate$1.doInHibernate(HibernateTemplate.java:519) [spring-orm-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406) [spring-orm-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) [spring-orm-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:512) [spring-orm-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:506) [spring-orm-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at cn.gk.dao.impl.BaseDaoImpl.get(BaseDaoImpl.java:80) [BaseDaoImpl.class:na]
at cn.gk.dao.impl.OrderTakeawayDaoImpl.getOrderTakeaway(OrderTakeawayDaoImpl.java:26) [OrderTakeawayDaoImpl.class:na]
at cn.gk.service.impl.BaseServiceImpl.rebatePoints(BaseServiceImpl.java:101) [BaseServiceImpl.class:na]
at cn.gk.service.impl.biz.BizOrderComponentServiceImpl.orderAutoClear(BizOrderComponentServiceImpl.java:172) [BizOrderComponentServiceImpl.class:na]
at cn.gk.service.impl.biz.BizOrderComponentServiceImpl$$FastClassByCGLIB$$5d18741c.invoke(<generated>) [com.springsource.net.sf.cglib-2.2.0.jar:na]
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) [com.springsource.net.sf.cglib-2.2.0.jar:na]
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689) [spring-aop-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) [spring-aop-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90) [spring-aop-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [spring-aop-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622) [spring-aop-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at cn.gk.service.impl.biz.BizOrderComponentServiceImpl$$EnhancerByCGLIB$$5289c1ca.orderAutoClear(<generated>) [com.springsource.net.sf.cglib-2.2.0.jar:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_11]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_11]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_11]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_11]
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:64) [spring-context-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53) [spring-context-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81) [spring-context-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_11]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_11]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_11]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [na:1.8.0_11]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_11]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_11]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_11]
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) ~[c3p0-0.9.1.2.jar:0.9.1.2]
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65) ~[c3p0-0.9.1.2.jar:0.9.1.2]
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:527) ~[c3p0-0.9.1.2.jar:0.9.1.2]
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) ~[c3p0-0.9.1.2.jar:0.9.1.2]
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81) ~[spring-orm-3.1.4.RELEASE.jar:3.1.4.RELEASE]
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
... 48 common frames omitted
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@6905c188 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1317) ~[c3p0-0.9.1.2.jar:0.9.1.2]
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557) ~[c3p0-0.9.1.2.jar:0.9.1.2]
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477) ~[c3p0-0.9.1.2.jar:0.9.1.2]
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525) ~[c3p0-0.9.1.2.jar:0.9.1.2]
... 51 common frames omitted

在网上查阅很多资料都没发现是什么错误，在我无意中注释掉调用我同事的代码后我发现，尽然项目畅通无阻了。

奇怪吧！他就只是简单的用了一下hibernate的事物而已，不知道影响到我这边啊。

下面给你看下他的实现方式：

protected String rebatePoints(int orderId, int orderType) {

Transaction tx = beginTransaction();

.....

try{

tx.commit();

}cath{

......

tx.rollback();

}

}

public Transaction beginTransaction() {
return SessionFactoryUtils.getSession(gethTemplate().getSessionFactory(), true).beginTransaction();
}

看出问题来了么，说实话我当时还真没有。后来我渐渐感觉到他这个事物有问题，只有打开没关闭。也就是说只有等线程死了之后连接才会被释放。虽然找到了原因但是还是没法解决，因为我从来没用过Transaction做数据库事物处理。

奈何老天眷顾，夜晚托梦于我。赐予了解决之道 如下：

protected String rebatePoints(int orderId, int orderType) {

Session session=getSession();

Transaction tx =session.beginTransaction();

.....

try{

tx.commit();

}cath{

......

tx.rollback();

}

closeSesssion(session);

}

public Session getSession() {
return SessionFactoryUtils.getSession(gethTemplate().getSessionFactory(), true);
}

public void closeSession(Session session) {
SessionFactoryUtils.closeSession(session);
}

把获得session和关闭session的方法进行封装，通过session.beginTransaction()来得到Transaction，既不用改动业务，又完美解决问题。

完美解决~！