取消超時執行的sql

ckshop發表於2018-01-14
SQL

線上上 有些sql執行的比較慢,希望可以指定超時時間, 取消這個sql的執行,以下以druid為例為實現這個功能

jdbc 有2個超時時間, 一個是queryTimeout, 一個是socketTimeout,
queryTimeout的作用是sql執行超時之後,可以取消這次的執行,底層原理是傳送kill $id通知mysql來中斷這個sql的執行
socketTimeout的作用主要是為了解決tcp連線超時的問題, 超時之後,這個tcp連線會斷開

要注意的是,socket超時只是釋放tcp連結, 但sql還是在mysql裡面執行(可以通過show processlist來看到)

所以為了取消sql的執行,socketTimeout的時間一定要大於queryTimeout才有意義


複製程式碼

queryTimeout會丟擲異常com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request

socketTimeout會丟擲異常com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 2,020 milliseconds ago. The last packet sent successfully to the server was 2,010 milliseconds ago.

可以看具體示例

public class JdbcTimeout {

    DruidDataSource dataSource = new DruidDataSource();

    @Before
    public void setUp() {
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/case?useUnicode=true&characterEncoding=utf-8&socketTimeout=20000000");
        dataSource.setQueryTimeout(5);
        System.out.println("started");
    }
    @Test
    public void testQuery() throws Exception {
        try(Connection conn = dataSource.getConnection() ) {
            try(Statement stmt = conn.createStatement()) {
                String sql = "SELECT sleep(30*1000), 'name' ";
                try(ResultSet rs = stmt.executeQuery(sql)) {
                    while (rs.next()) {
                        String name = rs.getString("name");
                        System.out.println(name);
                    }
                }

            }

        }

    }
    @Test
    public void testUpdate() throws Exception {
        String sql = "UPDATE person  SET age=3  WHERE id=1 AND SLEEP(30*1000)";
        try(Connection conn = dataSource.getConnection() ) {
            try(Statement stmt = conn.createStatement()) {
                int updated = stmt.executeUpdate(sql);
                System.out.println(updated);
            }

        }
    }
}
複製程式碼

相關文章