Scheduling and Locking Issues
The previous sections have focused primarily on making individual queries faster. MySQL also allows you to affect the scheduling priorities of statements, which may allow queries arriving from several clients to cooperate better so that individual clients aren't locked out for a long time. Changing the priorities can also ensure that particular kinds of queries are processed more quickly. We'll look first at MySQL's default scheduling policy and then see what options are available to you for influencing this policy. For the purposes of this discussion, a client performing a retrieval (a SELECT) is a reader. A client performing an operation that modifies a table (DELETE, INSERT, REPLACE, or UPDATE) is a writer.
MySQL's basic scheduling policy can be summed up like this:
Write requests should be processed in the order in which they arrive.
Writes have higher priority than reads.
The scheduling policy is implemented with the aid of table locks. Whenever a client accesses a table, a lock for it must be acquired first. It's possible to do this explicitly with LOCK TABLES, but normally the server's lock manager acquires locks as necessary automatically. When the client is finished with a table, the lock on it can be released. An explicitly acquired lock is released with UNLOCK TABLES, but here too the server automatically releases locks that it has acquired.
A client performing a write operation must have a lock for exclusive access to the table. The table is in an inconsistent state while the operation is in progress because the data record is being deleted, added, or changed, and any indexes on the table may need to be updated to match. Allowing other clients to access the table while the table is in flux causes problems. It's clearly a bad thing to allow two clients to write to the table at the same time because that would quickly corrupt the table into an unusable mess. But it's not good to allow a client to read from an in-flux table, either, because the table might be changing right at the spot being read, and the results would be inaccurate.
A client performing a read operation must have a lock to prevent other clients from writing to the table so that the table doesn't change while the table is being read. The lock need not provide exclusive access for reading, however. The lock can allow other clients to read the table at the same time. Reading doesn't change the table, so there is no reason readers should prevent each other from accessing the table.
MySQL allows you to influence its scheduling policy by means of several query modifiers. One of these is the LOW_PRIORITY keyword for DELETE, INSERT, LOAD DATA, REPLACE, and UPDATE statements. Another is the HIGH_PRIORITY keyword for SELECT statements. The third is the DELAYED keyword for INSERT and REPLACE statements.
The LOW_PRIORITY keyword affects scheduling as follows. Normally, if a write operation for a table arrives while the table is being read, the writer blocks until the reader is done because once a query has begun it will not be interrupted. If another read request arrives while the writer is waiting, the reader blocks, too, because the default scheduling policy is that writers have higher priority than readers. When the first reader finishes, the writer proceeds, and when the writer finishes, the second reader proceeds.
If the write request ks a LOW_PRIORITY request, the write is not considered to have a higher priority than reads. In this case, if a second read request arrives while the writer is waiting, the second reader is allowed to slip in ahead of the writer. Only when there are no more readers is the writer is allowed to proceed. One implication of this scheduling modification is that, theoretically, it's possible for LOW_PRIORITY writes to be blocked forever. As long as additional read requests arrive while previous ones are still in progress, the new requests will be allowed to get in ahead of the LOW_PRIORITY write.
The HIGH_PRIORITY keyword for SELECT queries is similar. It allows a SELECT to slip in ahead of a waiting write, even if the write has normal priority.
The DELAYED modifier for INSERT acts as follows. When an INSERT DELAYED request arrives for a table, the server puts the rows in a queue and returns a status to the client immediately so that the client can proceed even before the rows have been inserted. If readers are reading from the table, the rows in the queue are held. When there are no readers, the server begins inserting the rows in the delayed-row queue. Every now and then, the server pauses to see whether any new read requests have arrived and are waiting. If so, the delayed-row queue is suspended and the readers are allowed to proceed. When there are no readers left, the server begins inserting delayed rows again. This process continues until the queue is empty.
The scheduling modifiers did not appear in MySQL all at once. The following table lists these modifiers and the version of MySQL in which they appeared. You can use this to determine what capabilities your version of MySQL has.