如何进行频繁提交，以便我的 sql 删除查询不会超时 [英] how to make frequent commits so my sql delete query won't time out

问题描述

在我的 Spring Batch 项目中，作为个别任务的一部分，我有几个更新/删除查询超时，因为它们正在删除大量记录.由于这些只是更新和删除查询，我将它们实现为不是面向块的 Tasklet 步骤.

I have a couple of update/delete queries as part of individual tasks in my Spring Batch project that are timing out because they are deleting large sets of records. Since these are just update and delete queries I implemented them as Tasklet steps which are not chunk-oriented.

如何进行频繁提交以避免超时.

How can I make frequent commits so that I can avoid timeouts.

public class DeleteTask implements Tasklet {

    private static final String DELETE_QUERY = "DELETE FROM MYTABLE WHERE COLUMN = 'TEST'";
    private JdbcTemplate jdbcTemplate;


    public DeleteTask(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }


    @Override
    public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) {
        log.info("Purging IMCO records from MYTABLE table...");
        try {
            jdbcTemplate.update(DELETE_QUERY);
        } catch (Exception e) {
            log.info("Failed to purge  records from MYTABLE table due to:");
            log.info(e.getMessage());
        }
        log.info("Records purged");
        return RepeatStatus.FINISHED;
    }
}

异常状态:

 StatementCallback; SQL [DELETE FROM MYTABLE WHERE LOB_CD = 'TEST'[jcc][t4][2055][11259][4.14.113] The database manager is not able to accept new requests, has terminated all requests in progress, 
ERRORCODE=-4499, SQLSTATE=58009; nested exception is com.ibm.db2.jcc.am.DisconnectNonTransientException: [jcc][t4][2055][11259][4.14.113] The database manager is not able to accept new requests, has terminated all requests in progress, 

推荐答案

一个简单的 SQL 复合语句可能是最好的方法

A simple SQL compound statement would maybe be the best method

BEGIN
    DECLARE DONE BOOLEAN DEFAULT FALSE;  
    --
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN SET DONE = TRUE; END;
    --
    WHILE NOT DONE
    DO
        DELETE FROM (SELECT * FROM MY_TABLE WHERE COL1 = 'A' FETCH FIRST 20000 ROWS ONLY)
        COMMIT;
    END WHILE;
END

这篇关于如何进行频繁提交，以便我的 sql 删除查询不会超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！