Understanding PageHelper Issues and ThreadLocal in MyBatis Pagination

This article shares a practical investigation of unexpected behaviours when using the PageHelper pagination plugin in a Java backend project. The author experienced problems such as duplicate user registration, limited result sets, and password‑reset errors, and traced them back to the internal workings of PageHelper.

Typical anomalies observed:

Already‑registered usernames can be registered again.

A query that should return many rows only returns five items.

Resetting a user password triggers a SQL error mentioning an unexpected LIMIT 5 clause.

All these issues stem from PageHelper automatically appending pagination parameters (LIMIT/OFFSET) to SQL statements, even when the developer did not explicitly request pagination.

Key PageHelper components examined:

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

The method checkUserNameUnique(username) checks the database for an existing username via the following MyBatis mapper XML:

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

How PageHelper works internally

When a controller calls startPage() , PageHelper extracts pagination parameters from the HTTP request and stores them in a ThreadLocal<Page> called LOCAL_PAGE . The relevant code:

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

The stored page object is later retrieved by the MyBatis interceptor via PageHelper.getLocalPage() :

Page page = PageHelper.getLocalPage();

The interceptor ( PageInterceptor ) decides whether to apply pagination. If pagination is required, it runs a count query first and then calls ExecutorUtil.pageQuery . If not, it falls back to the normal executor.query path.

Crucially, the interceptor clears the ThreadLocal after the SQL execution in a finally block:

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

The afterAll() implementation removes the ThreadLocal entry:

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

clearPage() simply calls LOCAL_PAGE.remove(); .

Root causes identified

If startPage() is called but the subsequent SQL never executes (e.g., an early return or exception), the ThreadLocal is not cleared, causing the next request handled by the same thread to inherit stale pagination parameters.

If an exception occurs before the finally block runs, the cleanup may be skipped, again contaminating the thread.

Recommendations

Always execute the SQL immediately after startPage() to ensure the ThreadLocal is cleared promptly.

If you suspect a code path might bypass the normal flow, manually invoke clearPage() before the risky operation.

Avoid calling clearPage() on methods that do not use pagination, as it may disrupt legitimate pagination later in the same request.

By understanding the source code of PageHelper and its interaction with MyBatis, developers can avoid subtle bugs caused by thread‑local pagination state.