Why Scheduled Batch Jobs Miss Data: The Offset Pagination Bug When Updating Status
A batch job using offset pagination while updating record status from PENDING to DONE silently skips data because each processed page shrinks the result set, causing OFFSET to jump over unprocessed records; the fix is to always query page 0 or use keyset pagination with stable cursors.
The Silent Data-Loss Bug in a Daily Batch Job
A scheduled task ran every night, scanned pending records, called an external API, and updated each record's status to DONE. The job always logged "sync task finished" with no errors, yet business users found thousands of records stuck in PENDING. The missing records were not fixed; they changed day to day.
Root Cause: Offset Pagination While Mutating the Query Condition
The code used a classic offset loop:
public void syncPendingTasks() {
int page = 0;
int size = 500;
while (true) {
Page<SyncTask> tasks = taskRepository.findByStatus(TaskStatus.PENDING, PageRequest.of(page, size));
if (tasks.isEmpty()) break;
for (SyncTask task : tasks) {
sync(task);
task.setStatus(TaskStatus.DONE);
taskRepository.save(task);
}
page++;
}
}Each iteration queries
WHERE status = 'PENDING' ORDER BY id LIMIT 500 OFFSET page*500. After the first 500 records are updated to DONE, they no longer satisfy the WHERE clause. The result set shrinks from 2000 to 1500 pending records. The next query uses OFFSET 500, which now skips the new first 500 pending records (originally 501–1000) and fetches 1001–1500. Those skipped records remain PENDING forever. The loop terminates when OFFSET exceeds the shrinking result set, leaving a tail of unprocessed data.
Why the Bug Is Invisible
Every SELECT and UPDATE succeeds; no exceptions, deadlocks, or timeouts.
The job prints "sync task finished" and exits cleanly.
Monitoring sees a green status; business sees missing data.
Fix 1: Always Query Page 0 (First Page)
Because processed records are removed from the PENDING set, the next batch is always the current first page. The loop becomes:
public void syncPendingTasks() {
int size = 500;
while (true) {
List<SyncTask> tasks = taskRepository.findByStatus(TaskStatus.PENDING, PageRequest.of(0, size)).getContent();
if (tasks.isEmpty()) break;
for (SyncTask task : tasks) {
syncTask(task);
}
}
}Each iteration fetches the first 500 pending records, processes them, and they disappear from the result set. The next iteration again fetches the new first 500. No OFFSET, no skipping.
Fix 2: Keyset (Cursor) Pagination for Large or Complex Queries
When the query condition is more complex or the dataset is huge, keyset pagination avoids OFFSET entirely:
long lastId = 0L;
while (true) {
List<SyncTask> tasks = taskRepository.findTop500ByStatusAndIdGreaterThanOrderById(TaskStatus.PENDING, lastId);
if (tasks.isEmpty()) break;
for (SyncTask task : tasks) {
process(task);
lastId = task.getId();
}
}SQL:
SELECT * FROM sync_task WHERE status = 'PENDING' AND id > ? ORDER BY id LIMIT 500. The cursor lastId is stable; processed records moving to DONE does not affect the position of subsequent records.
Essential: Richer Status Model and Atomic State Transitions
Two states (PENDING, DONE) are insufficient. Add PROCESSING and FAILED:
public enum TaskStatus { PENDING, PROCESSING, DONE, FAILED }Before processing, atomically claim the task in the database:
UPDATE sync_task
SET status = 'PROCESSING', worker_id = ?
WHERE id = ? AND status = 'PENDING';Check affectedRows: 1 means this worker owns the task; 0 means another instance already claimed it. This prevents duplicate processing in multi-instance deployments.
Avoid Mutable Sort Columns
If the query uses ORDER BY updated_at and the task updates updated_at, processed rows jump to the end of the sort order, breaking pagination. Use immutable cursor columns like id or created_at + id.
Use Slice or List Instead of Page for Batch Jobs
Batch jobs don't need total count or total pages; they only need the next batch. Spring Data's Slice or a simple List avoids the overhead of count queries:
List<SyncTask> tasks = taskRepository.findTop500ByStatusOrderById(TaskStatus.PENDING);Batch Tracking and Final Verification
Introduce a SyncBatch entity to group records per run. Track total, success, failed, processing, pending counts. At the end, verify no pending records remain for the batch:
long remaining = taskRepository.countByBatchIdAndStatus(batchId, TaskStatus.PENDING);
if (remaining > 0) throw new BatchIncompleteException(...);This cheap final check catches any logic gaps.
Design for Resumability
If a pod restarts mid-run, already DONE records are skipped; FAILED records can be retried or moved to a dead-letter queue; PROCESSING records without a heartbeat can be reclaimed. The state machine naturally supports resume.
Advanced: Database-Driven Work Distribution
For critical high-volume tasks, let the database hand out work: workers claim batches via atomic UPDATE, eliminating the "page" concept entirely. Workers simply ask "give me the next 500 pending tasks" and process them.
Conclusion
The bug was not a syntax error but a mental model mismatch: treating batch processing like user pagination. When processing mutates the query predicate, OFFSET becomes a moving target. The fix is often a one-line change ( page = 0), but the insight—"does my processing change the WHERE or ORDER BY?"—should guide every batch job design.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
