Understanding PageHelper Pagination Issues in MyBatis and How to Avoid ThreadLocal Pitfalls

The author, a senior architect, shares several strange phenomena observed in a backend project using MyBatis and PageHelper, including duplicate user registrations, limited query results, and password update errors caused by unexpected LIMIT clauses.

Root cause analysis reveals that PageHelper stores pagination parameters in a ThreadLocal variable via startPage() . If the thread’s pagination configuration isn’t cleared—either because the subsequent SQL isn’t executed or an exception occurs—the leftover LIMIT is unintentionally applied to later non‑paginated queries.

Key code snippets illustrate how startPage() retrieves request parameters, creates a Page object, and stores it in LOCAL_PAGE . The interceptor’s intercept method then checks this thread‑local page to decide whether to apply pagination, perform a count query, or execute the original SQL.

PageHelper’s cleanup occurs in the finally block via afterAll() , which ultimately calls clearPage() to remove the thread‑local entry. However, if an exception prevents reaching this block, the thread remains polluted, leading to the observed bugs.

To avoid these issues, the author recommends invoking the SQL immediately after startPage() and, when necessary, manually calling clearPage() before any non‑paginated operations. This ensures the thread‑local pagination state does not leak across requests, especially in thread‑pooled environments like Tomcat or Netty.

Overall, the article provides a deep dive into PageHelper’s inner workings, highlights pitfalls of ThreadLocal misuse, and offers concrete steps to ensure reliable pagination in Java backend services.