Designing Automatic Order Closure: Comparing DB Polling, Redis Expiration, Redis Zset Delay Queue, and Message Queue Delayed Messages

Background

Many e‑commerce systems need to automatically close orders that remain unpaid after a certain period (e.g., 30 minutes) so that inventory can be released and users notified. This article consolidates several common solutions and evaluates their suitability.

1. Database Polling

Typical approach: when an order is created, store expire_time = now() + 30min together with the order state and index the columns. A scheduler (e.g., XXL‑Job) runs every few seconds, queries orders whose state = '未付款' and now() >= expire_time , locks the IDs, and updates their status to “timeout”.

select id from order where state = '未付款' and now() >= expire_time limit 500

Pros

Simple to implement, low cost, strong reliability, and easy to maintain; widely used.

Cons

Precision is limited by the polling interval; a small interval can cause high DB load on large tables, while a large interval introduces delay in closing orders.

2. Redis Expiration Listener (Not Recommended)

Enabling notify-keyspace-events Ex in redis.conf allows a KeyExpirationEventMessageListener to receive expiration events. However, expiration notifications are not immediate (they depend on lazy or periodic deletion) and can be lost if the consumer restarts, making the solution unreliable for critical order closure.

notify-keyspace-events Ex

public class Test extends KeyExpirationEventMessageListener {
    public Test(RedisMessageListenerContainer listenerContainer) {
        super(listenerContainer);
    }

    @Override
    public void onMessage(Message message, byte[] bytes) {
        String orderId = message.toString();
    }
}

Cons

Key expiration notifications are delayed (lazy vs. periodic deletion) and not guaranteed to be delivered, leading to potential missed closures.

3. Redis Zset Delay Queue

Redis sorted sets store members (order IDs) with scores representing the expected expiration timestamp. New orders are added with score = expire_time . A background worker polls every second, uses rangeByScore(now(), +inf) to fetch overdue orders, processes them, and removes the entries.

Pros

Simple to implement and maintain.

In‑memory add/remove/query operations are fast when the number of unpaid orders is small.

Supports multiple expiration times in a single structure.

Cons

No built‑in retry mechanism; when multiple workers compete for tasks, an extra “processing” set is required to track in‑flight jobs and roll back on failure.

4. Message Queue – Delayed Messages

Delayed messages are stored in the MQ until a specified delay expires, then they are consumed. RocketMQ, for example, uses fixed delay levels (e.g., 1s, 5s, 10s, …) in version 4.x and a time‑wheel in version 5.x to support arbitrary delays.

Implementation sketch:

// org.apache.rocketmq.store.CommitLog.putMessage()
if (msg.getDelayTimeLevel() > 0) {
    // adjust to max level if needed
    if (msg.getDelayTimeLevel() > this.defaultMessageStore.getScheduleMessageService().getMaxDelayLevel()) {
        msg.setDelayTimeLevel(this.defaultMessageStore.getScheduleMessageService().getMaxDelayLevel());
    }
    topic = ScheduleMessageService.SCHEDULE_TOPIC;
    queueId = ScheduleMessageService.delayLevel2QueueId(msg.getDelayTimeLevel());
    MessageAccessor.putProperty(msg, MessageConst.PROPERTY_REAL_TOPIC, msg.getTopic());
    MessageAccessor.putProperty(msg, MessageConst.PROPERTY_REAL_QUEUE_ID, String.valueOf(msg.getQueueId()));
    msg.setPropertiesString(MessageDecoder.messageProperties2String(msg.getProperties()));
    msg.setTopic(topic);
    msg.setQueueId(queueId);
}

Each delay level creates a timer task that periodically checks the corresponding queue and forwards ready messages to the real topic.

public void start() {
    for (Map.Entry
entry : this.delayLevelTable.entrySet()) {
        Integer level = entry.getKey();
        Long timeDelay = entry.getValue();
        Long offset = this.offsetTable.getOrDefault(level, 0L);
        if (timeDelay != null) {
            this.timer.schedule(new DeliverDelayedMessageTimerTask(level, offset), FIRST_DELAY_TIME);
        }
    }
}

Pros

Offloads polling pressure to the MQ, reducing database load and simplifying business logic.

Cons

High message volume: most orders will be paid or cancelled, so delayed messages become wasteful.

Fixed delay levels limit flexibility (e.g., RabbitMQ max 49 days, RocketMQ 4.x only 18 levels, 5.x up to 3 days by default).

Additional reliability concerns: message loss, send failures, and added operational complexity.

Conclusion

For small or early‑stage projects, simple database or Redis polling is usually sufficient and more cost‑effective. Introducing heavyweight middleware such as a message queue should be justified by clear reliability or scalability needs, and a fallback polling mechanism should be retained for exceptional cases.