Why PageHelper Can Break Your MyBatis Queries: Hidden ThreadLocal Pitfalls

What weird issues did my project encounter?

During development I faced several strange problems after integrating PageHelper into a new urgent project.

Duplicate account registration?

The system allowed an already registered username to be registered again.

else if (UserConstants.NOT_UNIQUE.equals(userService.checkUserNameUnique(username))
    || "匿名用户".equals(username)) {
    // 注册用户已存在
    msg = "注册用户'" + username + "'失败";
}

The method checkUserNameUnique(username) checks the database for an existing username:

<select id="checkUserNameUnique" parameterType="String" resultType="int">
    select count(1) from sys_user where user_name = #{userName} limit 1
</select>

Dropdown list returns only five rows?

Even though the query should return dozens of rows, only five are returned because PageHelper silently adds a LIMIT 5 clause.

Changing user password throws an error?

When an admin resets a password, the SQL execution fails with the message that the UPDATE statement does not recognize "Limit 5".

This reveals that the pagination LIMIT clause is being appended to non‑paginated SQL statements.

How does PageHelper cause the above problems?

PageHelper usage

Typical controller code:

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

The startPage() method reads pagination parameters from the request and stores them in a ThreadLocal:

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);
    }
}

Parameters are:

pageNum – page number

pageSize – records per page

orderBy – sorting clause

reasonable – auto‑adjusts illegal page numbers

PageHelper stores the Page object in a ThreadLocal called LOCAL_PAGE:

protected static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal<>();

When MyBatis executes a mapper method, the PageInterceptor intercepts the call. It retrieves the current Page via PageHelper.getLocalPage() and decides whether to apply pagination, perform a count query, and finally invoke ExecutorUtil.pageQuery for the actual paging.

If the ThreadLocal still holds a page configuration after a request—e.g., because the controller called startPage() but the subsequent SQL was never executed, or an exception prevented the finally block from clearing it—subsequent non‑paginated requests on the same thread will incorrectly receive a LIMIT clause, leading to the bugs described earlier.

Clearing the ThreadLocal

At the end of intercept, PageHelper calls dialect.afterAll(), which eventually invokes clearPage() to remove the ThreadLocal entry:

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

If this cleanup does not happen (e.g., due to an uncaught exception), the stale pagination parameters persist.

Practical recommendations

Always execute the SQL immediately after startPage() so the ThreadLocal is cleared in the same request.

If you suspect a leak, manually call clearPage() before the problematic method.

Do NOT call clearPage() before a non‑paginated method, as it will break legitimate pagination.

Why does the issue appear only intermittently?

Application servers reuse threads from a pool. If a thread retains a stale page configuration, only the requests that happen to run on that thread will exhibit the bug, while others work fine.

Conclusion

PageHelper is convenient but can introduce subtle bugs when its ThreadLocal state is not properly cleared. Understanding its internal workflow—how startPage(), ThreadLocal, and the MyBatis interceptor interact—helps avoid these pitfalls and write more reliable pagination code.