Why MySQL Statement Cancellation Timer Spikes in High‑Traffic Java Apps and How to Fix It

Problem Description

During a traffic peak the CPU spiked; thread dumps showed many threads named “MySQL Statement Cancellation Timer”. The issue was investigated.

Relevant components: MySQL driver, Alibaba Druid connection pool, MyBatis ORM.

Dependency Information

mysql‑jdbc 8.0.24

druid 1.2.8

mybatis 3.4.6

Environment Configuration

Druid configuration uses all default values.

MyBatis configuration (code snippet).

<code>@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
    factory.setVfs(SpringBootVFS.class);
    factory.setDataSource(dataSource);
    // todo 省略其他配置
    Configuration c = new Configuration();
    c.setLogImpl(StdOutImpl.class);
    c.setDefaultStatementTimeout(25000);
    factory.setConfiguration(c);
    return factory.getObject();
}</code>

Analysis Process

Found the thread creation point in NativeSession .

Displayed the call chain.

When

enableQueryTimeouts = true

and

timeOutInMillis != 0

, a CanalQueryTask is created and scheduled on timeout.

The method invoked is

com.mysql.cj.CancelQueryTaskImpl#run

(illustrated in diagram).

Solution

Project uses Alibaba Druid. The timeout is set via

setQueryTimeout

, which passes

timeOutInMillis

.

Set

validationQueryTimeout

to

0

to disable CancelQueryTask .

Remove MyBatis

defaultStatementTimeout

parameter.

If both are removed, MySQL server’s

wait_timeout

(default 28800 seconds) can be used to control idle connections.

<code># Show global and session timeout variables
show global VARIABLES like '%timeout%';
show VARIABLES like '%timeout%';
</code>

Druid can also test connections on borrow, return, or idle using

testOnBorrow

,

testOnReturn

, and

testWhileIdle

.

Reproduction and Fix Verification

Test classes involved: PushCallbackService.java, CallbackLog.java, DBTimerController.java, MccClient.java.

After applying the changes, the “MySQL Statement Cancellation Timer” threads no longer appear in thread dumps.

References

https://segmentfault.com/a/1190000020162800

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout