使用shedlock在计划时间清除春季批处理作业表旧记录时的死锁

Question

我遇到了这样一个具有挑战性的场景，其中的事务在执行批处理更新查询时被Microsfoft sql server锁定。

我看到了这个错误。

事务(进程ID 293)与另一个进程在锁定资源上陷入僵局，并被选择为死锁牺牲品。重新运行事务。嵌套的异常是com.microsoft.sqlserver.jdbc.SQLServerException:事务(进程ID 293)在锁定资源上与另一个进程陷入僵局，并被选择为死锁受害者。

我的批处理更新查询：

 jdbcTemplate.batchUpdate(purgeQueryArray)

我有7-8张表，如果数据超过7天就需要清除。现在，在环境较低的情况下，由于数据量很低，所以它工作得很好。在生产中，我们在每个表中有多达300到500 K的数据。在删除这许多记录时，执行spring查询的shedlock任务以死锁告终。相同操作的API运行良好，但在不同的时间执行，因此无法确定计划任务运行时的加载情况。

@Scheduled(cron = "${scheduler.expression}", zone = "GMT")
    @SchedulerLock(name = "SCHEDULER_LOCK", lockAtLeastFor = "10S", lockAtMostFor = "5M")
    public void purge() {
       // prepare array of queries purgeQueryArray
       jdbcTemplate.batchUpdate(purgeQueryArray)

    }

Shedlock表数据：

SCHEDULER_LOCK 2020-10-21 00:00:15 2020-10-21 00:00:00 tomcat 406116080-2-521278230

虽然我给了lockAtMostFor=5M，但看起来lock_until显示了15秒，这是很奇怪的。这就是原因吗？因为操作将需要1-2分钟的生产量的数据。

如有任何建议，将不胜感激。

编辑：

DELETE FROM BATCH_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID IN (SELECT BE.STEP_EXECUTION_ID FROM BATCH_STEP_EXECUTION BE 
join BATCH_STEP_EXECUTION_CONTEXT BEC on BE.STEP_EXECUTION_ID = BEC.STEP_EXECUTION_ID
where CAST(LAST_UPDATED as date) < DATEADD(day, -7, GETDATE()));

DELETE FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION
where CAST(LAST_UPDATED as date) < DATEADD(day, -7, GETDATE()));

DELETE FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM  BATCH_JOB_EXECUTION
where CAST(LAST_UPDATED as date) < DATEADD(day, -7, GETDATE()));

DELETE FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION
where CAST(LAST_UPDATED as date) < DATEADD(day, -7, GETDATE()));

DELETE FROM BATCH_JOB_EXECUTION WHERE CAST(LAST_UPDATED as date) < DATEADD(day, -7, GETDATE());

DELETE FROM BATCH_JOB_INSTANCE WHERE JOB_INSTANCE_ID NOT IN (SELECT JOB_INSTANCE_ID FROM BATCH_JOB_EXECUTION);

提前感谢

Answer 1

LAST_UPDATED是BATCH_STEP_EXECUTION公司的吗？(我喜欢在每一列上加上别名，因为否则很难读到。)

如果死锁锁定更多或锁定时间更长，则死锁将更有可能发生。优化查询是很重要的。如果这是不可能的，那么尝试使用小批处理大小来最小化事务的大小。

我要做的第一件事是将数据复制到测试环境中。我将尝试的第一个测试是将日期设置得足够远，以排除任何记录。如果速度慢，那就做扫描。一小批不会有帮助--这可能会使情况变得更糟。

CAST(LAST_UPDATED as date) < DATEADD(day，-7，GETDATE())的WHERE子句覆盖列，即使有索引，也会进行表扫描。您能直接将LAST_UPDATED与完全相同类型的局部变量进行比较吗？

也许检查一下执行计划。这可能表明出了问题。

另一种选择是，如果ID足够稳定，则首先获取它们。然后在一个单独的事务中执行delete，使用要删除的数据的表，也许还有一个临时表。我会在批中循环删除。

如果ID为标识列或以单调方式递增，则获取要保留的最旧ID。那应该很快。然后删除所有具有较小ID的内容。(请确保这是有效的逻辑。)

也许可以避开IN条款？这是同样的结果吗？

DELETE BEC
FROM BATCH_STEP_EXECUTION_CONTEXT BEC
INNER JOIN BATCH_STEP_EXECUTION BE 
ON BE.STEP_EXECUTION_ID = BEC.STEP_EXECUTION_ID
WHERE LAST_UPDATED < @LAST_UPDATED_LIMIT -- uncover the column if possible

有趣的东西。祝好运。