GYBYPtree 2022-11-12 05:18 采纳率: 50%
浏览 303
已结题

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

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

公司上线了一个扫码支付功能,每天中午吃饭的人在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

展开全部

  • 写回答

5条回答 默认 最新

  • 东芃9394 2022-11-12 05:36
    关注
    获得3.30元问题酬金

    你好,你这个问题解决不难,主要原因是因为使用了默认的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.在现有基础上进行配置修改,主要寻找配置思路是检测数据库连接时间设置短一些,空闲连接数设置少一些;

    评论
  • 游一游走一走 2022-11-12 06:06
    关注
    获得4.05元问题酬金

    把检查项都加上看看

    spring.datasource.validation-query=select 1
    spring.datasource.test-on-connect=true
    spring.datasource.connection-test-query=select 1
    
    评论
  • Jackyin0720 2022-11-12 05:42
    关注

    提供参考实例,链接:https://www.cnblogs.com/weibanggang/p/9295228.html
    【实例讲解详细,代码清晰,值得借鉴】

    评论
  • yy64ll826 2022-11-16 07:33
    关注

    com.microsoft.sqlserver.jdbc.SQLServerException: 该连接已关闭的解决方案
    https://blog.csdn.net/gaoqiao1988/article/details/9235853
    希望对你有所帮助

    评论
  • *拯 2022-11-19 15:02
    关注
    评论
编辑
预览

报告相同问题?

问题事件

  • 系统已结题 11月19日
  • 创建了问题 11月12日

悬赏问题

  • ¥15 根据图片内容用Python解决问题
  • ¥25 完成Python爬虫任务
  • ¥20 为什么很少有分析折叠共源共栅放大器的psrr的资料,想分析,怎么入手
  • ¥25 完成Python任务
  • ¥15 java应用无法获取nacos导入的yml文件配置
  • ¥15 群晖域名解析到127.0.0.1
  • ¥15 mr_keep全是FALSE,但找不出来问题在哪
  • ¥15 电脑成服务器了怎么处理
  • ¥15 Ubuntu18.04怎样远程链接
  • ¥15 GO富集分析设置数据库
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部