Mysql jdbc的queryTimeout分析
Mysql的jdbc-driver
com.mysql.jdbc.Driver
设置queryTimeout方法
com.mysql.jdbc.StatementImpl.setQueryTimeout
StatementImpl实例有一个field:timeoutInMillis
public void setQueryTimeout(int seconds) throws SQLException {
synchronized(this.checkClosed().getConnectionMutex()) {
if(seconds < 0) {
throw SQLError.createSQLException(Messages.getString("Statement.21"), "S1009", this.getExceptionInterceptor());
} else {
this.timeoutInMillis = seconds * 1000;
}
}
}
queryTimeout使用场景示例:
com.mysql.jdbc.StatementImpl.executeQuery
ResultSet executeQuery(String sql) throws SQLException;
executeQuery有一个较复杂的逻辑:
-
获取connection的互斥锁
-
校验、初始化一些配置,是否为ping请求
-
sql转义,防sql注入
-
判断timeout是否有效,有效时创建一个CancelTask
-
将cancelTask放入Timer中延迟执行
if (locallyScopedConn.getEnableQueryTimeouts() && this.timeoutInMillis != 0 && locallyScopedConn.versionMeetsMinimum(5, 0, 0)) { timeoutTask = new CancelTask(this); //每个连接会有一个CancelTimer,一个deamon线程 locallyScopedConn.getCancelTimer().schedule(timeoutTask, this.timeoutInMillis); }
也就是在当前时间的timeoutInMillis后会执行这个Task
-
执行sql语句,获取结果
-
超时任务判断,如果有超时任务,分为两种情况:1 超时异常已经抛出,直接返回异常;1 超时任务未执行,cancel超时任务
this.results = locallyScopedConn.execSQL(this, sql, this.maxRows, (Buffer)null, this.resultSetType, this.resultSetConcurrency, this.createStreamingResultSet(), this.currentCatalog, cachedFields); if(timeoutTask != null) { if(timeoutTask.caughtWhileCancelling != null) { throw timeoutTask.caughtWhileCancelling; } timeoutTask.cancel(); locallyScopedConn.getCancelTimer().purge(); timeoutTask = null; }
-
获取lastInsertId
-
返回results
StatementImpl.CancelTask
class CancelTask extends TimerTask {
SQLException caughtWhileCancelling = null;
StatementImpl toCancel;
Properties origConnProps = null;
String origConnURL = "";
long origConnId = 0L;
CancelTask(StatementImpl cancellee) throws SQLException {
this.toCancel = cancellee;
this.origConnProps = new Properties();
Properties props = StatementImpl.this.connection.getProperties();
Enumeration keys = props.propertyNames();
while(keys.hasMoreElements()) {
String key = keys.nextElement().toString();
this.origConnProps.setProperty(key, props.getProperty(key));
}
this.origConnURL = StatementImpl.this.connection.getURL();
this.origConnId = StatementImpl.this.connection.getId();
}
public void run() {
Thread cancelThread = new Thread() {
public void run() {
Connection cancelConn = null;
java.sql.Statement cancelStmt = null;
try {
MySQLConnection npe = (MySQLConnection)StatementImpl.this.physicalConnection.get();
if(npe != null) {
if(npe.getQueryTimeoutKillsConnection()) {
CancelTask.this.toCancel.wasCancelled = true;
CancelTask.this.toCancel.wasCancelledByTimeout = true;
npe.realClose(false, false, true, new MySQLStatementCancelledException(Messages.getString("Statement.ConnectionKilledDueToTimeout")));
} else {
Object var4 = StatementImpl.this.cancelTimeoutMutex;
synchronized(StatementImpl.this.cancelTimeoutMutex) {
if(CancelTask.this.origConnURL.equals(npe.getURL())) {
cancelConn = npe.duplicate();
cancelStmt = cancelConn.createStatement();
cancelStmt.execute("KILL QUERY " + npe.getId());
} else {
try {
cancelConn = (Connection)DriverManager.getConnection(CancelTask.this.origConnURL, CancelTask.this.origConnProps);
cancelStmt = cancelConn.createStatement();
cancelStmt.execute("KILL QUERY " + CancelTask.this.origConnId);
} catch (NullPointerException var25) {
;
}
}
CancelTask.this.toCancel.wasCancelled = true;
CancelTask.this.toCancel.wasCancelledByTimeout = true;
}
}
}
} catch (SQLException var27) {
CancelTask.this.caughtWhileCancelling = var27;
} catch (NullPointerException var28) {
;
} finally {
if(cancelStmt != null) {
try {
cancelStmt.close();
} catch (SQLException var24) {
throw new RuntimeException(var24.toString());
}
}
if(cancelConn != null) {
try {
cancelConn.close();
} catch (SQLException var23) {
throw new RuntimeException(var23.toString());
}
}
CancelTask.this.toCancel = null;
CancelTask.this.origConnProps = null;
CancelTask.this.origConnURL = null;
}
}
};
cancelThread.start();
}
}
timeout后执行的操作主要为:
- cancelConn = npe.duplicate(); //复制一个当前连接配置相同的连接
- cancelStmt = cancelConn.createStatement(); //创建一个Statement对象,用来发送sql语句到数据库
- cancelStmt.execute("KILL QUERY " + npe.getId()); //杀掉已经timeout的语句
可以看到,只要CancelTask执行,除了执行sql的连接压根没有成功生成外,都会执行KILL QUERY操作,里面不做任何请求是否已成功的判断。
原因也比较明显,凡是执行到CancelTask,说明确实超时了。
connectTimeout=5000&socketTimeout=10000
其实,设置了queryTimeout也不一定生效,上述代码中无论是成功执行,还是CancelTask,都会涉及到对socket的操作,socket操作是底层的,它也有timeout选项,错误的配置或不配置,会采用操作系统的默认配置,这个时间可能是长达30分钟的。一旦网络出现问题,调用socket.read()时阻塞了,都到导致应用程序假死。
解决办法
在jdbc.url中配置参数connectTimeout和socketTimeout参数,当然他们的值应该大于计划内的程序执行sql的最长耗时时间,否则可能中断正常的sql执行。