Understanding PageHelper Pagination Issues and ThreadLocal Management in MyBatis

After re‑introducing PageHelper in a new project, the author encountered several unexpected behaviors: already‑registered usernames could be registered again, dropdown lists returned only five items despite many records, and password‑reset operations failed with SQL errors mentioning a stray LIMIT 5 clause.

Investigation revealed that the root cause lies in PageHelper's use of a ThreadLocal<Page> to store pagination parameters. Methods like startPage() set this thread‑local state, while getLocalPage() and setLocalPage(page) retrieve and modify it. If the thread‑local is not cleared after a request, subsequent requests handled by the same thread inherit stale pagination settings, causing unintended LIMIT clauses in non‑paginated SQL.

Key code snippets illustrate the process:

@GetMapping("/cms/cmsEssayList")
public TableDataInfo cmsEssayList(CmsBlog cmsBlog) {
    cmsBlog.setStatus("1");
    startPage();
    List<CmsBlog> list = cmsBlogService.selectCmsBlogList(cmsBlog);
    return getDataTable(list);
}

The startPage() method extracts pagination parameters from the request and invokes PageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable) . The interceptor PageInterceptor then decides whether to apply pagination based on the presence of a Page object retrieved via PageHelper.getLocalPage() .

When pagination is not required, the interceptor still checks the thread‑local; if a leftover Page exists, it erroneously adds a LIMIT clause, leading to the observed bugs.

PageHelper does attempt to clean up after execution:

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

The afterAll() implementation calls clearPage() , which removes the thread‑local entry via LOCAL_PAGE.remove() . However, if an exception occurs before the finally block or if developers invoke startPage() without immediately executing a query, the cleanup may be skipped, leaving the thread polluted.

Recommendations:

Always execute the SQL query immediately after calling startPage() .

Manually invoke clearPage() before any non‑paginated operation if there is a risk of leftover pagination state.

Avoid calling clearPage() before a legitimate paginated query, as it will disable pagination.

Be aware that thread pools (e.g., Tomcat, Netty) reuse threads, so stale ThreadLocal data can affect later requests.

By understanding PageHelper's internal use of ThreadLocal and ensuring proper cleanup, developers can prevent the subtle bugs described and use the plugin safely in production environments.