从EJB定时器中删除大量行 [英] Delete huge number of rows from an EJB Timer

问题描述

我需要从一个EJB定时器内的表中删除数百万行。
问题是定时器的事务超时时间为90秒，所以我应该将工作分成小尺寸块。

I need to delete millions of rows from a table from within an EJB Timer. The problem is that the timer has a transaction timeout of 90 seconds, so I should divide the work into bite-size chunks.

由于我不知道在90秒内可以删除多少行，算法应该循环删除几次，直到时间几乎升高。

Since I don't know how many rows can be deleted in 90 seconds the algorithm should loop and delete a few at a time until the time is almost up.

问题是：
在JPA中可以如何限制行数被限制？
删除是在具有早于特定日期的时间戳的所有行上进行的。

The problem is: How can the number of rows to delete be limited elegantly in JPA? The delete is made on all rows having a timestamp earlier than a certain date.

我想可以找到第1000个最旧的行和 DELETE WHERE timestamp< = {1000th-oldest-row.timestamp} 然而，这不是很优雅，我必须得到1000的最后一行才能获得时间戳。

I guess it is possible to find the 1000th oldest row and DELETE WHERE timestamp <= {1000th-oldest-row.timestamp} This, however, is not very elegant and I would have to get to the last row in a 1000 to get the timestamp.

其次，如果90秒后表不干净，定时器应立即触发。这可以很容易地解决，但是再一次不是很优雅。

Secondly, the timer should trigger immediately again if the table is not clean after the 90 seconds. This can be easily solved but, again, is not very elegant.

推荐答案

您仍然会遇到交易到期问题与解决方案

You will still face transaction expiration issues with the solution you have.

诀窍是在单独的事务中执行每个块，如下所示在pesudo代码中。

The trick is to execute each chunk in a separate transaction as shown below in pesudo code.

@Entity

@NamedQueries ( value = {
    @NamedQuery (
        name = pagedDeleteExpiredItems
        query=    DELETE FROM MyTable
            WHERE (<table key>) IN (
                SELECT <table key> FROM (
                SELECT ROWNUM AS row_num, <table key> FROM MyTable
                WHERE timestamp <= :currentTime
                )
                WHERE row_num < :pageSize
            )
    )
})

public class MyEntity {
    @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
    int doPagedDeleteExpiredItems(Date currentTime, int pageSize) {
        Query query = em.createNamedQuery("pagedDeleteExpiredItems");
        query.setParameter("currentTime", currentTime);
        query.setParameter("pageSize", pageSize);
        int deleteCount = query.executeUpdate();
        return deleteCount;
    }
}


@EJBTimer
public class DeleteExpiredItemsTimer {

    @EJB(beanName = "MyEntity")
    MyEntity myEntity;

    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    void handleTimeout(Timer timer) {
        Date currentTime = getCurrentTime()
        int pageSize = 100
        int deleteCount;
        do {
            myEntity.doPagedDeleteExpiredItems(currentTime, pageSize);
        } while(deleteCount>0);
    }
}

这篇关于从EJB定时器中删除大量行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！