Understanding PageHelper Issues and ThreadLocal Pitfalls in MyBatis

After re‑introducing PageHelper in a new backend project, several strange issues appeared during testing, including the ability to register an already‑existing username, dropdown lists returning only five rows, and password‑reset operations failing with a "Limit 5" SQL error.

The root cause lies in how PageHelper stores pagination parameters in a ThreadLocal object. When startPage() is called, it extracts request pagination data and invokes PageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable) . The parameters are kept in a thread‑local cache ( LOCAL_PAGE ) until the SQL execution finishes.

protected void startPage() {
    PageDomain pageDomain = TableSupport.buildPageRequest();
    Integer pageNum = pageDomain.getPageNum();
    Integer pageSize = pageDomain.getPageSize();
    if (StringUtils.isNotNull(pageNum) && StringUtils.isNotNull(pageSize)) {
        String orderBy = SqlUtil.escapeOrderBySql(pageDomain.getOrderBy());
        Boolean reasonable = pageDomain.getReasonable();
        PageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable);
    }
}

During MyBatis execution, the PageInterceptor intercepts the query and retrieves the pagination object via PageHelper.getLocalPage() . If the thread still holds a previous pagination configuration (for example, because the previous request threw an exception or the SQL was never executed), the interceptor will incorrectly append a LIMIT clause to non‑paginated statements, causing the observed anomalies.

public static Page<T> getLocalPage() {
    return LOCAL_PAGE.get();
}

protected static void setLocalPage(Page page) {
    LOCAL_PAGE.set(page);
}

public static void clearPage() {
    LOCAL_PAGE.remove();
}

The interceptor’s intercept method finally calls dialect.afterAll() , which in turn invokes clearPage() to remove the thread‑local data. However, if an exception occurs before this cleanup, the stale pagination parameters remain, and subsequent requests handled by the same thread will suffer from unexpected LIMIT injections.

finally {
    if (dialect != null) {
        dialect.afterAll();
    }
}

To avoid these problems, the article recommends calling the SQL operation immediately after startPage() , or manually invoking clearPage() before any non‑paginated method when there is a risk of leftover configuration. It also warns against clearing the page before a legitimate pagination call, as that would break the intended paging behavior.

Overall, the deep dive into PageHelper’s source code not only explains the specific bugs encountered but also provides valuable insight into MyBatis interceptor mechanics and proper ThreadLocal management for reliable backend pagination.