MAX_EXECUTION_TIME hint and SELECT statement termination granularity
0 reputation · 21 May 2020, 07:20 UTC
Query Timeout Enforcement
MySQL 5.7.7+ provides the MAX_EXECUTION_TIME(N) optimizer hint to limit the execution duration of SELECT statements in milliseconds. While this mechanism prevents runaway read queries, the enforcement occurs at specific checkpoints during the execution process.
Execution Constraints
The timeout check is typically performed between row fetches. This creates a potential discrepancy between the requested millisecond limit and the actual time the server takes to terminate a query, particularly during complex operations that do not trigger frequent row-fetch checkpoints.
Because this hint is restricted to SELECT statements, other data modification language (DML) operations require different timeout configurations to avoid long-running locks or resource exhaustion.
- How does the granularity of timeout checks vary between different storage engines when using the
MAX_EXECUTION_TIMEhint? - Under what specific execution conditions will a
SELECTstatement exceed the defined millisecond limit before the server triggers error 3024?
