[mysql driver]query timeout实现 mysqldriverquery timeout

之前一直对jdbc的query timeout有疑惑，看了下mysql的实现，大概了解了。

关于jdbc的各种超时时间关系见这个文章http://www.importnew.com/2466.html，这里简单来看一下mysql的query timeout实现，也是比较简单。query timeout就是在发起请求前，启动一个定时任务，触发之后抛出异常。

核心代码再执行sql过程中

protected synchronized ResultSetInternalMethods executeInternal(int maxRowsToRetrieve,
			Buffer sendPacket, boolean createStreamingResultSet,
			boolean queryIsSelectOnly, Field[] metadataFromCache,
			boolean isBatch)
			throws SQLException {
		try {
			//恢复超时状态
			resetCancelledState();
			//物理连接
			MySQLConnection locallyScopedConnection = this.connection;
			
			this.numberOfExecutions++;
	
			if (this.doPingInstead) {
				doPingInstead();
				
				return this.results;
			}
			
			ResultSetInternalMethods rs;
			
			CancelTask timeoutTask = null;
	
			try {
				//如果设置了query timeout，则启动一个timer任务
				if (locallyScopedConnection.getEnableQueryTimeouts() &&
						this.timeoutInMillis != 0
						&& locallyScopedConnection.versionMeetsMinimum(5, 0, 0)) {
					timeoutTask = new CancelTask(this);
					locallyScopedConnection.getCancelTimer().schedule(timeoutTask, 
							this.timeoutInMillis);
				}
				//这里执行sql，因为mysql是bio的，所以正常的话线程会等待server端返回，要么就socket read timeout抛出异常
				rs = locallyScopedConnection.execSQL(this, null, maxRowsToRetrieve, sendPacket,
					this.resultSetType, this.resultSetConcurrency,
					createStreamingResultSet, this.currentCatalog,
					metadataFromCache, isBatch);
				//如果需要超时，即使服务端正常返回了，还是会抛出超时异常
				if (timeoutTask != null) {
					//先把timer任务停了，大部分情况下超时任务还没到点触发
					timeoutTask.cancel();
					
					locallyScopedConnection.getCancelTimer().purge();
					//如果在进行超时操作的时候抛出了异常，则直接抛出这个异常
					if (timeoutTask.caughtWhileCancelling != null) {
						throw timeoutTask.caughtWhileCancelling;
					}
					
					timeoutTask = null;
				}
				synchronized (this.cancelTimeoutMutex) {
					if (this.wasCancelled) {
						SQLException cause = null;
						
						//如果被超时处理了，则抛出MySQLTimeoutException
						if (this.wasCancelledByTimeout) {
							cause = new MySQLTimeoutException();
						} 
						//如果是被cancel了，就抛出cancel异常						
						else {
							cause = new MySQLStatementCancelledException();
						}
						//完了之后，重新恢复状态，等待下一次sql请求
						resetCancelledState();
						//抛出异常
						throw cause;
					}
				}
			} finally {
				//最后的时候，取消超时任务，大部分场景都是正常访问，timer没到点就返回了
				if (timeoutTask != null) {
					timeoutTask.cancel();
					locallyScopedConnection.getCancelTimer().purge();
				}
			}
			
			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;
		}
	}

 具体的超时实现CancelTask

public void run() {
					//超时的时候，杀连接
					if (connection.getQueryTimeoutKillsConnection()) {
						try {
							toCancel.wasCancelled = true;
							toCancel.wasCancelledByTimeout = true;
							connection.realClose(false, false, true, 
									new MySQLStatementCancelledException(Messages.getString("Statement.ConnectionKilledDueToTimeout")));
						} catch (NullPointerException npe) {
							// not worth guarding against
						} catch (SQLException sqlEx) {
							caughtWhileCancelling = sqlEx;
						}
					} else {
						//正常的关闭当前sql请求
						Connection cancelConn = null;
						java.sql.Statement cancelStmt = null;
	
						try {
							synchronized (cancelTimeoutMutex) {
								//复制一个连接，这里会重新创建新的连接，如果此时db down了，则会connect timeout。然后发送kill指令，如果db down了，发送不成功，抛出异常，此时cancel失败
								if (origConnURL == connection.getURL()) {
									//All's fine
									cancelConn = connection.duplicate();
									cancelStmt = cancelConn.createStatement();
									cancelStmt.execute("KILL QUERY " + connectionId);
								} else {
									try {
										cancelConn = (Connection) DriverManager.getConnection(origConnURL, origConnProps);
										cancelStmt = cancelConn.createStatement();
										cancelStmt.execute("KILL QUERY " + connectionId);
									} catch (NullPointerException npe){
										//Log this? "Failed to connect to " + origConnURL + " and KILL query"
									}
								}
								//设置状态，方便主线程进行超时判断
								toCancel.wasCancelled = true;
								toCancel.wasCancelledByTimeout = true;
							}
						}
						//如果超时任务抛出异常，比如db挂了，则给主线程一个异常						
						 catch (SQLException sqlEx) {
							caughtWhileCancelling = sqlEx;
						} catch (NullPointerException npe) {
							// Case when connection closed while starting to cancel
							// We can't easily synchronize this, because then one thread
							// can't cancel() a running query
	
							// ignore, we shouldn't re-throw this, because the connection's
							// already closed, so the statement has been timed out.
						} finally {
							if (cancelStmt != null) {
								try {
									cancelStmt.close();
								} catch (SQLException sqlEx) {
									throw new RuntimeException(sqlEx.toString());
								}
							}
	
							if (cancelConn != null) {
								try {
									cancelConn.close();
								} catch (SQLException sqlEx) {
									throw new RuntimeException(sqlEx.toString());
								}
							}
							
							toCancel = null;
							origConnProps = null;
							origConnURL = null;
						}
					}
				}

从上可知，如果发送sql请求之后db挂了，query timeout触发时还没恢复，则query timeout不成功，此时主线程还会等待server端返回直到socket read timeout抛出异常，而如果socket read timeout设置比query timeout小，则query timeout始终无效，因为超时任务还没启动，主线程就抛出socket timeout异常了。