Update low_priority in MySQL solves concurrency problems

In the processing of visitor information update is encountered a large concurrency problem, low_priority, low priority, can make concurrent not so much CPU, for low VPS, the role is still very large.
UPDATE [low_priority] tbl_name SET col_name1=expr1,col_name2=expr2,...

MySQL update with low_priority lets update not lock the table

MySQL allows you to change the priority of statement scheduling, which allows queries from multiple clients to work better Together so that a single client does not wait for a long time because of a lock. changing the priority also ensures that certain types of queries are processed faster. This section explains MySQL's default scheduling policy and the options that can be used to influence these policies. It also addresses the use of concurrent insert operations and the effect of storage engine lock hierarchies on client concurrency. For the sake of discussion, we refer to the client that performs the search (select) as the "reader" and the client that performs the modification (DELETE, INSERT, replace, or update) as the "writer."

(in the case where real-time is not required, writing can reduce the priority of the new insert operation and ensure the speed of the read.) 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 single data table can be performed simultaneously.

The MyISAM and memory storage engines implement such scheduling strategies with the help 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 released. It is possible to explicitly acquire or release a lock through the lock tables and unlock tables statements, but in general, the server's lock manager automatically acquires the lock when needed and releases the lock when it is no longer needed. The type of lock acquired depends on whether the client is writing or reading.

A client that writes to a table must have exclusive (exclusive) access to the lock. During the operation, the data table is in an inconsistent (inconsistent) state because the indexes on the data table may need to be updated to match each other when the data record is deleted, added, or modified. This data table can cause problems if other clients are allowed to access it during the change process. It is very obvious that it is not good to allow two clients to write to a single data table at the same time, because such operations will quickly make the information in the data table a heap of useless garbage. However, it is not good to allow the client to read the changes in the data table because the data in the location being read may be changing (modified) and the read result may not be true.

A client that performs a read operation on a table must acquire a lock that prevents other clients from writing or altering the table during the read. However, this lock does not require exclusive access. The read operation does not alter 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 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 the INSERT and replace statements.

The low_priority and high_priority modifiers affect the storage engines (such as MyISAM and memory) that use data table locks. The delayed modifier acts on the MyISAM and memory data tables.

Change the priority of statement scheduling

The Low_priority keyword affects the execution schedule of delete, INSERT, LOAD DATA, replace, and UPDATE statements. Typically, when a data table is being read, if a write operation arrives, the writer waits for the reader to complete the operation (it cannot be interrupted after the query starts, so the reader is allowed to complete the operation). If the writer is waiting, another read operation arrives, and the read is blocked (block) because the default scheduling 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 begins the operation until the writer finishes the operation.

If the write operation is a low_priority (low priority) request, then the system does not consider it to have a higher priority than the read operation. In this case, if the writer waits, the second reader arrives, allowing the second reader to be inserted before the writer. The writer is allowed to start operations only if there are no other readers. In theory, this scheduling modification implies that there may be situations where low_priority write operations are always blocked. If the previous read operation has been in progress for other read operations, then 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 insert a pending write operation, even if the write operation takes precedence over normal conditions. Another effect is that high-priority select executes before the normal SELECT statement, because these statements are blocked by write operations.

Use the--low-priority-updates option to start the server if you want all statements that support the Low_priority option to be handled by default at a low priority. By using insert high_priority to increase the INSERT statement to a normal write priority, you can eliminate the effect of this option on a single INSERT statement.

Update low_priority in MySQL solves concurrency problems