建议

查询超时时间设置，是在mysql-connector-java底层是通过定时器Timer来实现statement timeout的功能，也就是说，对于设置了statement timeout的sql，将会导致mysql创建定时Timer来执行sql

因此对于这查询超时设置，只能够在正常的业务代码中进行配置（但是这个就要做到读写分离，要是写SQL配置了超时，那么server就会出现脏数据需要做幂等设计了，同时高QPS场景、分库分表、读写分离场景下不建议使用）原理部分下面讲解

 

举例说明：

  • mysql:mysql-connector-java:8.0.19
  • com.xueqiu.infra.toolbox:xueqiu-toolbox-datasource:0.0.50

1.直接使用PreparedStatement

ConnectionPool pool = ConnectionPool.newRWPool("redis_manager", infos).orElseThrow(() -> new IllegalStateException("init failed."));
 
String query = "select sleep(5)";
try (Connection connection = pool.getConnection(); PreparedStatement pstmt = connection.prepareStatement(query)) {
pstmt.setQueryTimeout(1);
System.out.println(pstmt.execute());
}

异常信息

Exception in thread "main" com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:113)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
at com.xueqiu.infra.toolbox.datasource.ConnectionPoolRWImplFunctionTest.main(ConnectionPoolRWImplFunctionTest.java:31)

2.使用JDBCTemplate 

ConnectionPool pool = ConnectionPool.newRWPool("redis_manager", infos).orElseThrow(() -> new IllegalStateException("init failed."));
//这个SpringJDBCTemplate是业务中看到大多数项目都是这么自己封装后使用的
SimpleSpringJDBCTemplate springJDBCTemplate = new SimpleSpringJDBCTemplate(pool);
JdbcTemplate jdbcTemplate = springJDBCTemplate.getJdbcTemplate();
jdbcTemplate.setQueryTimeout(1);
String query = "select sleep(5)";
jdbcTemplate.execute(query);

异常信息

Exception in thread "main" org.springframework.dao.QueryTimeoutException: StatementCallback; SQL [select sleep(5)]; Statement cancelled due to timeout or client request; nested exception is com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:120)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1397)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:387)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:414)
at com.xueqiu.snowflake.usercenter.extend.service.impl.JDBCTemplateTest.main(JDBCTemplateTest.java:26)
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:862)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2958)
at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2473)
at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2956)
at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.execute(StatementProxyImpl.java:147)
at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:619)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:405)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:375)
... 2 more

 

原理部分

尽管statement timeout很灵活，但是在高并发的情况下，会创建大量的线程，一些场景下并不建议使用。
原因在于，mysql-connector-java底层是通过定时器Timer来实现statement timeout的功能，也就是说，对于设置了statement timeout的sql，将会导致mysql创建定时Timer来执行sql，意味着高并发的情况下，mysql驱动可能会创建大量线程。
以下是模拟设置statement timeout之后，通过jstack命令查看的结果。

"MySQL Statement Cancellation Timer" #19 daemon prio=5 os_prio=31 tid=0x00007ffdef092000 nid=0xa703 in Object.wait() [0x00007000029c7000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
- waiting on <0x000000076ae72f08> (a java.util.TaskQueue)
at java.lang.Object.wait(Object.java:502)
at java.util.TimerThread.mainLoop(Timer.java:526)
- locked <0x000000076ae72f08> (a java.util.TaskQueue)
at java.util.TimerThread.run(Timer.java:505)

可以看到这里包含了一个名为Mysql Statement Cancellation Timer的线程，这就是用于控制sql执行超时的定时器线程。
在高并发的情况下，大量的sql同时执行，如果设置了statement timeout，就会出现需要这样的线程。
在mysql-connector-java驱动的源码中，体现了这个逻辑。
在查询StatementImpl中定义了一个超时Timer

com.mysql.cj.jdbc.ClientPreparedStatement#executeInternal

protected <M extends Message> ResultSetInternalMethods executeInternal(int maxRowsToRetrieve, M sendPacket, boolean createStreamingResultSet,
boolean queryIsSelectOnly, ColumnDefinition metadata, boolean isBatch) throws SQLException {
synchronized (checkClosed().getConnectionMutex()) {
try {
 
JdbcConnection locallyScopedConnection = this.connection;
 
((PreparedQuery<?>) this.query).getQueryBindings()
.setNumberOfExecutions(((PreparedQuery<?>) this.query).getQueryBindings().getNumberOfExecutions() + 1);
 
ResultSetInternalMethods rs;
 
CancelQueryTask timeoutTask = null;
 
try {
timeoutTask = startQueryTimer(this, getTimeoutInMillis());
 
if (!isBatch) {
statementBegins();
}
 
rs = ((NativeSession) locallyScopedConnection.getSession()).execSQL(this, null, maxRowsToRetrieve, (NativePacketPayload) sendPacket,
createStreamingResultSet, getResultSetFactory(), metadata, isBatch);
 
if (timeoutTask != null) {
stopQueryTimer(timeoutTask, true, true);
timeoutTask = null;
}
 
} finally {
if (!isBatch) {
this.query.getStatementExecuting().set(false);
}
 
stopQueryTimer(timeoutTask, false, false);
}
 
return rs;
} catch (NullPointerException npe) {
checkClosed(); // we can't synchronize ourselves against async connection-close due to deadlock issues, so this is the next best thing for
// this particular corner case.
 
throw npe;
}
}
}

