建议
查询超时时间设置，是在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。
参考链接：
