mysql 执行超时处理逻辑分析

出现mysql 异常：com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request

如何模拟？

1. 通过cmd 窗口 mysql -uroot -p 登录mysql
2. 查看自己事务隔离级别以及修改自动提交。

SELECT @@global.tx_isolation;
select @@autocommit;
set autocommit = 0;

3. 执行命令开启事务以及执行更新sql 不提交然后再去我们的程序中更新同一条数据，这样模拟出现执行sql 超时现象。

 start transaction;
 begin;
 update tb_user set name = 'drj' where id = 1;

分析源码

这时候执行程序中同一个sql 就会出现超时异常就是我们文章开始说的那个异常MysqlTimeoutException，通过异常我们可以定位到核心源码部分，分析源码如下(版本：mysql-connector-java–5.1.34)：

1. 这里源码主要是在符合条件下给调度任务赋值，为后续调度任务处理做基础。

protected ResultSetInternalMethods executeInternal(int maxRowsToRetrieve, Buffer sendPacket, boolean createStreamingResultSet, boolean queryIsSelectOnly, Field[] metadataFromCache, boolean isBatch) throws SQLException {
        synchronized(this.checkClosed().getConnectionMutex()) {
            ResultSetInternalMethods var10000;
            try {
                this.resetCancelledState();
                MySQLConnection locallyScopedConnection = this.connection;
                ++this.numberOfExecutions;
                if (this.doPingInstead) {
                    this.doPingInstead();
                    var10000 = this.results;
                    return var10000;
                }
								/**
                这里是处理记时间的定时任务对象
                **/
                CancelTask timeoutTask = null;

                ResultSetInternalMethods rs;
                try {
                		/**
                    这个三个条件 第一个和第三个默认都是满足的 第二个是我们配置的执行sql 超时时间 所以我们只需要将他配置大于0 。里面内容就是生成定时任务对象，配置触发时间了，通过分析locallyScopedConnection.getCancelTimer().schedule 发现就是当前时间+ timeoutInMillis 作为触发时间，然后在里面它给我们做啥操作继续看下个里面源码
                    **/
                    if (locallyScopedConnection.getEnableQueryTimeouts() && this.timeoutInMillis != 0 && locallyScopedConnection.versionMeetsMinimum(5, 0, 0)) {
                        timeoutTask = new CancelTask(this, this);
                        locallyScopedConnection.getCancelTimer().schedule(timeoutTask, (long)this.timeoutInMillis);
                    }

                    if (!isBatch) {
                        this.statementBegins();
                    }

                    rs = locallyScopedConnection.execSQL(this, (String)null, maxRowsToRetrieve, sendPacket, this.resultSetType, this.resultSetConcurrency, createStreamingResultSet, this.currentCatalog, metadataFromCache, isBatch);
                    //假如执行到这里 说明rs 返回没有阻塞的话 任务就需要及时取消掉。原因就不用多说了。
                    if (timeoutTask != null) {
                        timeoutTask.cancel();
                        locallyScopedConnection.getCancelTimer().purge();
                        if (timeoutTask.caughtWhileCancelling != null) {
                            throw timeoutTask.caughtWhileCancelling;
                        }

                        timeoutTask = null;
                    }

                    Object var11 = this.cancelTimeoutMutex;
                    synchronized(this.cancelTimeoutMutex) {
                     //这个下面任务调度源码会涉及到这个变量，根本原因就是执行sql 还没完 但是触发了kill 任务，这个时候代码就认为超时了 所以抛出MySQLTimeoutException异常
                        if (this.wasCancelled) {
                            SQLException cause = null;
                            if (this.wasCancelledByTimeout) {
                                cause = new MySQLTimeoutException();
                            } else {
                                cause = new MySQLStatementCancelledException();
                            }

                            this.resetCancelledState();
                            throw cause;
                        }
                    }
                } finally {
                    if (!isBatch) {
                        this.statementExecuting.set(false);
                    }
 										// 最后不管执行与否 取消任务释放资源
                    if (timeoutTask != null) {
                        timeoutTask.cancel();
                        locallyScopedConnection.getCancelTimer().purge();
                    }

                }

                var10000 = rs;
            } catch (NullPointerException var23) {
                this.checkClosed();
                throw var23;
            }

            return var10000;
        }
    }

