The default Scheduling Policies of MySQL can be summarized as follows:
· Write operations take precedence over read operations.
· Write operations on a data table can only occur once at a time point, and write requests are processed in the order they arrive.
· Multiple read operations on a data table can be performed simultaneously.
MySQL allows you to change the statement scheduling priority.
MySQL provides several statement modifiers that allow you to modify its scheduling policy:
· The LOW_PRIORITY keyword is applied to DELETE, INSERT, load data, REPLACE, and UPDATE.
· The HIGH_PRIORITY keyword is applied to SELECT and INSERT statements.
· The DELAYED keyword is applied to INSERT and REPLACE statements.
LOW_PRIORITY and HIGH_PRIORITY regulators affect storage engines that use data table locks (such as MyISAM and MEMORY ). The DELAYED modifier acts on the MyISAM and MEMORY data tables.
Update/insert/load data/replace/delete [low_priority]...
Select/insert [high_priotiy]...
If the write operation is a LOW_PRIORITY (low priority) Request, the system will not consider it as having a higher priority than the read operation. In this case, if the second reader arrives while the writer is waiting, the second reader is allowed to be inserted before the writer. The writer is allowed to start operations only when there are no other readers. Theoretically, this scheduling modification implies that the write operation of LOW_PRIORITY may be blocked forever. If other read operations have been completed during the previous read operation, new requests will be inserted before the LOW_PRIORITY write operation.
The HIGH_PRIORITY (high priority) keyword of the SELECT query is similar. It allows SELECT to insert a pending write operation, even if the write operation has a higher priority under normal circumstances. Another effect is that a high-priority SELECT statement is executed before a normal SELECT statement, because these statements are blocked by the write operation.
If you want all statements that support the LOW_PRIORITY option to be processed by default at a low priority, use the -- low-priority-updates option to start the server.
Note: The above description is for the storage engine that uses table locks, such as myisam and merge.