事务未提交导致的分页问题

问题背景

“测试过程中遇到一个奇怪的问题，所有分页查询页面在服务启动之后，分页功能正常，系统运行一段时间之后，所有分页查询页面，同一查询条件查询3次，分页功能异常，只显示第一页的记录，页码也只有1页；改变查询条件之后，连续查询三次也出现同样的问题。”

问题排查

此问题一开始出现，以为是查询页码分页问题，开发表示分页功能正常，不知道怎么修改，以为是代码没提交，后面就重新提交代码。

测试更新代码后，重启服务，直接验证查询分页问题正常。

但……一段时间后，发现此问题又出现了。

为了找到问题的根源，我用Jmeter接口测试检测某个分页查询返回的页码数，通过设置响应断言来判断页码返回值。正常情况下，页码大于1，若出现异常，则页码数小于等于1；根据异常出现的时间点，去日志中查找该时间对应的操作记录。通过此方法来定位哪个功能导致了分页异常。

最后发现一个页面的新增功能，在判断记录唯一性时，触发了该问题。

于是，问题重现了。

判断唯一性后，事务未提交，代码如下：

    public SecCodeResponseVo.Builder secInfoSave(SecCodeRequestVo request) {

        SecCodeResponseVo.Builder builder = SecCodeResponseVo.newBuilder();
        Response.Builder responseBuilder = Response.newBuilder();
        JSONObject result = new JSONObject();
        JSONObject reqJSON = JSON.parseObject(request.getReqParam());
        LoginUserInfoBO userInfo = ((JSONObject) reqJSON.get("userInfo")).toJavaObject(LoginUserInfoBO.class);
        BusinessInfoDto businessInfo = ((JSONObject) reqJSON.get("businessInfo")).toJavaObject(BusinessInfoDto.class);
        JSONObject reqParam = (JSONObject) reqJSON.get("reqParam");
        reqParam.put("branchCode", "000");
        reqParam.put("setEmp", "000000");

        String businessCode = businessInfo.getBusinessCode();
        String operationType = OperationTypeEnum.ADD.code;
        String type = "OUOS1016";
        String operType = "update";
        OuSecInfoSendMessageBO ouSecInfoSendMessageBO = reqParam.toJavaObject(OuSecInfoSendMessageBO.class);

        //日志记录json
        JSONArray message = new JSONArray();

        //1.对本地数据进行修改
        TransactionStatus tStatus1 = null;

        try {
            //输入参数校验
            if (!checkReqParam(builder, ouSecInfoSendMessageBO, "add")) {
                return builder;
            }

            ouSecInfoSendMessageBO.setOperType("1");

            TbSecInfo tbSecInfo = reqParam.toJavaObject(TbSecInfo.class);
            tbSecInfo.setEntrustMethodSet(tbSecInfo.getEntrustMethodSet() == null ? null : tbSecInfo.getEntrustMethodSet().replace(",", ""));
            tbSecInfo.setFrbdCnclMthdSet(tbSecInfo.getFrbdCnclMthdSet() == null ? null : tbSecInfo.getFrbdCnclMthdSet().replace(",", ""));
            tbSecInfo.setCreateUserId(userInfo.getOperatorNo());
            tbSecInfo.setCreateTime(new Date());
            tbSecInfo.setUpdateUserId(userInfo.getOperatorNo());
            tbSecInfo.setUpdateTime(new Date());
            tbSecInfo.setSysNo("01");
            tbSecInfo.setSysType("1");
            QueryWrapper<TbSecInfo> tbSecInfoQueryWrapper = new QueryWrapper<>();
            tbSecInfoQueryWrapper.eq("SEC_CODE", tbSecInfo.getSecCode());
            tbSecInfoQueryWrapper.eq("MARKET_CODE", tbSecInfo.getMarketCode());
            tbSecInfoQueryWrapper.eq("SYS_NO", "01");
            Integer tbSecInfoCount = tbSecInfoMapper.selectCount(tbSecInfoQueryWrapper);
            if (tbSecInfoCount != 0) {
                logger.info("\n ### 相同市场和证券代码已在TB_SEC_INFO表中存在，不允许重复 ###\n");
                return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("相同市场和证券代码已在TB_SEC_INFO表中存在，不允许重复"));
            }
            TbSecCodeBO tbSecCodeBO = new TbSecCodeBO();
            tbSecCodeBO.setMarketCode(tbSecInfo.getMarketCode());
            tbSecCodeBO.setSecCode(tbSecInfo.getSecCode());
            List<OuSecInfo> ouSecInfos = ouSecInfoMapper.selectListByMarkCode(tbSecCodeBO);
            if (ouSecInfos.size() >= 1) {
                logger.info("\n ### 相同市场和证券代码已在OU_SEC_INFO表中存在，不允许重复 ###\n");
                return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("相同市场和证券代码已在OU_SEC_INFO表中存在，不允许重复"));
            }
            // tb数据事务
            tStatus1 = transactionManager.getTransaction(transactionDefinition);
            int resultSecInfo = tbSecInfoMapper.insert(tbSecInfo);

            if (resultSecInfo <= 0) {
                //新增或修改tb_sec_info表失败
                logger.info("\n ### 新增或修改tb_sec_info表失败，resultSecInfo ###\n");
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("新增或修改tb_sec_info表失败"));
            }

            QueryWrapper<TbSecEnglishName> tbSecEnglishNameQueryWrapper = new QueryWrapper<>();
            tbSecEnglishNameQueryWrapper.eq("SEC_CODE", tbSecInfo.getSecCode());
            tbSecEnglishNameQueryWrapper.eq("MARKET_CODE", tbSecInfo.getMarketCode());
            tbSecEnglishNameQueryWrapper.eq("SYS_NO", "01");
            Integer tbSecEnglishNameCount = tbSecEnglishNameMapper.selectCount(tbSecEnglishNameQueryWrapper);


            TbSecEnglishName tbSecEnglishName = reqParam.toJavaObject(TbSecEnglishName.class);
            tbSecEnglishName.setCreateUserId(userInfo.getOperatorNo());
            tbSecEnglishName.setCreateTime(new Date());
            tbSecEnglishName.setUpdateUserId(userInfo.getOperatorNo());
            tbSecEnglishName.setUpdateTime(new Date());
            tbSecEnglishName.setSecName(reqParam.getString("secAllEnglishName"));
            tbSecEnglishName.setSysNo("01");
            tbSecEnglishName.setSysType("1");
            int resultSecEnglishName;
            if (tbSecEnglishNameCount == 0) {
                //没有则新增
                resultSecEnglishName = tbSecEnglishNameMapper.insert(tbSecEnglishName);
            } else {
                //有则修改
                resultSecEnglishName = tbSecEnglishNameMapper.update(tbSecEnglishName, tbSecEnglishNameQueryWrapper);
            }

            if (resultSecEnglishName <= 0) {
                //新增或修改tb_sec_english表失败
                logger.info("\n ### 新增或修改tb_sec_english表失败，resultSecEnglishName ###\n");
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("新增或修改tb_sec_english表失败"));
            }

            //插入成功后调用日志记录
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("tableName", "TB_SEC_INFO");
            jsonObject1.put("sourceObject", null);
            jsonObject1.put("targetObject", JSON.toJSONString(tbSecInfo));
            message.add(jsonObject1);
            JSONObject jsonObject2 = new JSONObject();
            jsonObject2.put("tableName", "TB_SEC_ENGLISH_NAME");
            jsonObject2.put("sourceObject", null);
            jsonObject2.put("targetObject", JSON.toJSONString(tbSecEnglishName));
            message.add(jsonObject2);
            RecordLogResponseBo<BusinessOpLogTmp> insertBusinessLog = insertBusinessLog(businessCode, operationType, userInfo, JSON.toJSONString(message));

            if (insertBusinessLog.getResultCode().equals(ResponseCodeEnum.SUCCESS.code) && !ObjectUtils.isEmpty(insertBusinessLog.getResultObj())) {
                BusinessOpLogTmp resultObj = insertBusinessLog.getResultObj();
                //获取业务流水号
                String workFlowNo = resultObj.getWorkflowNo();
                result.put("workFlowNo", workFlowNo);
            } else {
                if (null != tStatus1) {
                    transactionManager.rollback(tStatus1);
                }
                logger.info("\n ### 日志记录失败 ###\n");
return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("日志记录失败"));
            }

            transactionManager.commit(tStatus1);
            logger.info("\n ### 本地记录已落表 ###\n");

        } catch (Exception e) {
            e.printStackTrace();
        }

 