com.mysql.cj.AbstractQuery#startQueryTimer

public CancelQueryTask startQueryTimer(Query stmtToCancel, int timeout) {
if (this.session.getPropertySet().getBooleanProperty(PropertyKey.enableQueryTimeouts).getValue() && timeout != 0) {
CancelQueryTaskImpl timeoutTask = new CancelQueryTaskImpl(stmtToCancel);
this.session.getCancelTimer().schedule(timeoutTask, timeout);
return timeoutTask;
}
return null;
}
 
 
public void stopQueryTimer(CancelQueryTask timeoutTask, boolean rethrowCancelReason, boolean checkCancelTimeout) {
if (timeoutTask != null) {
timeoutTask.cancel();
 
if (rethrowCancelReason && timeoutTask.getCaughtWhileCancelling() != null) {
Throwable t = timeoutTask.getCaughtWhileCancelling();
throw ExceptionFactory.createException(t.getMessage(), t);
}
 
this.session.getCancelTimer().purge();
 
if (checkCancelTimeout) {
checkCancelTimeout();
}
}
}

com.mysql.cj.NativeSession#getCancelTimer

public synchronized Timer getCancelTimer() {
if (this.cancelTimer == null) {
this.cancelTimer = new Timer("MySQL Statement Cancellation Timer", Boolean.TRUE);
}
return this.cancelTimer;
}

这里我们看到ClientPreparedStatement内部，提供了一个名为MySQL Statement Cancellation Timer的定时器。
在sql执行时，如果设置了statement timeout，则将sql包装成一个task，通过Timer进行执行：mysql 驱动源码里有多处使用到了这个Timer，这里以ClientPreparedStatement的executeQuery方法为例进行讲解，包含了以下代码片段：
可以看到，在指定statement timeout的情况下，mysql内部会将sql执行操作包装成一个CancelTask，然后通过定时器Timer来运行。

Timer实际上是与StatementImpl绑定的，同一个StatementImpl执行的多个sql，会共用这个Timer。
默认情况下，这个Timer是不会创建的，一旦某个StatementImpl上执行的一个sql，指定了statement timeout，此时这个Timer才创建，一直到这个StatementImpl被销毁时，Timer才会取消。

在一些场景下，如分库分表、读写分离，如果使用的数据库中间件是基于smart-client方式实现的，会与很多库建立连接，由于其底层最终也是通过mysql-connector-java创建连接，这种场景下，如果指定了statement timeout，那么应用中将会存在大量的Timer线程，在这种场景下，并不建议设置。

扩展部分

spring事务的超时机制，实际上是还是通过Statement.setQueryTimeout进行设置，每次都是把当前事务的剩余时间，设置到下一个要执行的sql中。
事实上，spring的事务超时机制，需要ORM框架进行支持，例如mybatis-spring提供了一个SpringManagedTransaction，里面有一个getTimeout方法，就是通过从spring中获取事务的剩余时间。

@Transactional(timeout = 3)
 如果同时配置了，@Transactional注解上的配置，将会覆盖默认的配置。

 transaction timeout的实现原理可以用以下流程进行描述，假设事务超时为5秒，需要执行3个sql：

  start transaction  #事务超时为5秒
    |
   \|/
   sql1  #statement timeout设置为5秒
    |
    |    #执行耗时1s，那么整个事务超时还剩4秒 
   \|/
   sql2  #设置statement timeout设置为4秒
    |
    |    #执行耗时2秒，整个事务超时还是2秒
   \|/ 
   sql3  #设置statement timeout设置为2秒
    |
   ---   #假设执行耗时超过2s，那么整个事务超时，抛出异常

通常是因为连接池大小设置的不合理。如何设置合理的线程池大小需要进行综合考虑。

这里以sql执行耗时、要支撑的qps为例：
假设某个接口的sql执行耗时为5ms，要支撑的最大qps为1000。一个sql执行5ms，理想情况下，一个Connection一秒可以执行200个sql。
又因为支持的qps为1000，那么理论上我们只需要5个连接即可。
当然，实际情况远远比这复杂，例如，我们没有考虑连接池内部的逻辑处理耗时，mysql负载较高执行sql变慢，应用发生了gc等，这些情况都会导致获取连接时间变长。
建议是，比理论值，高3-5倍。

1 应用启动时，出现获取连接超时异常
        可以通过调大initPoolSize。如果连接池有延迟初始化(lazy init)功能，也要设置为立即初始化，否则，只有第一次请求访问数据库时，才会初始化连接池。这个时候容易出现获取链接超时。
2 业务高峰期，出现获取连接超时异常
        如果是偶然出现，可以忽略。如果出现的较为频繁，可以考虑调大maxPoolSize和minPoolSize。

 

 

参考链接：

https://stackoverflow.com/questions/20889062/jdbctemplate-setquerytimeout-specific-for-each-query-i-e-query-level

https://www.mysql.com/cn/products/connector/