如何停止超时的DB查询

一.问题:有时我们执行的DB查询(例如一些查询的存储过程)会时间太长,当我们的代码逻辑上不想等待这么长时间时,怎么删除呢?

 

二.思考:首先想到使用"select connectionId()",然后通过"kill query id"语句来删除掉对应的DB进程.后来发现JdbcTemplate里面CallableStatement的cancel()可以关掉存储过程的调用.那么我们可以结合Timer来计时,当超过N分钟后,就执行cancel()即可.

 

三.代码:

1.定义一个task:(可以配合connectionId来做追踪,虽然逻辑上没有使用上)

public class CustomTimeTask extends TimerTask {
	private static final Logger logger = LoggerFactory.getLogger(CustomTimeTask.class);
	
	CallableStatement cs ;
	int id;
	
	public CustomTimeTask(CallableStatement cs, int id) {
		super();
		this.cs = cs;
		this.id = id;
	}

	@Override
	public void run() {
		try {
			logger.info("[Timeout]TimeTask try to cancel the long time procedure query. Connection ID is [{}]", id);
			cs.cancel();
		} catch (Exception e) {
			logger.error("[Timeout]TimeTask fail to cancel the long time procedure query. Please check the if the query has been stopped in database. "
					+ "The connection ID is [{}]", id);
		}
	}

}

 

2.执行:

logger.debug("-----------Start to call CSV store procedure------------");
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		
		Timer timer = null;
		boolean isQuery = false;
		
	......
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			cs = conn.prepareCall(foo);
			....
			
			isQuery = true;
			int queryTimeoutMins = SystemConfig.getQueryTimeout();
			if(queryTimeoutMins>0) {
				logger.debug("Execute the statemnet to call procedure  to connection ID [{}]. "
					+ "And start the timer to cancel the timeout query. Timeout setting [{} minutes]"
						,conID,queryTimeoutMins);
				CustomTimeTask customTimeTask = new CustomTimeTask(cs, conID);
				timer = new Timer();
				timer.schedule(customTimeTask, queryTimeoutMins * 60 * 1000);
			}else {
				logger.debug("Execute the statemnet to call procedure to gen CSV record to connection ID [{}]. "
						+ "But not start the timer to cancel the timeout query because timeout setting is [{} minutes]"
						,conID,queryTimeoutMins);
			}
			
			cs.execute();
			if(timer != null) {
				logger.debug("The query for connection ID [{}] is completed. "
					+ "Cancel the timer and begin to get the resultSet. ",conID);
				timer.cancel();
				timer.purge();
			}else {
				logger.debug("The query for connection ID [{}] is completed. Begin to get the resultSet.",conID);
			}
			isQuery = false;
			
			rs = cs.getResultSet();
			
			.........
			
			...........
			
			rs.close();
		}catch (DataAccessException e) {
			dbLogger.error("DB is down. Exception is [{}]", e.getMessage());
			try {
				if(isQuery && cs!=null && !cs.isClosed()) {
					logger.info("Program has begun the query. Try to cancel the procedure query for connection ID [{}]"
							, conID);
					cs.cancel();
				}
				
			} catch (MySQLStatementCancelledException e1) {
				logger.info("Successfully cancel the procedure query for connection ID [{}]"
						, conID);
				if(timer != null) {
					timer.cancel();
					timer.purge();
				}
				isQuery = false;
			} catch (Exception e1) {
				logger.error("-- Error occur when try to cancel the query for connection ID[{}]. "
						+ "Exception is [{}].",conID, e.getMessage());
			}
			throw e;
			
		} catch (MySQLStatementCancelledException e) {
			logger.info("Successfully cancel the procedure query for connection ID [{}]"
					, conID);
			if(timer != null) {
				timer.cancel();
				timer.purge();
			}
			isQuery = false;
			return null;
		}catch (Exception e) {
			logger.error("-- Error occur, exception is [{}]", e.getMessage());
			logger.error("DB: error:", e);
			try {
				if(isQuery && cs!=null && !cs.isClosed()) {
					logger.info("Program has begun the query. Try to cancel the procedure query for connection ID [{}]"
							, conID);
					cs.cancel();
				}
				
			} catch (MySQLStatementCancelledException e1) {
				logger.info("Successfully cancel the procedure query for connection ID [{}]"
						, conID);
				if(timer != null) {
					timer.cancel();
					timer.purge();
				}
				isQuery = false;
			} catch (Exception e1) {
				logger.error("-- Error occur when try to cancel the query for connection ID[{}]. "
						+ "Exception is [{}].",conID, e.getMessage());
			}
			
			return null;
		}finally {
			try {
				if (rs != null)
					rs.close();
				if (cs != null)
					cs.close();
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				logger.error("Error occurs when close the connection to database. Error:", e);
				return null;
			}
			
		}
		logger.debug("-----------End to call store procedure------------");
		return resultMap;
	}

 

有几个地方值得注意,省略号的地方就正常cs执行存储过程的处理.但是对于timer要做判断:

1.如果查询结束了,那就没必要等待schedule了,要把timer取消掉

2.如果DB错误,判断是否在查询并且确定cs是否close,否则要调用cs.cancel()来结束

3.同时,注意cs.cancel()后,timer有没有结束