分页代码：

    public IPage<DataFeeTemplatePageResponseBo> queryFeeTemplatePage(DataFeeTemplatePageRequestBo dataFeeTemplatePageRequestBo, LoginUserInfoDTO loginUserInfoDTO) {
        FeeTemplateConditionBo condition = dataFeeTemplatePageRequestBo.getCondition();
        Page pagger = dataFeeTemplatePageRequestBo.getPagger();
        List<OrderBO> order = dataFeeTemplatePageRequestBo.getOrder();
        //拼接排序sql段
        StringBuffer orderStr = new StringBuffer("");
        if (order != null && !order.isEmpty()) {
            for (int i = 0; i < order.size(); i++) {
                OrderBO orderBO = order.get(i);
                if (i != order.size()) {
                    orderStr.append(orderBO.getColumn() + " ")
                            .append(orderBO.getType() + " , ");
                } else {
                    orderStr.append(orderBO.getColumn() + " ")
                            .append(orderBO.getType());
                }
            }
        }
        //拼接过滤sql段
        StringBuffer filterStr = new StringBuffer("");
        List<FilterBO> filter = dataFeeTemplatePageRequestBo.getFilter();
        if (filter != null && !filter.isEmpty()) {
            for (int i = 0; i < filter.size(); i++) {
                FilterBO filterBO = filter.get(i);
                filterStr.append(filterBO.getColumn())
                        .append(" ")
                        .append(filterBO.getType())
                        .append(" ")
                        .append(filterBO.getValue());
            }
        }

        IPage<DataFeeTemplatePageResponseBo> dataFeeTemplatePageResponseBoIPage = tbDataFeeTemplateMapper.queryFeeTemplatePage(
                pagger, condition, orderStr.toString(), filterStr.toString(), loginUserInfoDTO.getOperateBranchs());

        return dataFeeTemplatePageResponseBoIPage;
    }

 

