Designing a High‑Concurrency Coupon Expiration System with Task Tables and Batch Processing

Why Coupon Expiration Is Not a Simple "Timed Scan" Problem

Many teams start with a naive implementation that runs a periodic SQL update to set status = 'EXPIRED' for rows whose expire_time is past. This works in development but quickly fails when the business reaches massive scale: daily coupon issuance of 30‑100 million, active coupon count in the billions, expiration times clustered at hour‑marks, and strict latency requirements from users, operations, and the core transaction path.

Thus, coupon expiration is a time‑driven batch‑processing problem involving large‑scale window scans, high‑concurrency write contention, distributed execution with idempotence and retries, fault‑tolerant handover, and isolation from the main business flow.

Defining the Problem Boundary

2.1 The Essence Is a State Transition with a Time Condition

From a business view, expiration merely changes ACTIVE to EXPIRED. From a system view it is a timed state‑migration process. A typical state diagram includes INIT → ACTIVE → LOCKED → USED → EXPIRED → INVALID, with additional states for pre‑allocation, risk‑based invalidation, and operational withdrawal.

The difficulty lies in concurrent conditions such as users redeeming while the expiration task updates the same row, new coupons being issued while the task scans the shard, batch revocations overlapping with expiration, and multiple workers needing to avoid duplicate or missed processing.

2.2 Production‑Grade Goals Must Be Explicit

Expiration visibility latency: P95 < 30 s, P99 < 2 min.

Correctness: no missed or premature expirations, no side‑effects from duplicate runs.

Business isolation: expiration must not noticeably affect coupon acquisition, redemption, or query paths.

Elasticity: peak throughput should be 3‑5× normal load.

Recovery: a node crash must allow task takeover within 5 min.

Why Direct Scans on the Coupon Table Usually Fail

3.1 Large‑Table Scans Are Inevitable Bottlenecks

Even with an index on expire_time, scanning billions of rows can cause the optimizer to choose costly plans, especially when ACTIVE rows dominate. Concentrated expiration windows amplify both scan and update load, and continuous polling consumes cache and connection resources, competing with core transaction traffic.

3.2 Coupling Scheduling Directly to the Main Table Increases Risk

Binding discovery and execution together leads to coarse granularity (one SQL handles a massive batch), invisible progress, and poor recovery because a failure forces a full re‑run.

Production Solution: Four‑Plane Task‑Table Architecture

The system is split into four logical planes:

Control Plane : defines task windows, splitting strategy, priority, retry rules, and rate limits.

Execution Plane : scans and updates coupon status, emits expiration events, and advances downstream sync.

Status Plane : records task progress, retry count, error details, and handover info.

Governance Plane : monitoring, alerting, auto‑scaling, compensation, manual intervention, and rollback.

Separating these planes enables the system to both run and be managed.

4.1 Overall Architecture

Components include a Generator, a coupon_expire_task table, a Dispatcher that steals tasks, an Executor worker cluster, sharded coupon tables, an outbox event table, and MQ/CDC for downstream sync.

4.2 Why the Task Table Is More Stable Than Direct SQL

The task table turns a vague large job into traceable, retryable, and hand‑over‑able small tasks. Compared dimensions:

Dimension          | Direct Scan               | Task‑Table
-------------------|---------------------------|--------------------------
Scheduling granularity | One SQL handles a huge batch | One task handles a clear interval
Concurrency control   | Relies on SQL behaviour      | Relies on explicit task state machine
Failure recovery      | Hard to resume from a point  | Can continue from recorded progress
Observability         | Only slow SQL visible        | Backlog, latency, success rate visible
Scaling method        | Increase SQL pressure        | Add more Worker instances
Ops intervention      | Mostly re‑run whole scan       | Retry, release, skip, manual compensation

Task Model Design

5.1 Essential Fields in the Task Table

