项目场景:
提示:这里简述项目相关背景:
项目场景:Springboot框架,hibernate框架,mysql数据库,hikari连接池
问题描述
项目部署到生产环境启动20分钟左右就会报错,报链接池链接数据库超时;
HikariPool-1 - Connection is not available, request timed out after 30001ms.
dao代码展示:
这是一种比较老并且恶心的sql写法,也是第一次接触这种框架
@Override
public List<BContractApplyListVO> selectContractList( String parentId) {
StringBuffer sql = new StringBuffer();
sql.append("SELECT * FROM ( select null AS id, a.contract_id as contractId\n" +
" ,a.contract_code as contractCode\n" +
" ,a.project_name as projectName\n" +
" ,a.parent_contract_id as parentContractId\n" +
" ,a.customer_name as customerName\n" +
" ,a.device_name as deviceName\n" +
" ,a.contract_type as contractType\n" +
" ,a.create_user as createUser\n" +
" ,a.create_user_name as createUserName\n" +
" ,a.flow_process_id as flowProcessId \n" +
" ,a.contract_title as contractTitle\n" +
" ,a.verify_status as verifyStatus\n" +
" ,a.create_time as createTime\n" +
" ,a.execute_status as executeStatus \n" +
" ,if(isnull(t.subNumber),'0','1') AS subExecuteStatus\n" +
" ,'0' AS appType,a.del_flag AS delFlag,a.tenant_id AS tenantId,a.business_type AS businessType,a.customer_credit_code AS customerCreditCode,a.project_id as projectId\n" +
" from b_contract_apply a\n" +
" left join (\n" +
"select parent_contract_id,count(*) AS subNumber\n" +
"from b_contract_apply bca \n" +
"where bca.parent_contract_id IS NOT NULL \n" +
"and bca.execute_status = 0 \n" +
"GROUP BY bca.parent_contract_id HAVING COUNT(*) > 0 \n" +
" ) t on t.parent_contract_id = a.contract_id \n");
sql.append(" UNION ALL " +
"SELECT \n" +
" bccf.id AS id, \n" +
" bccf.contract_id AS contractId, \n" +
" bccf.contract_code AS contractCode, \n" +
" bccf.project_name AS projectName, \n" +
" '' AS parentContractId, \n" +
" bccf.customer_name AS customerName, \n" +
" bccf.device_name AS deviceName, \n" +
" bccf.contract_type AS contractType, \n" +
" bccf.create_user AS createUser, \n" +
" bccf.flow_process_id as flowProcessId, \n" +
" bccf.create_user_name AS createUserName,\n" +
" bccf.contract_name AS contractTitle, \n" +
" bccf.verify_status AS verifyStatus, \n" +
" bccf.create_time AS createTime, \n" +
" bccf.execute_status AS executeStatus, \n" +
" '' AS subExecuteStatus, \n" +
" '1' AS appType, \n" +
" bccf.del_flag AS delFlag, \n" +
" bccf.tenant_id AS tenantId, \n" +
" '' AS businessType,bccf.customer_file_name AS customerCreditCode,'' AS projectId \n" +
" FROM b_contract_change_flow bccf ) ai \n" +
" where ai.delFlag = 0 ");
if (!StringUtils.isEmpty(parentId)){
sql.append("and ai.parentContractId = '").append(parentId).append("'");
}else {
sql.append(" and ai.verifyStatus = 1 and ai.executeStatus = 0");
}
Query query = entityManager.createNativeQuery(sql.toString());
List<BContractApplyListVO> list = query.unwrap(NativeQuery.class).setResultTransformer(new FluentHibernateResultTransformer(BContractApplyListVO.class)).list();
EntityManagerFactoryUtils.closeEntityManager(this.entityManager);
return list;
}
原因分析:
问题的原因报错是说的连接池链接不上数据库,从而导致系统崩溃,这个微服务所有的功能都不能够正常运行.
这个项目有非常多的定时任务
一直想着是不是定时任务太多,链接池数量太少,没有自动提交链接的配置呢?
与是就百度各种查找修改链接池配置的问题; 但是测试后发现结果并不解人意, 但是发现由原来20分钟报错延迟到了2小时之后.就觉得有点作用.然后又把定时任务由3分钟一次改到10分钟一次. 报错时间又延长到8小时
这个解决办法就是治标不治本!
解决方案:
@Transactional:事物注解
看到这里很多同学就会问?这个跟事物有啥关系呢.
那确实就是有关系,这种操作数据库的JPA可以说是非常的老的,它不像现在的mybatis,mybatis-Plus.这些框架底层都是会在对表进行增删改查之后都是会提交资源,断开链接的.
细心的同学看dao帖出来的代码就能看到,这个有EntityManagerFactoryUtils.closeEntityManager(this.entityManager) 去关闭链接呀! 因为是SpringBoot的框架,这个关闭链接是没有用的!我们需要用事物,去帮我们提交
这个问题也是上到生产,看到领导被客户叼.自己也是被领导一顿好叼啊. 但是呢挨叼也是工作的一部分呀! 自己太菜了!
努力学习!加油....一起做人上人