分页调用时会读取到未提交的事务，导致读取的页码数是1（即检查新增操作重复记录时返回的记录数1）。

问题解决：

修改后代码：

    public SecCodeResponseVo.Builder secInfoSave(SecCodeRequestVo request) {

        SecCodeResponseVo.Builder builder = SecCodeResponseVo.newBuilder();
        Response.Builder responseBuilder = Response.newBuilder();
        JSONObject result = new JSONObject();
        JSONObject reqJSON = JSON.parseObject(request.getReqParam());
        LoginUserInfoBO userInfo = ((JSONObject) reqJSON.get("userInfo")).toJavaObject(LoginUserInfoBO.class);
        BusinessInfoDto businessInfo = ((JSONObject) reqJSON.get("businessInfo")).toJavaObject(BusinessInfoDto.class);
        JSONObject reqParam = (JSONObject) reqJSON.get("reqParam");
        reqParam.put("branchCode", "000");
        reqParam.put("setEmp", "000000");

        String businessCode = businessInfo.getBusinessCode();
        String operationType = OperationTypeEnum.ADD.code;
        String type = "OUOS1016";
        String operType = "update";
        OuSecInfoSendMessageBO ouSecInfoSendMessageBO = reqParam.toJavaObject(OuSecInfoSendMessageBO.class);

        //日志记录json
        JSONArray message = new JSONArray();

        //1.对本地数据进行修改
        TransactionStatus tStatus1 = null;

        try {
            //输入参数校验
            if (!checkReqParam(builder, ouSecInfoSendMessageBO, "add")) {
                return builder;
            }

            ouSecInfoSendMessageBO.setOperType("1");

            TbSecInfo tbSecInfo = reqParam.toJavaObject(TbSecInfo.class);
            tbSecInfo.setEntrustMethodSet(tbSecInfo.getEntrustMethodSet() == null ? null : tbSecInfo.getEntrustMethodSet().replace(",", ""));
            tbSecInfo.setFrbdCnclMthdSet(tbSecInfo.getFrbdCnclMthdSet() == null ? null : tbSecInfo.getFrbdCnclMthdSet().replace(",", ""));
            tbSecInfo.setCreateUserId(userInfo.getOperatorNo());
            tbSecInfo.setCreateTime(new Date());
            tbSecInfo.setUpdateUserId(userInfo.getOperatorNo());
            tbSecInfo.setUpdateTime(new Date());
            tbSecInfo.setSysNo("01");
            tbSecInfo.setSysType("1");
            QueryWrapper<TbSecInfo> tbSecInfoQueryWrapper = new QueryWrapper<>();
            tbSecInfoQueryWrapper.eq("SEC_CODE", tbSecInfo.getSecCode());
            tbSecInfoQueryWrapper.eq("MARKET_CODE", tbSecInfo.getMarketCode());
            tbSecInfoQueryWrapper.eq("SYS_NO", "01");
            Integer tbSecInfoCount = tbSecInfoMapper.selectCount(tbSecInfoQueryWrapper);
            if (tbSecInfoCount != 0) {
                logger.info("\n ### 相同市场和证券代码已在TB_SEC_INFO表中存在，不允许重复 ###\n");
                return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("相同市场和证券代码已在TB_SEC_INFO表中存在，不允许重复"));
            }
            TbSecCodeBO tbSecCodeBO = new TbSecCodeBO();
            tbSecCodeBO.setMarketCode(tbSecInfo.getMarketCode());
            tbSecCodeBO.setSecCode(tbSecInfo.getSecCode());
            List<OuSecInfo> ouSecInfos = ouSecInfoMapper.selectListByMarkCode(tbSecCodeBO);
            if (ouSecInfos.size() >= 1) {
                logger.info("\n ### 相同市场和证券代码已在OU_SEC_INFO表中存在，不允许重复 ###\n");
                return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("相同市场和证券代码已在OU_SEC_INFO表中存在，不允许重复"));
            }
            // tb数据事务
            tStatus1 = transactionManager.getTransaction(transactionDefinition);
            int resultSecInfo = tbSecInfoMapper.insert(tbSecInfo);

            if (resultSecInfo <= 0) {
                //新增或修改tb_sec_info表失败
                logger.info("\n ### 新增或修改tb_sec_info表失败，resultSecInfo ###\n");
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("新增或修改tb_sec_info表失败"));
            }

            QueryWrapper<TbSecEnglishName> tbSecEnglishNameQueryWrapper = new QueryWrapper<>();
            tbSecEnglishNameQueryWrapper.eq("SEC_CODE", tbSecInfo.getSecCode());
            tbSecEnglishNameQueryWrapper.eq("MARKET_CODE", tbSecInfo.getMarketCode());
            tbSecEnglishNameQueryWrapper.eq("SYS_NO", "01");
            Integer tbSecEnglishNameCount = tbSecEnglishNameMapper.selectCount(tbSecEnglishNameQueryWrapper);


            TbSecEnglishName tbSecEnglishName = reqParam.toJavaObject(TbSecEnglishName.class);
            tbSecEnglishName.setCreateUserId(userInfo.getOperatorNo());
            tbSecEnglishName.setCreateTime(new Date());
            tbSecEnglishName.setUpdateUserId(userInfo.getOperatorNo());
            tbSecEnglishName.setUpdateTime(new Date());
            tbSecEnglishName.setSecName(reqParam.getString("secAllEnglishName"));
            tbSecEnglishName.setSysNo("01");
            tbSecEnglishName.setSysType("1");
            int resultSecEnglishName;
            if (tbSecEnglishNameCount == 0) {
                //没有则新增
                resultSecEnglishName = tbSecEnglishNameMapper.insert(tbSecEnglishName);
            } else {
                //有则修改
                resultSecEnglishName = tbSecEnglishNameMapper.update(tbSecEnglishName, tbSecEnglishNameQueryWrapper);
            }

            if (resultSecEnglishName <= 0) {
                //新增或修改tb_sec_english表失败
                logger.info("\n ### 新增或修改tb_sec_english表失败，resultSecEnglishName ###\n");
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("新增或修改tb_sec_english表失败"));
            }

            //插入成功后调用日志记录
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("tableName", "TB_SEC_INFO");
            jsonObject1.put("sourceObject", null);
            jsonObject1.put("targetObject", JSON.toJSONString(tbSecInfo));
            message.add(jsonObject1);
            JSONObject jsonObject2 = new JSONObject();
            jsonObject2.put("tableName", "TB_SEC_ENGLISH_NAME");
            jsonObject2.put("sourceObject", null);
            jsonObject2.put("targetObject", JSON.toJSONString(tbSecEnglishName));
            message.add(jsonObject2);
            RecordLogResponseBo<BusinessOpLogTmp> insertBusinessLog = insertBusinessLog(businessCode, operationType, userInfo, JSON.toJSONString(message));

            if (insertBusinessLog.getResultCode().equals(ResponseCodeEnum.SUCCESS.code) && !ObjectUtils.isEmpty(insertBusinessLog.getResultObj())) {
                BusinessOpLogTmp resultObj = insertBusinessLog.getResultObj();
                //获取业务流水号
                String workFlowNo = resultObj.getWorkflowNo();
                result.put("workFlowNo", workFlowNo);
            } else {
                if (null != tStatus1) {
                    transactionManager.rollback(tStatus1);
                }
                logger.warn("\n ### 日志记录失败 ###\n");
                return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("日志记录失败"));
            }

            transactionManager.commit(tStatus1);
            logger.info("\n ### 本地记录已落表 ###\n");

        } catch (Exception e) {
            if (null != tStatus1) {
                transactionManager.rollback(tStatus1);
            }
            logger.error("\n ### 证券代码设置，数据持久化异常 ###\n", e);
            return builder.setResponse(Response.newBuilder().setCode(ResponseCodeEnum.FAILED.code).setMsg("证券代码设置失败"));
        }

修改后验证通过。