CREATE TABLE coupon_expire_task (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    task_no VARCHAR(64) NOT NULL,
    biz_date DATE NOT NULL,
    shard_no INT NOT NULL,
    window_start DATETIME NOT NULL,
    window_end DATETIME NOT NULL,
    route_key_start BIGINT NOT NULL DEFAULT 0,
    route_key_end BIGINT NOT NULL DEFAULT 0,
    cursor_id BIGINT NOT NULL DEFAULT 0,
    task_status VARCHAR(16) NOT NULL,
    priority INT NOT NULL DEFAULT 5,
    retry_count INT NOT NULL DEFAULT 0,
    max_retry INT NOT NULL DEFAULT 8,
    owner_worker VARCHAR(64) DEFAULT NULL,
    leased_until DATETIME DEFAULT NULL,
    expected_rows INT NOT NULL DEFAULT 0,
    success_rows INT NOT NULL DEFAULT 0,
    fail_rows INT NOT NULL DEFAULT 0,
    last_error_code VARCHAR(64) DEFAULT NULL,
    last_error_msg VARCHAR(512) DEFAULT NULL,
    ext_json JSON DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_task_no (task_no),
    KEY idx_status_priority (task_status, priority, id),
    KEY idx_lease (task_status, leased_until),
    KEY idx_biz_date_shard (biz_date, shard_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Key design intents: window_start/window_end: define the time window. route_key_start/route_key_end: limit the task to a controllable key range. cursor_id: enables checkpoint‑based continuation. leased_until: implements a lease to avoid permanent lock‑up after a worker crash. expected_rows/success_rows/fail_rows: support throughput, progress, and error analysis. ext_json: stores temporary tuning parameters without schema changes.

5.2 Recommended Task State Machine

INIT        – task generated, not yet released
READY       – eligible for stealing
RUNNING     – currently executing
RETRY_WAIT  – failed, waiting for back‑off retry
SUCCESS     – completed successfully
PARTIAL_OK  – partially succeeded, needs compensation
DEAD        – exceeded max retries, requires manual handling
CANCELLED   – manually cancelled or retired by policy

This granularity distinguishes transient failures from permanent ones and drives governance actions.

Task Splitting: Risk‑Based, Not Quantity‑Based

6.1 Two‑Level Splitting

First split by time window, then by shard or routing key range. Example:

Generate a task every minute for the next two minutes of coupons.

Each database shard creates its own task set.

Hot shards are further split by primary‑key or user‑key ranges.

Advantages:

Natural isolation between windows makes latency measurement easy.

Parallelism across shards avoids lock amplification.

Failures in a hot shard do not drag down the whole batch.

6.2 Determining Granularity

Task execution time: preferably 5‑30 s.

Rows per batch: 100‑1000.

Each task should touch only one database shard.

Retry cost must be acceptable; avoid tasks that need minutes to roll back.

Execution Chain Design

7.1 Generator – Discovering Intervals Only

The generator creates immutable task descriptors without touching business data. It must be idempotent so that repeated runs do not duplicate tasks.

@Service
@RequiredArgsConstructor
public class CouponExpireTaskGenerator {
    private final CouponShardRouter couponShardRouter;
    private final CouponExpireTaskMapper taskMapper;

    @Transactional(rollbackFor = Exception.class)
    public void generate(LocalDateTime now) {
        LocalDateTime windowStart = now.minusSeconds(30);
        LocalDateTime windowEnd = now.plusMinutes(2);
        for (int shardNo : couponShardRouter.allShards()) {
            List<RouteRange> ranges = couponShardRouter.splitRanges(shardNo);
            for (RouteRange range : ranges) {
                String taskNo = buildTaskNo(windowStart, windowEnd, shardNo, range);
                if (taskMapper.existsByTaskNo(taskNo)) continue;
                CouponExpireTask task = CouponExpireTask.builder()
                    .taskNo(taskNo)
                    .bizDate(windowStart.toLocalDate())
                    .shardNo(shardNo)
                    .windowStart(windowStart)
                    .windowEnd(windowEnd)
                    .routeKeyStart(range.start())
                    .routeKeyEnd(range.end())
                    .taskStatus("READY")
                    .priority(5)
                    .maxRetry(8)
                    .build();
                taskMapper.insert(task);
            }
        }
    }

    private String buildTaskNo(LocalDateTime start, LocalDateTime end, int shardNo, RouteRange range) {
        return start.toEpochSecond(ZoneOffset.UTC) + "_" + end.toEpochSecond(ZoneOffset.UTC) + "_" +
               shardNo + "_" + range.start() + "_" + range.end();
    }
}

The key is that the task number is deterministic, making the generator safely repeatable.

7.2 Stealing Tasks – Use Lease, Not Just Status

Many systems rely solely on READY → RUNNING status, which fails when a worker crashes. A robust approach combines status with a lease:

Only READY or a RUNNING task whose lease has expired can be taken.

When a worker acquires a task it writes owner_worker and leased_until immediately.

The worker periodically renews the lease to prevent premature takeover.

SELECT id FROM coupon_expire_task
WHERE task_status = 'READY'
ORDER BY priority ASC, id ASC
LIMIT 20 FOR UPDATE SKIP LOCKED;

UPDATE coupon_expire_task
SET task_status = 'RUNNING',
    owner_worker = ?,
    leased_until = DATE_ADD(NOW(), INTERVAL 60 SECOND)
WHERE id = ?;

If SKIP LOCKED is unavailable, alternatives include explicit sharding via middleware, optimistic‑lock version columns, or Redis locks.

7.3 Executor – Progressive Convergence

The executor does not try to finish everything in one go; it processes small batches, updates status with the old‑state condition for idempotence, writes outbox events in the same transaction, and records progress.

@Service
@RequiredArgsConstructor
@Slf4j
public class CouponExpireExecutor {
    private static final int FETCH_SIZE = 200;
    private final CouponMapper couponMapper;
    private final CouponExpireTaskMapper taskMapper;
    private final OutboxEventMapper outboxEventMapper;

    public void execute(CouponExpireTask task, String workerId) {
        long cursorId = task.getCursorId();
        while (true) {
            List<CouponDO> coupons = couponMapper.selectExpiringCoupons(
                task.getShardNo(), task.getWindowEnd(), task.getRouteKeyStart(),
                task.getRouteKeyEnd(), cursorId, FETCH_SIZE);
            if (coupons.isEmpty()) {
                taskMapper.markSuccess(task.getId(), workerId);
                return;
            }
            doOneBatch(task, workerId, coupons);
            cursorId = coupons.get(coupons.size() - 1).getId();
            taskMapper.refreshLeaseAndCursor(task.getId(), workerId, cursorId, 60);
        }
    }

    @Transactional(rollbackFor = Exception.class)
    protected void doOneBatch(CouponExpireTask task, String workerId, List<CouponDO> coupons) {
        List<Long> couponIds = coupons.stream().map(CouponDO::getId).toList();
        int updated = couponMapper.batchExpire(
            task.getShardNo(), couponIds, CouponStatus.ACTIVE.name(), CouponStatus.EXPIRED.name());
        List<OutboxEventDO> events = coupons.stream()
            .map(c -> OutboxEventDO.builder()
                .aggregateType("COUPON")
                .aggregateId(String.valueOf(c.getId()))
                .eventType("COUPON_EXPIRED")
                .eventKey("coupon-expired-" + c.getId())
                .payloadJson(buildPayload(c))
                .eventStatus("NEW")
                .build())
            .toList();
        outboxEventMapper.batchInsert(events);
        taskMapper.accumulateResult(task.getId(), updated, couponIds.size() - updated, workerId);
    }

    private String buildPayload(CouponDO coupon) {
        return "{\"couponId\":" + coupon.getId() + ",\"userId\":" + coupon.getUserId() + "}";
    }
}

Design highlights:

Use cursorId for checkpoint‑based continuation.

Update only rows where status = 'ACTIVE' to keep idempotence.

State change and outbox insertion share a transaction.

Commit after each batch to avoid long‑running transactions.

Core Data‑Access Layer: SQL Tailored for Batch Scenarios

8.1 Read SQL – Minimal Scan, Stable Progress

SELECT id, user_id, coupon_template_id, expire_time
FROM coupon_023
WHERE status = 'ACTIVE'
  AND expire_time <= ?
  AND route_key BETWEEN ? AND ?
  AND id > ?
ORDER BY id ASC
LIMIT 200;

Key points: id > ? acts as a cursor, avoiding deep offset.

Ordering by id guarantees repeatable order for retries.

Range on route_key limits the scan to the shard’s responsibility.

Time filter restricts to the current window.

8.2 Update SQL – Idempotent and Lock‑Friendly

UPDATE coupon_023
SET status = 'EXPIRED',
    expired_at = NOW(),
    updated_at = NOW()
WHERE id IN ( ... )
  AND status = 'ACTIVE';

Including the old status prevents overwriting rows that have already been processed by redemption or risk‑control paths.

8.3 Recommended Index

CREATE INDEX idx_expire_scan ON coupon_xxx (status, expire_time, route_key, id);

Order matters: filter by status first, then narrow by expire_time, limit to the task’s route_key range, and finally use id for stable cursor progression.

Concurrency Consistency: Avoid Duplicate or Premature Expiration

9.1 Conflict Between Expiration and Redemption

Typical race:

User redeems at 23:59:59.

Expiration task scans at 00:00:00.

Both attempt to transition the coupon state.

Two strategies:

Strict state‑machine : LOCKED cannot be expired directly; it must first be released by the pre‑allocation flow.

Time‑first : redemption re‑checks expiration and follows the final business rule.

For transaction‑heavy systems the strict state‑machine is preferred.

9.2 Two Levels of Idempotence

Task‑level: the same task_no is never generated twice; the same task can be re‑executed safely.

Business‑level: a coupon update only proceeds from a valid previous state to the target state.

9.3 Downstream Notification Must Be Idempotent

Expiration often triggers updates to user asset services, cache invalidation, user‑facing messages, and analytics. The main transaction only writes the outbox event; downstream consumers achieve exactly‑once semantics by deduplicating on event_key.

Failure Recovery – The Real Differentiator

10.1 Worker Crash Recovery

Distributed systems inevitably see task interruptions. Recovery must handle pod eviction, JVM GC stalls, DB hiccups, and graceful shutdown timeouts. A recovery job scans for tasks whose lease has expired and moves them back to RETRY_WAIT with an incremented retry count.

UPDATE coupon_expire_task
SET task_status = 'RETRY_WAIT',
    owner_worker = NULL,
    leased_until = NULL,
    retry_count = retry_count + 1,
    last_error_code = 'LEASE_TIMEOUT',
    updated_at = NOW()
WHERE task_status = 'RUNNING'
  AND leased_until < NOW()
  AND retry_count < max_retry;

10.2 Retry Strategy – Not Unlimited

Transient DB failures: 1‑2 quick retries.

Batch update conflicts: short back‑off then retry.

Code bugs or dirty data: move to DEAD for manual handling.

10.3 Compensation for Partial Success

In a batch of 200 coupons you may see 190 succeeded, 5 already processed by another path, and 5 failing due to dirty data. Instead of rolling back the whole batch, the system records successes, logs failures, and pushes the failed rows into a compensation queue or manual review list. This is why the PARTIAL_OK state exists.

Real‑World Peak Scenario: 618 Promotion

Assume 80 million coupons issued on the promotion day, with 30 million expiring exactly at the activity end. At midnight the system faces simultaneous order traffic, redemption, risk checks, and expiration. A naïve full‑table scan would clash with the core transaction path.

Recommended approach:

Start generating minute‑level expiration tasks 10 minutes before the activity ends.

After midnight, prioritize tasks for the most recent minute rather than scanning the entire history.

Configure dedicated thread pools, DB connection limits, and SQL timeouts for expiration.

Throttle downstream notification bursts.

Provide governance switches to reduce concurrency, pause new task generation, or run only high‑priority shards.

Resource Isolation and Elastic Design

12.1 Separate Thread‑Pool and Connection‑Pool

Expiration must not share unbounded resources with online paths. Three layers of isolation are recommended:

Dedicated executor thread pool.

Dedicated DB connection pool with explicit ceiling.

Dedicated outbox/event delivery pool.

Spring Boot 3.1.8 example:

@Configuration
public class CouponExpireExecutorConfig {
    @Bean("couponExpireExecutor")
    public ThreadPoolTaskExecutor couponExpireExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(8);
        executor.setMaxPoolSize(16);
        executor.setQueueCapacity(500);
        executor.setKeepAliveSeconds(60);
        executor.setThreadNamePrefix("coupon-expire-");
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        executor.initialize();
        return executor;
    }
}

Unbounded queues hide overload; a bounded queue forces back‑pressure.

12.2 Horizontal Scaling via Stateless Workers + Stateful Task Table

Since task state lives in the DB, workers can be added or removed without data loss. New instances immediately join the stealing loop; terminated instances have their leases reclaimed by the recovery job. HPA can use metrics such as task backlog, running count, delay seconds, batch duration, and retry total.

Observability Design

13.1 Essential Metrics

Task generation rate.

Counts of tasks in each status (READY, RUNNING, RETRY_WAIT, DEAD).

Task latency P50/P95/P99.

Rows updated per batch (success/failure).

Lease timeout recoveries.

Outbox backlog size.

Actual coupon expiration delay distribution.

13.2 Actionable Alerts

Alert                     | Threshold Example                | Suggested Action
--------------------------|--------------------------------|-------------------------------
Backlog continuously rising | READY > 5000 for 5 minutes   | Scale up workers, inspect DB slow SQL
Task retry surge          | Retries in 5 min > 100        | Check lock contention, DB stability
Expiration delay exceeds  | P99 delay > 120 seconds       | Reduce batch size, raise task priority
DEAD tasks appear          | Any DEAD task                  | Manual investigation of error code
Outbox queue growing      | Persistent increase > 10 min   | Verify downstream consumer health

Common Pitfalls and Mitigations

No event propagation : Use outbox + CDC or MQ to guarantee downstream consistency.

Offset pagination for long batches : Always use primary‑key cursor.

Oversized batches : Prioritize stability before chasing raw throughput.

Ignoring state boundaries (LOCKED, USED, INVALID) : Always include previous‑state condition in updates.

No manual intervention path : Provide APIs for retry, release, cancel, and compensation.

Technology Choice Comparison

15.1 When Task‑Table + Batch Processing Fits

Second‑to‑minute convergence is acceptable.

Authoritative data remains in a relational DB.

Team prefers stable, controllable systems over ultra‑low latency.

15.2 When Real‑Time Solutions (Redis ZSet, Streams) Are Needed

Expiration latency must be sub‑second.

Expiration volume is extremely high and tightly clustered.

Team can maintain in‑memory indexes, persistence, and dual‑write consistency.

Typical evolution path: single‑DB timer → task‑table batch → scheduling middleware → event‑driven real‑time.

Pre‑Launch Checklist

State machine and valid transitions are documented.

Task generation is naturally idempotent.

Stealing logic includes lease and timeout recovery.

Executor supports checkpoint continuation.

Main state change and downstream notifications are transactionally consistent.

Dedicated thread and DB pools are configured.

Core scan SQL execution plans have been verified.

Key metrics (backlog, delay, retries, dead tasks) are instrumented.

Manual retry, release, cancel, and compensation interfaces exist.

Capacity stress tests and failure drills have been performed for peak periods.

Conclusion

The real difficulty of a coupon expiration system is not flipping a flag, but reliably converging state across billions of rows under high‑concurrency, distributed failures, and strict latency constraints. A mature design rewrites the problem from a monolithic table scan into a task‑driven state convergence pipeline, breaks execution into small, resumable batches, expands consistency boundaries to include outbox events, and front‑loads governance capabilities. For medium‑to‑large businesses, the task‑table + batch‑processing pattern offers the best cost‑performance balance: it is simple enough to implement, robust enough for peak traffic, and extensible to more advanced real‑time architectures when the need arises.