Understanding MySQL database scheduling and locking problems

MySQL allows the scheduling characteristics of the statement to be affected, which enables queries from several clients to collaborate better so that a single client is not locked out for too long. Changing the scheduling feature also ensures that specific queries are processed faster. Let's take a look at MySQL's default scheduling strategy, and then see what options are available to change this policy. For the purposes of the discussion, it is assumed that the client program that performs the retrieval (SELECT) is a read program. Another client program that performs a modify table operation (Delete,insert,replace or up DATE) is a writer.

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

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

Write has a higher precedence than read.

Implement scheduling policies with the help of table locks. The client program must first obtain a lock on the table whenever it accesses the table. This work can be done directly with lock TABLES, but the lock manager for the general server automatically obtains locks when needed. The lock on the table can be freed when the client finishes processing the table. The lock obtained directly can be freed by unlock TABLES, but the server will automatically release the locks it obtains.

The client performing the write operation must have exclusive access to the table lock. In the middle of a write operation, the table is in an inconsistent state because the table is being deleted, added, or changed, and the indexes on that table may need to be updated accordingly. If the table is in constant flux, it can be problematic to allow other clients to access the table. It is obviously not good to have two clients writing the same table at the same time, because it will soon make the table unusable. Allowing a client to read a constantly changing table is also not a good thing, because it may be just being changed at the moment of reading the table, and the result is incorrect. The client performing the read operation must have a lock that prevents other clients from writing the table, ensuring that the table does not change during the read table. However, the lock does not need to provide exclusive access to the read operation. This lock also allows other clients to read the table at the same time. The read does not change the table, and there is no need to prevent other clients from reading the table.

MySQL allows the use of several query-limiting modifiers to influence its scheduling strategy. One of these is the Low_priority keyword for the delete, INSERT, LOAD DATA, REPLACE, and up DATE statements. The other is the High_priority keyword for the SELECT statement. The third is the delayed keyword for the INSERT and replace statements.

The Low_priority keyword affects scheduling as follows. Typically, if a table's write is reached when the table is being read, the writer is blocked until the reader completes, because once a query begins, it cannot be interrupted. If another read request arrives while the writer is waiting, the reader is blocked because the default scheduling policy is that the writer has a higher priority than the Read program. At the end of the first reader, the writer continues, and the second reader begins at the end of the writer's program.

If the write request is a low_priority request, the write operation is not considered to have an operation that has a higher precedence than the read operation. In this case, if the second read request arrives while the writer is waiting, the second read operation is queued before the pending write operation. The write program is allowed to execute only if there are no more read requests. The change in this schedule, in theory, means that low_priority writes may be blocked forever. When a previous read request is being processed, the new request is allowed to be queued before Low_priority writes, as long as another read request arrives.

The High_priority keyword for a SELECT query is similar. It causes the select to be inserted before the pending write operation, even if the write operation has a normal priority. The elayed modifier for the insert is as follows, when an insert delayed request arrives in the table, the server places the corresponding row in a queue and immediately returns a status to the client program so that the client program can continue to execute even if the rows have not been inserted into the table. If the reader is reading the table, the rows in the queue are suspended. When no reads are done, the server begins inserting rows in the deferred row queue. The server occasionally stops to see if a new read request arrives and waits. If so, the deferred row queue is suspended and the reader is allowed to continue. When there are no more read operations, the server starts inserting the delay row again. This process continues until the delay line queue is empty.

This schedule modifier does not appear in all versions of MySQL. The following table lists these modifiers and the MySQL version that supports these modifiers. You can use this table to determine what functionality is available in the MySQL version you are using:

The role of INSERT delayed on client side

Insert delayed is useful when other clients may execute lengthy SELECT statements, and you do not want to wait for the insert to complete. The client that publishes the insert delayed can continue execution more quickly because the server simply inserts the rows that will be inserted. However, you should be aware of the differences between the normal insert and insert delayed performance. If there is a syntax error in the insert delayed, an error is sent to the client, and if it is normal, the message is not emitted. For example, when this statement returns, the Auto_increment value obtained cannot be trusted. You cannot get a count of the number of duplicates on a unique index. This is because the insert operation returns a state before the actual insert completes. Others also indicate that if the row of the insert delayed statement is queued in a wait insert and the server crashes or is terminated (with kill-9), the rows are lost. Normal term termination is not the case, and the server inserts the rows before exiting.

　　please contact the site, timely note your name. Contact Email: edu#chinaz.com (change # to @).