Three Hidden Pagination Pitfalls That Can Break Your Production System

During a recent production incident the author discovered a bug that was not a code error but a misunderstanding of pagination logic. The problematic SQL was select * from table order by priority limit 1;, where priority is a numeric field with lower numbers meaning higher priority.

1. First production bug – batch processing loop

The original task was to periodically fetch orders with order_status = 0 (initial) and call an external RPC to update their status. A naïve implementation fetched all rows at once:

// get orders with status 0
//select * from order where order_status=0;
ArrayList initOrderInfoList = queryInitOrderInfoList();
for (OrderInfo orderInfo : initOrderInfoList) {
    try {
        String orderStatus = queryOrderStatus(orderInfo.getOrderId);
        updateOrderInfo(orderInfo.getOrderId, orderStatus);
    } catch (Exception e) {
        // log exception
    }
}

When the data volume grew, this approach risked OOM. The author switched to a fixed‑size batch:

while (true) {
    // select 100 rows each iteration
    //select * from order where order_status=0 order by create_time limit 100;
    ArrayList initOrderInfoList = queryInitOrderInfoList();
    for (OrderInfo orderInfo : initOrderInfoList) {
        try {
            String orderStatus = queryOrderStatus(orderInfo.getOrderId);
            updateOrderInfo(orderInfo.getOrderId, orderStatus);
        } catch (Exception e) {
            // log exception
        }
    }
}

The loop relied on while(true), so any order that never left the 0 state caused an infinite loop. Moreover, if the RPC timed out, every row stayed at status 0, leading to repeated processing of the same batch.

2. PageHelper misuse – ThreadLocal leakage

To simplify pagination the author adopted the MyBatis PageHelper plugin. The correct pattern is:

PageHelper.startPage(pageNum, 100);
List<OrderInfo> list = orderInfoMapper.select(param);

However, a later refactor introduced a conditional that could set param to null:

PageHelper.startPage(pageNum, 100);
if (param != null) {
    List<OrderInfo> list = orderInfoMapper.select(param);
}

When param was null, the pagination parameters remained in the thread’s ThreadLocal. Subsequent unrelated queries unintentionally consumed those parameters, producing unexpected paging results. The fix is to call PageHelper.startPage only immediately before the actual MyBatis query:

if (param != null) {
    PageHelper.startPage(pageNum, 100);
    List<OrderInfo> list = orderInfoMapper.select(param);
}

PageHelper clears the ThreadLocal in a finally block when used correctly, preventing leakage.

3. Duplicate data caused by identical ORDER BY values

Another bug appeared in a UI that listed orders sorted by create_time descending:

select * from table order by create_time desc limit 0,10;

Because a large batch of records had the exact same create_time (inserted via a manual SQL import), MySQL’s LIMIT‑optimization returned rows in a nondeterministic order. The same record could appear on multiple pages, as shown in the screenshot below.

The MySQL documentation explains that when ORDER BY columns contain duplicate values, the server may return rows in any order, and the combination of LIMIT and ORDER BY stops sorting after the required number of rows are found. This leads to nondeterministic pagination.

To guarantee a stable order, add a unique column (e.g., id) to the ORDER BY clause:

select * from table order by create_time desc, id asc limit 0,10;

Takeaways

Never rely on while(true) loops for batch processing without a clear exit condition.

When using pagination plugins like PageHelper, ensure the pagination call directly precedes the query to avoid ThreadLocal contamination.

Always include a deterministic tie‑breaker (such as a primary key) in ORDER BY when using LIMIT to prevent duplicate or missing rows across pages.

These three experiences illustrate how subtle pagination logic errors can cause data loss, endless loops, or inconsistent UI results in production systems.