如何停止超时的DB查询

如何停止超时的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有没有结束

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。