How LIMIT in a MySQL query allows you to cancel a thread

Question

I am wondering how the LIMIT in the query prevents the application thread from scrolling from the MySQL thread due to freezing in close interaction and why the restriction allows the query to be canceled, which otherwise does not work.

    Statement statement = connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(Integer.MIN_VALUE);

// Statement statement = connection.createStatement(); // this can be canceled

    new Thread(new Runnable() {

        // tries to cancel query after streaming starts
        @Override
        public void run() {
            try {
                Thread.sleep(5);
                statement.cancel(); // does nothing when streaming
               // statement.close(); // makes application thread hang
            } catch (SQLException | InterruptedException e) {
                e.printStackTrace();
            }
        }
    }).start();

    // adding limit to the query makes it possible to cancel stream, even if limit is not yet reached
    resultSet = statement.executeQuery("SOME_LONG_RUNNING_QUERY");

    int i = 0;
    while (resultSet.next()) {
        System.out.println(++i);
    }

    connection.close(); 

A regular (non-streaming) request can be safely canceled with or without restriction. However, in streaming mode, closing / canceling operations simply force the application thread / to do nothing, apparently when performing a read lock on a socket.

If I add a large LIMIT to a long query, then, as expected, the results cancel()will be executed with:

com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted

I understand that there are several questions on this subject, but none of them discuss the aspects below:

• Why LIMIT allows you to cancel a streaming request
• , , ?

Answer 1

Limit make - . , , .

, limit close, . LIMIT , . , , , .

, , .