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执行。

巴特西

Mysql JDBC-mysql-Driver queryTimeout分析