A brief analysis of MySQL basic scheduling methods

[Introduction] MySQL allows you to influence the scheduling characteristics of statements, which will allow queries from several clients to cooperate better so that a single client is not locked for too long. Changing scheduling characteristics can also ensure that certain queries are processed faster. Let's first look at MySQL's default scheduling policy, and then look at

MySQL allows you to affect the scheduling characteristics of statements, which will make queries from several clients cooperate better, so that a single client does not will be locked for too long. Changing scheduling characteristics can also ensure that certain queries are processed faster. Let's first look at MySQL's default scheduling policy, and then look at the options available to change this policy. For the purposes of this discussion, assume that the client program performing the retrieval (SELECT) is the reader program. Another client program that performs a table modification operation (DELETE, INSERT, REPLACE, or UP DATE) is the writer.

MySQL’s basic scheduling strategy can be summarized as follows:

◆Write requests should be processed in the order in which they arrive.

　◆Writing has higher priority than reading.

Implement scheduling strategy with the help of table locks. Whenever a client program wants to access a table, it must first obtain a lock on the table. This can be done directly using LOCK TABLES, but generally the server's lock manager will automatically acquire the lock when needed. The lock on the table can be released when the client finishes processing the table. Locks acquired directly can be released with UNLOCK TABLES, but the server also automatically releases locks it acquires.

The client performing the write operation must have an exclusive access lock on the table. While the write operation is in progress, because data records are being deleted, added, or changed on the table, the table is in an inconsistent state, and the indexes on the table may also need to be updated accordingly. If the table is constantly changing, allowing other clients to access the table at this time can cause problems. It is obviously not good to have two clients writing to the same table at the same time, because this will quickly make the table unavailable. It is also not a good thing to allow clients to read a changing table, because changes may be being made to the table at the moment it is read, and the results will be incorrect. The client performing the read operation must have a lock that prevents other clients from writing to the table to ensure that the table does not change during the table reading process. However, the lock does not need to provide exclusive access for read operations. This lock also allows other clients to read from the table at the same time. Reading does not change the table, so there is no need to prevent other clients from reading the table.

MySQL allows several query limit modifiers to affect its scheduling strategy. One of them is the LOW_PRIORITY keyword for DELETE, INSERT, LOAD DATA, REPLACE, and UP DATE statements. The other is the HIGH_PRIORITY keyword of the SELECT statement. The third one is the DELAYED keyword of the INSERT and REPLACE statements.

The LOW_PRIORITY keyword affects scheduling as follows. Normally, if a write to a table arrives while the table is being read, the writer is blocked until the reader completes, because once a query is started, it cannot be interrupted. If another read request arrives while the writer is waiting, the reader is also blocked because the default scheduling policy gives the writer a higher priority than the reader. At the end of the first reading program, the writing program continues, and at the end of this writing program, the second reading program begins.

　If the write request is a LOW_PRIORITY request, the write operation is not considered to have a higher priority than the read operation. In this case, if a second read request arrives while the writer is waiting, let the second read operation be queued before the waiting write operation. Writers are only allowed to execute if there are no other read requests. The theoretical implication of this change in scheduling is that LOW_PRIORITY writes may block forever. Whenever another read request arrives while a previous read request is being processed, this new request is allowed to queue before the LOW_PRIORITY write.

　The HIGH_PRIORITY keyword of the SELECT query has a similar effect. It causes the SELECT to be inserted before a pending write operation, even if the write operation has normal priority. The ELAYED modifier of INSERT works as follows. When an INSERT DELAYED request for the table arrives, the server puts the corresponding rows into a queue and immediately returns a status to the client program so that the client program can continue to execute, even if these rows It has not been inserted into the table yet. If a reader is reading from the table, the rows in the queue are pending. When there are no reads, the server begins inserting rows into the deferred row queue. From time to time the server stops to see if new read requests have arrived and waits. If so, the deferred row queue is suspended and the reader is allowed to continue. When there are no other read operations, the server starts inserting delayed rows again. This process continues until the delayed queue is empty.

This does not appear in all MySQL versions. The following table lists these modifiers and the MySQL versions that support them. You can use this table to determine what features the MySQL version used has:

　INSERT DELAYED is useful if other clients may execute lengthy SELECT statements and you do not want to wait for the insert to complete. Clients that issue INSERT DELAYED can continue execution more quickly because the server simply inserts the row to be inserted. However, you should be aware of the difference between normal INSERT and INSERT DELAYED performance. If there is a syntax error in INSERT DELAYED, an error is sent to the client. If it is normal, no message is sent. For example, the AUTO_INCREMENT value obtained when this statement returns cannot be trusted. Nor can you get a count of the number of duplicates on a unique index. This occurs because the insert operation returns a status before the actual insert is completed. Others also indicate that if rows for an INSERT DELAYED statement are queued waiting for insertion and the server crashes or is terminated (with kill -9), then these rows will be lost. Normal TERM termination does not do this, the server will insert these rows before exiting.

The above is the detailed content of A brief analysis of MySQL basic scheduling methods. For more information, please follow other related articles on the PHP Chinese website!