定时任务里面事务不生效问题
下面是一个定时任务中事务不生效的场景。 报出的异常是：Unexpected error occurred in scheduled task java.lang.RuntimeException: 清理积分过程中发生异常
@Scheduled(cron = "*/30 * * * * ? ")
public void cleanPoint() {
log.info("**********************************开始清理一年前这一天的用户积分**********************************");
// 1. 获取当前日期
LocalDate now = LocalDate.now();
log.info("当前日期：{}", now);
// 2. 获取一年前的日期
LocalDate yearAgo = now.minusYears(1);
log.info("一年前的日期：{}", yearAgo);
// 3. 批量更新一年前这一天的数据，将其balance值设置为0
int pageNum = 1;
while (true) {
// 3.1 分批次更新user_assets_details表，每次5000条数据，将其balance设置为0
Page<UserAssetsDetailsCleanBO> page = new Page<>(pageNum, 500);
List<UserAssetsDetailsCleanBO> detailsList = userAssetsDetailsService.listInfo(yearAgo, page); // 查询一年前这一天的数据（id, 用户id，余额之和，隆鑫积分之和，咕咕行积分之和）
if (CollectionUtils.isNotEmpty(detailsList)) {
cleanPoint(detailsList, page);
pageNum++;
} else {
break;
}
}
log.info("**********************************结束清理一年前这一天的用户积分**********************************");
}
public void cleanPoint(List<UserAssetsDetailsCleanBO> detailsList, Page<UserAssetsDetailsCleanBO> page) {
log.info("**********************************开始清理一年前这一天的用户积分**********************************");
DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
TransactionStatus status = transactionManager.getTransaction(def);
try {
// 3.1 分批次更新user_assets_details表，每次5000条数据，将其balance设置为0
log.info("成功查询出了一年前这一天的数据，共{}条数据，采用分批处理，总共会执行{}次批量更新，当前是第{}次开始执行", detailsList.size(), page.getPages(), page.getCurrent());
log.info("当前是第{}次开始批量更新 user_assets_details 表", page.getCurrent());
if (CollectionUtils.isNotEmpty(detailsList)) {
List<UserAssetsDetails> newList = new ArrayList<>();
for (UserAssetsDetailsCleanBO bo : detailsList) {
UserAssetsDetails vo = new UserAssetsDetails();
vo.setId(bo.getId());
vo.setBalance(0);
newList.add(vo);
if (bo.getLoncinScore() == null) {
bo.setLoncinScore(0);
}
if (bo.getGuguxScore() == null) {
bo.setGuguxScore(0);
}
if (bo.getTotalScore() == null) {
bo.setTotalScore(0);
}
}
userAssetsDetailsService.updateBatchById(newList);
log.info("结束第{}次批量更新 user_assets_details 表", page.getCurrent());
log.info("当前是第{}次开始批量保存 user_assets_bill 表", page.getCurrent());
// 3.2 将数据存入user_assets_bill用户资产流水表中
List<UserAssetsDetailsCleanBO> distinctDetailsList = detailsList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(UserAssetsDetailsCleanBO::getUserId))), ArrayList::new)); // 根据用户id去重
if (CollectionUtils.isNotEmpty(distinctDetailsList)) {
for (UserAssetsDetailsCleanBO details : distinctDetailsList) {
Long userId = details.getUserId();
// 判断该用户是否已存入过资产流水表中
boolean containsUserId = cacheService.isContainsUserId(userId);
if (containsUserId) {
continue;
}
Integer totalScore = details.getTotalScore();
Integer guguxScore = details.getGuguxScore();
Integer loncinScore = details.getLoncinScore();
boolean b = userScoreBusinessNewService.scoreExpired(userId, totalScore, guguxScore, loncinScore);
cacheService.setUserId(userId);
if (!b) {
log.error("用户{}的积分清理失败", userId);
}
}
}
log.info("结束第{}次批量保存 user_assets_bill 表", page.getCurrent());
}
int a = 1/0;
} catch (Exception e) {
e.printStackTrace();
log.error("清理积分过程中发生异常", e);
throw new RuntimeException("清理积分过程中发生异常");
}
}
解决方案如下，手动事务：
// 手动事务
@Resource(name = "transactionManager")
private DataSourceTransactionManager transactionManager;
//@XxlJob("EverydayCleanPointJobHandler")
@Scheduled(cron = "*/30 * * * * ? ")
public void cleanPoint() {
log.info("**********************************开始清理一年前这一天的用户积分**********************************");
// 1. 获取当前日期
LocalDate now = LocalDate.now();
log.info("当前日期：{}", now);
// 2. 获取一年前的日期
LocalDate yearAgo = now.minusYears(1);
log.info("一年前的日期：{}", yearAgo);
// 3. 批量更新一年前这一天的数据，将其balance值设置为0
int pageNum = 1;
while (true) {
// 3.1 分批次更新user_assets_details表，每次5000条数据，将其balance设置为0
Page<UserAssetsDetailsCleanBO> page = new Page<>(pageNum, 500);
List<UserAssetsDetailsCleanBO> detailsList = userAssetsDetailsService.listInfo(yearAgo, page); // 查询一年前这一天的数据（id, 用户id，余额之和，隆鑫积分之和，咕咕行积分之和）
if (CollectionUtils.isNotEmpty(detailsList)) {
cleanPoint(detailsList, page);
pageNum++;
} else {
break;
}
}
log.info("**********************************结束清理一年前这一天的用户积分**********************************");
}
public void cleanPoint(List<UserAssetsDetailsCleanBO> detailsList, Page<UserAssetsDetailsCleanBO> page) {
log.info("**********************************开始清理一年前这一天的用户积分**********************************");
DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
TransactionStatus status = transactionManager.getTransaction(def);
try {
// 3.1 分批次更新user_assets_details表，每次5000条数据，将其balance设置为0
log.info("成功查询出了一年前这一天的数据，共{}条数据，采用分批处理，总共会执行{}次批量更新，当前是第{}次开始执行", detailsList.size(), page.getPages(), page.getCurrent());
log.info("当前是第{}次开始批量更新 user_assets_details 表", page.getCurrent());
if (CollectionUtils.isNotEmpty(detailsList)) {
List<UserAssetsDetails> newList = new ArrayList<>();
for (UserAssetsDetailsCleanBO bo : detailsList) {
UserAssetsDetails vo = new UserAssetsDetails();
vo.setId(bo.getId());
vo.setBalance(0);
newList.add(vo);
if (bo.getLoncinScore() == null) {
bo.setLoncinScore(0);
}
if (bo.getGuguxScore() == null) {
bo.setGuguxScore(0);
}
if (bo.getTotalScore() == null) {
bo.setTotalScore(0);
}
}
userAssetsDetailsService.updateBatchById(newList);
log.info("结束第{}次批量更新 user_assets_details 表", page.getCurrent());
log.info("当前是第{}次开始批量保存 user_assets_bill 表", page.getCurrent());
// 3.2 将数据存入user_assets_bill用户资产流水表中
List<UserAssetsDetailsCleanBO> distinctDetailsList = detailsList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(UserAssetsDetailsCleanBO::getUserId))), ArrayList::new)); // 根据用户id去重
if (CollectionUtils.isNotEmpty(distinctDetailsList)) {
for (UserAssetsDetailsCleanBO details : distinctDetailsList) {
Long userId = details.getUserId();
// 判断该用户是否已存入过资产流水表中
boolean containsUserId = cacheService.isContainsUserId(userId);
if (containsUserId) {
continue;
}
Integer totalScore = details.getTotalScore();
Integer guguxScore = details.getGuguxScore();
Integer loncinScore = details.getLoncinScore();
boolean b = userScoreBusinessNewService.scoreExpired(userId, totalScore, guguxScore, loncinScore);
cacheService.setUserId(userId);
if (!b) {
log.error("用户{}的积分清理失败", userId);
}
}
}
log.info("结束第{}次批量保存 user_assets_bill 表", page.getCurrent());
}
int a = 1/0;
transactionManager.commit(status); // 手动提交事务
} catch (Exception e) {
e.printStackTrace();
log.error("清理积分过程中发生异常", e);
transactionManager.rollback(status); // 手动回滚事务
throw new RuntimeException("清理积分过程中发生异常");
}
}
SELECT
circle.id AS circleId,
circle.circle_name_py,
circle.circle_name,
circle.circle_sculpture,
circle.circle_type_id,
circle.join_in_flag,
circle_user_num.user_num as countUserNum,
circle_user.status
FROM
ggx_circle circle
LEFT JOIN ggx_circle_type type ON circle.circle_type_id = type.id
left join ggx_circle_admin admin on admin.circle_id = circle.id
left join ggx_circle_user circle_user on circle_user.circle_id = circle.id
left join (SELECT circle.id as cid, count( admin.admin_id ) as num FROM ggx_circle circle LEFT JOIN ggx_circle_admin admin ON circle.id = admin.circle_id WHERE admin.STATUS = 1 GROUP BY circle.id) circle_admin on circle_admin.cid = circle.id
left join (SELECT circle_id as circle_user_id, COUNT(*) AS user_num FROM ggx_circle_user WHERE status IN (1, 3) GROUP BY circle_id) circle_user_num on circle_user_num.circle_user_id = circle.id
where
circle_admin.num < 3 and circle.delete_flag = 0 and circle.status = 1 and circle.is_recruit_admin = 1 and circle.is_open = 1 and admin.admin_id!=1 group by circle.id;
-- 上述第一个子查询：查询出 每个圈子有几个圈主
SELECT circle.id as cid, count( admin.admin_id ) as num FROM ggx_circle circle LEFT JOIN ggx_circle_admin admin ON circle.id = admin.circle_id WHERE admin.STATUS = 1 GROUP BY circle.id
-- 第二个子查询：查询出 每个圈子有多少人
SELECT circle_id as circle_user_id, COUNT(*) AS user_num FROM ggx_circle_user WHERE status IN (1, 3) GROUP BY circle_id
SELECT
circle.id AS circleId,
circle.circle_name_py,
circle.circle_name,
circle.circle_sculpture,
circle.circle_type_id,
circle.join_in_flag,
users.userNum AS countUserNum
FROM
ggx_circle circle
LEFT JOIN ggx_circle_type type ON circle.circle_type_id = type.id
LEFT JOIN ( SELECT circle_id, count(*) AS adminNum FROM ggx_circle_admin WHERE delete_flag = 0 AND `status` = 1 GROUP BY circle_id ) AS adminNums ON circle.id = adminNums.circle_id
LEFT JOIN ggx_circle_admin admin ON circle.id = admin.circle_id AND admin.admin_id = 1
LEFT JOIN ( SELECT circle_id, count(*) AS userNum FROM ggx_circle_user WHERE status IN ( 1, 3 ) GROUP BY circle_id ) AS users ON circle.id = users.circle_id
where
circle.is_recruit_admin = 1 AND ( adminNums.adminNum <3 OR adminNums.circle_id IS NULL ) AND ( admin.admin_id IS NULL OR admin.delete_flag = 1 OR admin.status != 1 ) AND circle.circle_type_id = 1
SELECT
opus.id AS opusId,
base.id AS userId,
opus.content,
opus.cover,
opus.opus_type
FROM
`gugux-opus`.opus opus
INNER JOIN `gugux-user`.user_base base ON base.id = opus.created_by
WHERE
opus.state =3
SELECT
user_id,
count(id) AS fun_score
FROM
`gugux-mall`.mall_order
WHERE
state = 50 and actual_pay > 50
GROUP BY
user_id