2. 这个源码主要是任务调度执行的内容

class CancelTask extends TimerTask {
        long connectionId = 0L;
        String origHost = "";
        SQLException caughtWhileCancelling = null;
        StatementImpl toCancel;
        Properties origConnProps = null;
        String origConnURL = "";

        CancelTask(StatementImpl cancellee) throws SQLException {
            this.connectionId = cancellee.connectionId;
            this.origHost = StatementImpl.this.connection.getHost();
            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();
        }

        public void run() {
            Thread cancelThread = new Thread() {
                public void run() {
                    Connection cancelConn = null;
                    java.sql.Statement cancelStmt = null;

                    try {
                        /**
                        代码可以走到这里说明 任务没有被取消 取消任务代码在上面eg:
                        if (timeoutTask != null) {
                        timeoutTask.cancel();
                        locallyScopedConnection.getCancelTimer().purge();
                    }
                    所以他执行内容第一就是kill 正在执行的sql 语句比如咋们那个update 语句
                        **/
                        if (StatementImpl.this.connection.getQueryTimeoutKillsConnection()) {
                            CancelTask.this.toCancel.wasCancelled = true;
                            CancelTask.this.toCancel.wasCancelledByTimeout = true;
                            StatementImpl.this.connection.realClose(false, false, true, new MySQLStatementCancelledException(Messages.getString("Statement.ConnectionKilledDueToTimeout")));
                        } else {
                            Object var3 = StatementImpl.this.cancelTimeoutMutex;
                            synchronized(StatementImpl.this.cancelTimeoutMutex) {
                                if (CancelTask.this.origConnURL.equals(StatementImpl.this.connection.getURL())) {
                                    cancelConn = StatementImpl.this.connection.duplicate();
                                    cancelStmt = cancelConn.createStatement();
                                    cancelStmt.execute("KILL QUERY " + CancelTask.this.connectionId);
                                } else {
                                    try {
                                        cancelConn = (Connection)DriverManager.getConnection(CancelTask.this.origConnURL, CancelTask.this.origConnProps);
                                        cancelStmt = cancelConn.createStatement();
                                        cancelStmt.execute("KILL QUERY " + CancelTask.this.connectionId);
                                    } catch (NullPointerException var24) {
                                        ;
                                    }
                                }
								
                                /**
                                这是一个任务标识 为true 标识执行过任务kill 所以在外层判断会使用他。 上面代码中判断使用这个变量：Object var11 = this.cancelTimeoutMutex;
                    synchronized(this.cancelTimeoutMutex) {
                        if (this.wasCancelled) {
                            SQLException cause = null;
                            if (this.wasCancelledByTimeout) {
                                cause = new MySQLTimeoutException();
                            } else {
                                cause = new MySQLStatementCancelledException();
                            }

                            this.resetCancelledState();
                            throw cause;
                        }
                    }
                               到此我们异常就会抛出 也就明白了 他是怎么判断是否执行超时的 **/
                                CancelTask.this.toCancel.wasCancelled = true;
                                CancelTask.this.toCancel.wasCancelledByTimeout = true;
                            }
                        }
                    } catch (SQLException var26) {
                        CancelTask.this.caughtWhileCancelling = var26;
                    } catch (NullPointerException var27) {
                        ;
                    } finally {
                        if (cancelStmt != null) {
                            try {
                                cancelStmt.close();
                            } catch (SQLException var23) {
                                throw new RuntimeException(var23.toString());
                            }
                        }

                        if (cancelConn != null) {
                            try {
                                cancelConn.close();
                            } catch (SQLException var22) {
                                throw new RuntimeException(var22.toString());
                            }
                        }

                        CancelTask.this.toCancel = null;
                        CancelTask.this.origConnProps = null;
                        CancelTask.this.origConnURL = null;
                    }

                }
            };
            cancelThread.start();
        }
    }