频繁调用的接口中数据库连接无故关闭，出现com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed异常，接口无法响应

Question

问题遇到的现象和发生背景

公司上线了一个扫码支付功能，每天中午吃饭的人在300左右，高峰时期同时扫码就餐的最多不过15个人，但现在每隔两三天支付接口中数据库连接都会关闭，出现com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed和后面请求出现的java.lang.IllegalStateException: Session/EntityManager is closed的异常，导致异常后扫码没有反应，重启tomcat后可以正常运行，但两三天后又会出现此问题。贴上接口代码、异常信息和报错位置，请大家帮忙看下

用代码块功能插入代码，请勿粘贴截图

报错位置是接口中自己封装的用于获取单据id值的代码，以下是支付接口

/**
     * 用户消费支付
     * 
     * @param request
     * @return
     * @throws IOException
     */
    public Result<?> paySaleInfo(HttpServletRequest request, PaymentOrder order) throws IOException {
        User user = ContextUtils.getLoginUser();
        log.info("消费支付:{},操作用户:{}", JSONObject.toJSONString(order), user);
        Date date = new Date();

        boolean isNew = false;
        if (order.getId() == null) {
        **    order.setId(billNoService.getMaxIntegerID("PaymentOrder"));   **  //报错位置，获取单据id
            String prefix = "PAY";
            order.setBillNo(billNoService.getBillNo(prefix, "PaymentOrder"));
            order.setDate(date);
            isNew = true;
        } else {
            if(JpaUtil.linq(PaymentOrder.class).equal("id", order.getId()).exists()) {
                PaymentOrder bill = JpaUtil.linq(PaymentOrder.class).equal("id", order.getId()).findOne();
                if (bill.getStatus().equals("已支付")) {
                    return Result.error("此订单交易已结束");
                }
            }else {
                return Result.error("此订单不存在");
            }
        }
        /**剩余代码省略*/

以下是自己封装的获取id值的方法，方法加了同步，按理说不存在线程不安全的问题啊

/**
     *     获取主键
     * @param 前缀
     * @param 表名
     * @return
     */
    public synchronized Integer getMaxIntegerID(String tableName) {
        int no = 1;
        BillNo entity;
**        if(JpaUtil.linq(BillNo.class).equal("tableName", tableName).equal("prefix", "ID").exists()) {    **     //报错位置
            entity = JpaUtil.linq(BillNo.class).equal("tableName", tableName).equal("prefix", "ID").findOne();
            no = entity.getCount();
            no = no + 1;
            entity.setCount(no);
            JpaUtil.mergeAndFlush(entity);
        }else {
            entity = new BillNo();
            entity.setTableName(tableName);
            entity.setPrefix("ID");
            entity.setCount(no);
            JpaUtil.persistAndFlush(entity);
        }
        return entity.getCount();
    }

以下是数据库部分配置

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.continue-on-error=true
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=true
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=100
spring.datasource.hikari.idle-timeout=500000
spring.datasource.hikari.max-lifetime=540000
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.pool-name=MyHikariCPTT

运行结果及报错内容

日志异常：

2022-11-12 11:36:02.709 [Thread-17360] INFO  org.apache.catalina.core.StandardWrapper[173] - 正在等待为Servlet[dispatcherServlet]释放[19]实例
2022-11-12 11:36:03.804 [Thread-17360] INFO  org.apache.catalina.core.StandardWrapper[173] - 正在等待为Servlet[dispatcherServlet]释放[19]实例
2022-11-12 11:36:04.898 [Thread-17360] INFO  org.apache.catalina.core.StandardWrapper[173] - 正在等待为Servlet[dispatcherServlet]释放[19]实例
2022-11-12 11:36:05.023 [Thread-17360] INFO  com.zaxxer.hikari.HikariDataSource[381] - MyHikariCPTT - Shutdown initiated...
2022-11-12 11:36:05.023 [Thread-17360] INFO  com.zaxxer.hikari.HikariDataSource[383] - MyHikariCPTT - Shutdown completed.
2022-11-12 11:36:05.023 [http-nio-8091-exec-15] WARN  com.zaxxer.hikari.pool.ProxyConnection[161] - MyHikariCPTT - Connection ConnectionID:5511 ClientConnectionId: 741edc07-2f2f-4cf4-9f06-61021c3d47c1 marked as broken because of SQLSTATE(08S01), ErrorCode(0)
com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654)
    at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1789)
    at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:4838)
    at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6150)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:402)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2168)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)
    at org.hibernate.loader.Loader.doQuery(Loader.java:938)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2692)
    at org.hibernate.loader.Loader.doList(Loader.java:2675)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)
    at org.hibernate.loader.Loader.list(Loader.java:2502)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1489)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
    at org.hibernate.query.Query.getResultList(Query.java:146)
    at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72)
    at com.bstek.bdf3.dorado.jpa.lin.impl.LinqImpl.executeCountQuery(LinqImpl.java:583)
    at com.bstek.bdf3.dorado.jpa.lin.impl.LinqImpl.count(LinqImpl.java:567)
    at com.bstek.bdf3.dorado.jpa.lin.impl.LinqImpl.exists(LinqImpl.java:576)

**    at com.cqjysoft.tt.business.common.service.GenaralBillNoService.getMaxIntegerID(GenaralBillNoService.java:56)
    at com.cqjysoft.tt.business.common.service.GenaralBillNoService$$FastClassBySpringCGLIB$$e99e7983.invoke(<generated>)
    **
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
    at com.cqjysoft.tt.business.common.service.GenaralBillNoService$$EnhancerBySpringCGLIB$$ae140de5.getMaxIntegerID(<generated>)
    at com.cqjysoft.tt.business.order.service.OrderService.paySaleInfo(OrderService.java:700)
    at com.cqjysoft.tt.business.order.service.OrderService$$FastClassBySpringCGLIB$$a4032cda.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
    at com.cqjysoft.tt.business.order.service.OrderService$$EnhancerBySpringCGLIB$$71d16c20.paySaleInfo(<generated>)
    at com.cqjysoft.tt.business.order.controller.OrderController.paySaleInfo(OrderController.java:103)
    at sun.reflect.GeneratedMethodAccessor478.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle

Answer 1

你好，你这个问题解决不难，主要原因是因为使用了默认的hikari数据库连接池，而没有正确配置数据库连接池；
主要解决方法：
1.使用druid数据库连接池，解决相对简单一些，给一个成功的参考配置

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://ip:3306/zxl_sso_dev?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.datasource.username=用户名
spring.datasource.password=密码
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.initial-size=10
spring.datasource.druid.max-wait=3000
spring.datasource.druid.min-idle=50
spring.datasource.druid.max-active=100

2.在现有基础上进行配置修改，主要寻找配置思路是检测数据库连接时间设置短一些，空闲连接数设置少一些；