Druid-0.2.26 报Could not open JDBC Connection for transaction;

Question

生产环境

Could not open JDBC Connection for transaction; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 50

org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 50

at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:240)

at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)

at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)

at $Proxy196.getIPCity(Unknown Source)

at cn.richinfo.cmail.webmail.web.util.LoginNotifyImpl.getCity(LoginNotifyImpl.java:214)

at cn.richinfo.cmail.webmail.web.util.LoginNotifyImpl.sendLoginNotify(LoginNotifyImpl.java:239)

at cn.richinfo.cmail.webmail.web.controller.login.common.LoginCommon.doLoginResult(LoginCommon.java:338)

at cn.richinfo.cmail.webmail.web.controller.login.common.LoginCommon.doLoginMail(LoginCommon.java:212)

at cn.richinfo.cmail.webmail.web.controller.login.SMAPSSOController.handleRequestInternal(SMAPSSOController.java:122)

at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)

at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)

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)

at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at cn.richinfo.cmail.webmail.web.WebmailCharacterEncodingFilter.doFilterInternal(WebmailCharacterEncodingFilter.java:39)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)

at org.apache.coyote.http11.Http11NioProcessor.process(Http11NioProcessor.java:891)

at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:750)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:2282)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

at java.lang.Thread.run(Thread.java:662)

Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 50

at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1069)

at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:898)

at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4534)

at com.alibaba.druid.filter.stat.StatFilter.dataSource_getConnection(StatFilter.java:661)

at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4530)

--

--

at $Proxy56.getWebUserConfigModelById(Unknown Source)

at cn.richinfo.cmail.basemail.common.authenticate.LoginCmail.setAttrsConfig(LoginCmail.java:421)

at cn.richinfo.cmail.basemail.common.authenticate.LoginCmail.updateCmailUserInfo(LoginCmail.java:371)

at cn.richinfo.cmail.basemail.common.authenticate.AuthenticateHelper.createUserInfo(AuthenticateHelper.java:468)

at cn.richinfo.cmail.basemail.common.authenticate.AuthenticateHelper.login(AuthenticateHelper.java:285)

at cn.richinfo.cmail.webmail.web.controller.login.common.LoginCommon.doLoginMail(LoginCommon.java:210)

at cn.richinfo.cmail.webmail.web.controller.login.SMAPSSOController.handleRequestInternal(SMAPSSOController.java:122)

at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)

at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)

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)

at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at cn.richinfo.cmail.webmail.web.WebmailCharacterEncodingFilter.doFilterInternal(WebmailCharacterEncodingFilter.java:39)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)

at org.apache.coyote.http11.Http11NioProcessor.process(Http11NioProcessor.java:891)

at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:750)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:2282)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

at java.lang.Thread.run(Thread.java:662)

Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 50

at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1069)

at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:898)

at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4534)

at com.alibaba.druid.filter.stat.StatFilter.dataSource_getConnection(StatFilter.java:661)

at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4530)

at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:880)

at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:872)

at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:97)

at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)

at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)

... 43 more

#初始化连接数
db.initialSize=5


#最大连接数量，这个开始是50个，后台故障得不到解决，临时搞100，但问题依旧
db.maxActive=100


#最大空闲连接
db.maxIdle=20


#最小空闲连接
db.minIdle=3


#是否自动回收超时连接
db.removeAbandoned=true


#超时等待时间以毫秒为单位 
db.maxWait=60000
#超时时间(以秒数为单位) defautl:300s
db.removeAbandonedTimeout=180
#连接池中连接，在时间段内一直空闲， 被逐出连接池的时间（默认为30分钟，可以适当做调整，需要和后端服务端的策略配置相关） -->
db.minEvictableIdleTimeMillis=180000
#设置的Evict线程的时间，单位ms，大于0才会开启evict检查线程
db.timeBetweenEvictionRunsMillis=30000

以下是数据源的配置：

<!-- 配置数据源 -->

<bean id="ds_cmail_web" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">

        <property name="driverClassName"><value>${db.web.driverClassName}</value></property>

        <property name="url"><value>${db.web.url}</value></property>

        <property name="username"><value>${db.web.username}</value></property>

        <property name="password"><value>${db.web.password}</value></property>

        <property name="filters" value="stat" />  

        <property name="maxActive"><value>${db.maxActive}</value></property>  

        <property name="initialSize"><value>${db.initialSize}</value></property>  

        <property name="maxWait"><value>${db.maxWait}</value></property>  

        <property name="minIdle"><value>${db.minIdle}</value></property>  

        <property name="timeBetweenEvictionRunsMillis"><value>${db.timeBetweenEvictionRunsMillis}</value></property>  

        <property name="minEvictableIdleTimeMillis"><value>${db.minEvictableIdleTimeMillis}</value></property> 

        <property name="maxOpenPreparedStatements"><value>${db.maxOpenPreparedStatements}</value></property> 

        <property name="removeAbandonedTimeout"><value>${db.removeAbandonedTimeout}</value></property> 

        <property name="validationQuery" value="SELECT 'x'" />  

        <property name="testWhileIdle" value="true" />  

        <property name="testOnBorrow" value="false" />  

        <property name="testOnReturn" value="false" />  

        <property name="removeAbandoned" value="true" /> <!-- 打开removeAbandoned功能 -->  

        <property name="logAbandoned" value="true" /> <!-- 关闭abanded连接时输出错误日志 -->

</bean>

1. 环境是spring+mybatis， 使用Druid做为数据连接池

2. 数据库是做的主-从备份（故障发生时，重启数据库，问题依旧）

3. 故障发生时，重启应用的tomcat，问题还是存在。

发生故障之前的代码运行了4-5天，没有出现问题。突然今天出现，在各种方法尝试没有解决后，把程序回滚到上个版本（数据库操作这一块代码没有变，新版只是增加了几个数据库的操作而已），后台程序正常。  然后局部机器更新到新的程序，问题没有发生。

各位高手有什么解决建议，谢谢！

Answer 1

你这种回滚了的太难查，大致思路是查监控以及去复现

1 diff差异代码，看看是不是上了新的sql或批量处理的代码。

2 系统监控数据库的监控（连接数），如果有的话把慢查询日志打开看看

3 业务日志打开看看你业务日志的响应时间是否增长。