HikariPool-1 - Connection is not available, request timed out after 30001ms.链接池链接数据库超时

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

项目场景：Springboot框架,hibernate框架,mysql数据库,hikari连接池

项目部署到生产环境启动20分钟左右就会报错,报链接池链接数据库超时;
HikariPool-1 - Connection is not available, request timed out after 30001ms.

      @Override
public List selectContractList( String parentId) {
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT * FROM ( select null AS id, a.contract_id as contractIdn" +
        "      ,a.contract_code as contractCoden" +
        "      ,a.project_name as projectNamen" +
        "      ,a.parent_contract_id as parentContractIdn" +
        "      ,a.customer_name as customerNamen" +
        "      ,a.device_name as deviceNamen" +
        "      ,a.contract_type as contractTypen" +
        "      ,a.create_user as createUsern" +
        "      ,a.create_user_name as createUserNamen" +
        "      ,a.flow_process_id as flowProcessId  n" +
        "      ,a.contract_title as contractTitlen" +
        "      ,a.verify_status as verifyStatusn" +
        "      ,a.create_time as createTimen" +
        "      ,a.execute_status as executeStatus  n" +
        "      ,if(isnull(t.subNumber),'0','1') AS subExecuteStatusn" +
        "     ,'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 projectIdn" +
        "  from b_contract_apply an" +
        "  left join (n" +
        "select parent_contract_id,count(*) AS subNumbern" +
        "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 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的框架,这个关闭链接是没有用的!我们需要用事物,去帮我们提交

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

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