MySQL 5.7.4 introduces the ability to set server side execution time limits, specified in milliseconds, for top level read-only SELECT statements. This feature is introduced as part of WL#6936. It is based on a contribution submitted by Davi Arnaut with Bug#68252. Thank you, Davi!

The statement timeouts work by interrupting the execution of the statement when it takes longer than a specified number of milliseconds to complete. After the specified number of milliseconds has passed, the server aborts the individual query without affecting the larger transaction or connection contexts. The following error is then reported to the client when the query is aborted:

  1907: Query execution was interrupted, max_statement_time exceeded.

To be clear, the execution time limit specified is really a “soft hint”, because the query interruption may not happen precisely at the specified execution time limit. There can be a minor delay between the timer expiration and the actual query interruption.

A time limit for any SELECT statement run against a MySQL instance can be set by specifying a timeout value in milliseconds for the GLOBAL system variable max_statement_time.

For example:
SET GLOBAL MAX_STATEMENT_TIME=1000;

Then any SELECT statement run against this MySQL instance will be aborted if it takes more than 1 second to complete. The default for the GLOBAL variable is 0, which means that there is no global time limit.

An execution time limit can also be set for all SELECT statements run within a particular session by specifying the timout value in milliseconds for the SESSION system variable max_statement_time.

For example:
SET SESSION MAX_STATEMENT_TIME=2000;

Then any SELECT statements run in this particular session are aborted if they take more than 2 seconds to complete.

Finally, the maximum execution time can also be set for a specific SELECT statement using the MAX_STATEMENT_TIME clause directly in the query.

For example:
SELECT MAX_STATEMENT_TIME=1000 * FROM table;

This clause in a SELECT statement can be used to set the max execution time only for the entire statement, so it can be used only within top level SELECTs. This clause cannot be used within non top level SELECT statements, for example within subqueries or derived tables. A non-zero time limit set using the MAX_STATEMENT_TIME clause in a SELECT statement takes precedence over the system variable max_statement_time.

The max statement time limits only apply to read-only SELECTs. Here, read-only meaning:

• A SELECT statement which does not access a table at all.
  For example: SELECT 23+1;
• Or a SELECT statement that only results in the reading of data.

SELECTs which directly or indirectly result in modifications to table data are considered not read-only.

For example:
CREATE FUNCTION f() RETURNS INT
BEGIN
INSERT INTO table VALUES( now() );
RETURN 1;
END

SELECT f();

The above SELECT is not considered read-only, because it indirectly results in a table change. The read-only determination is made during query runtime, not during query parsing. So if a SELECT statement is determined to not be read-only, then any timer set for it is cancelled and the following NOTE message is reported to the user:

  Note 1908 Select is not a read only statement, disabling timer

The max statement time for SELECT statements is applicable as follows:

• It applies only to top level SELECT statements. It does not apply to non top level SELECTs, such as subqueries and derived tables. Using the MAX_STATEMENT_TIME=N clause in non top level statements will result in an error.

• It applies only to read-only SELECT statements. Any timer will be cancelled for SELECT statements after determining they are not read-only, and a note will be reported to the client.

• It does not apply to SELECT statements within stored programs. Using the MAX_STATEMENT_TIME clause in SELECT statements within a stored program will result in an error.

When a time limit is set for a SELECT statement, then a corresponding timer is started for it. This timer is cancelled if the SELECT statement completes within the time limit that was set. If it takes longer than the specified time limit, then the internal timer expires and the SELECT statement is aborted. The timer implementation differs on various platforms:

• On Linux, the timer is implemented using POSIX per process timers with SIGEV_THREAD_ID (Linux specific) as a signal event notify option.

• On Solaris, the timer is implemented using POSIX per process timers with I/O completion ports for the event notify option.

• On BSD, the timer is implemented using the “kqueue” event notification mechanism.

• On Windows, the timer is implemented using windows timers and the I/O completion ports for the event notification.

The count of timers set, timers exceeded, and timer failures is maintained with the status variables max_statement_time_set, max_statement_time_exceeded and max_statement_time_failed. Please check here for more information on these variables.

Here’s a list of the major changes made to the contribution as part of WL#6936:

• The feature was expanded to cover Windows and Solaris, in order to cover all of our supported platforms.

• The ability to set execution time limits at the USER account level was removed.
  We did not really see a good use case for it (we would like to add more fine-grained resource controls), so we decided not to include it.

• The feature is restricted to read-only SELECT statements, rather than supporting it for all DML and DDL statements.
  Some storage engines are non-transactional, so interrupting non read-only SELECT statements and other DML/DDL operations may lead to the database being in an inconsistent state. Our DDL statements are also not yet transactional, so interrupting DDL statements may also create inconsistencies. To avoid such situations, the feature is restricted to read-only SELECT statements.

• Support for setting max execution time limits was also removed in relation to stored programs.
  Stored programs can generally contain any number of DML or DDL statements in them. To avoid the situations mentioned above, support for setting max execution time for stored programs themselves, as well as SELECT statements within them, was removed. This also prevents multiple timers being set for one outer or top level statement (as the SELECT could call a stored program which contains SELECTs with the timeout clause, which contain…), which is not supported.

• We added a GLOBAL counterpart for the SESSION system variable max_statement_time.

Hope you enjoy using MySQL!