Low_priority of update in MySQL

Low_priority, Low priority
UPDATE [low_priority] tbl_name SET col_name1=expr1,col_name2=expr2,...

MySQL update with low_priority let update do not lock the table

MySQL allows you to change the precedence of statement scheduling, which enables queries from multiple clients to collaborate better so that a single client does not wait for a long time because of locking. Changing the priority also ensures that certain types of queries are processed faster. This section explains MySQL's default scheduling policies and the options that can be used to influence these policies. It also talks about the use of concurrent inserts and the impact of storage engine lockdown levels on client concurrency. For the sake of discussion, the client performing the search (SELECT) is referred to as "reader", and the client performing the modification operation (DELETE, INSERT, replace, or update) is referred to as "writer".

(without requiring real-time, writing can reduce the priority of the new insert operation and ensure the read speed.) Of course, there are better ways to accomplish this, master-slave, intermediate cache, all can. )

The default scheduling policy for MySQL can be summarized as follows:

· Write operations take precedence over read operations.

· Writes to a data table can occur only once at a time, and write requests are processed in the order in which they arrive.

· Multiple read operations on a data table can be performed concurrently.

The MyISAM and memory storage engines implement such a scheduling strategy with the aid of data table locks. When a client accesses a table, it must first acquire its lock. When the client completes the operation of the table, the lock is lifted. It is possible to explicitly acquire or release a lock by lock tables and unlock tables, but in general, the server's lock manager automatically acquires the lock when it is needed and releases the lock when it is no longer needed. The type of the acquired lock depends on whether the client is a write or read operation.

Clients that write to a table must have exclusive (exclusive) access to the lock. The data table is in an inconsistent (inconsistent) state when the operation is in progress, because the indexes on the datasheet may need to be updated to match each other when the data records are deleted, added, or modified. This datasheet is in the process of changing, and if other client access is allowed, there is a problem. Obviously, it is not good to allow two clients to write a single data table at the same time, because such an operation would quickly make the information in the datasheet a heap of useless rubbish. However, it is also not good to allow the client to read the changed datasheet, because the data in the location being read may be changing (modified), and the result may not be true.

A client performing a read operation on a table must acquire a lock to prevent other clients from writing or altering the table during the read process. However, this lock does not require exclusive access. The read operation does not change the data, so there is no reason for a reader to block other readers from accessing the table. So the read lock allows other clients to read the table at the same time.

MySQL provides several statement modifiers that allow you to modify its scheduling strategy:

· The Low_priority keyword is applied to delete, INSERT, LOAD DATA, replace, and update.

· The High_priority keyword is applied to the SELECT and INSERT statements.

· The delayed keyword is applied to the INSERT and replace statements.

The low_priority and high_priority regulators affect storage engines that use data table locks (for example, MyISAM and memory). The delayed regulator acts on the MyISAM and memory data tables.

Change the precedence of statement scheduling

The Low_priority keyword affects the execution schedule of the Delete, INSERT, LOAD DATA, replace, and UPDATE statements. Normally, when a data table is being read, if a write operation arrives, the writer waits for the reader to complete the operation (the query cannot be interrupted after it starts, so the reader is allowed to complete the operation). If the writer is waiting, another read operation is reached, and the read operation is blocked (block) because the default dispatch policy is that the writer takes precedence over the reader. When the first reader completes the operation, the writer begins the operation, and the second reader does not begin until the writer completes the operation.

If the write operation is a low_priority (low-priority) request, the system does not consider it to have precedence over the read operation. In this case, if the writer is waiting, and the second reader arrives, the second reader is allowed to be inserted before the writer. The writer is allowed to start the operation only when there are no other readers. In theory, this scheduling modification suggests that there may be situations where low_priority write operations can always be blocked. If the preceding read operation has been in progress with other read operations, the new request is inserted before the low_priority write operation.

The high_priority (high priority) keyword for a select query is similar. It allows a select to be inserted before a pending write operation, even if the write operation is normally higher priority. Another effect is that high-priority select executes before a normal SELECT statement because the statements are blocked by write operations.

If you want all statements that support the Low_priority option to be handled by default, use the –low-priority-updates option to start the server. You can eliminate the effect of this option on a single INSERT statement by using the Insert high_priority to increase the INSERT statement to a normal write priority. Low_priority of update in MySQL