项目场景：

提示：这里简述项目相关背景：

项目场景：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的框架,这个关闭链接是没有用的!我们需要用事物,去帮我们提交

这个问题也是上到生产,看到领导被客户叼.自己也是被领导一顿好叼啊. 但是呢挨叼也是工作的一部分呀! 自己太菜了!

努力学习!加油....一起做人上人