Understanding PageHelper Pagination Issues and ThreadLocal Management in MyBatis

After a long period of not using PageHelper , the author encountered several unexpected behaviors when a new project adopted it for pagination, including duplicate user registration, limited query results, and password‑reset errors caused by hidden LIMIT clauses.

Typical Anomalies

Duplicate Registration

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

The method checkUserNameUnique(username) checks the database for existing usernames, but due to PageHelper's hidden pagination, the INSERT/UPDATE statements receive an unexpected LIMIT clause.

Only Five Items Returned

Even though the query should return more than ten rows, only five are returned because PageHelper automatically adds pagination parameters when a ThreadLocal page is present.

Password Reset Error

When an admin resets a password, the generated SQL throws an error: sql语句异常,update语句不认识 “Limit 5” , indicating that a stray LIMIT was appended.

How PageHelper Works

Using startPage()

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

The call to startPage() reads pagination parameters from the request and stores them in a ThreadLocal via PageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable) .

ThreadLocal Storage

PageHelper keeps the pagination configuration in a ThreadLocal<Page> LOCAL_PAGE object, which is unique per thread. When a request arrives, LOCAL_PAGE.get() retrieves any previously set pagination data.

Interceptor Logic

@Override
public Object intercept(Invocation invocation) throws Throwable {
    // ... obtain MappedStatement, parameters, RowBounds, etc.
    if (!dialect.skip(ms, parameter, rowBounds)) {
        if (dialect.beforeCount(ms, parameter, rowBounds)) {
            Long count = count(...);
            if (!dialect.afterCount(count, parameter, rowBounds)) {
                return dialect.afterPage(new ArrayList(), parameter, rowBounds);
            }
        }
        resultList = ExecutorUtil.pageQuery(dialect, executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
    } else {
        resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
    }
    return dialect.afterPage(resultList, parameter, rowBounds);
} finally {
    if (dialect != null) {
        dialect.afterAll();
    }
}

The interceptor decides whether to apply pagination based on the presence of a ThreadLocal page. If pagination is required, it performs a count query first, then executes the paged query via ExecutorUtil.pageQuery . Otherwise it falls back to the normal executor.query path.

Cleaning Up

@Override
public void afterAll() {
    AbstractHelperDialect delegate = autoDialect.getDelegate();
    if (delegate != null) {
        delegate.afterAll();
        autoDialect.clearDelegate();
    }
    clearPage();
}

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

Even when no pagination occurs, afterAll() is invoked to ensure the ThreadLocal is cleared. If an exception happens before the finally block runs, the ThreadLocal may remain polluted, causing subsequent requests on the same thread to inherit stale pagination parameters.

Practical Recommendations

Always execute the SQL statement immediately after calling startPage() to guarantee the ThreadLocal is cleared in the same request.

If you suspect a missing cleanup (e.g., due to an early exception), manually call clearPage() before the next potentially non‑paginated operation.

Avoid calling clearPage() before a legitimate pagination call, as it will remove the needed configuration.

Why Errors Appear Intermittently

Containers like Tomcat reuse threads from a pool. If a thread retains a stale PageHelper configuration, only the requests that happen to be processed by that thread will exhibit the erroneous LIMIT behavior.

Conclusion

While PageHelper simplifies pagination, improper handling of its ThreadLocal can lead to subtle bugs. Understanding the underlying interceptor, ThreadLocal lifecycle, and ensuring proper cleanup helps avoid these pitfalls and improves overall backend reliability.