WL#4354: SELECT LOW_PRIORITY

Create a new selct option: LOW_PRIORITY

When this option is given to a select statement, the statement shall wait for
pending delayed inserts before it starts the normal select operation.

Pending delayed inserts are rows that have been sent to the server by INSERT
DELAYED statements, but have not yet been inserted into the table nor rejected.
They are still waiting in a queue for processing. There is one queue per table
for which delayed inserts have been sent.

SELECT LOW_PRIORITY shall wait on one queue after the other. The wait one each
queue shall end when the queue becomes empty. Non-existing or empty queues shall
be skipped.

The queues shall not be blocked. This means that at the end of the overall
waiting the queues may no longer be empty. A queue can receive new rows
immediately after the statement noticed that it became empty.

SELECT LOW_PRIORITY only guarantees that each existing queue for its select
tables was empty at one point in time during the wait. That way it guarantees
that all rows are processed that have been sent by INSERT DELAYED in the same
session before the select started. But it does also wait for rows queued by
other sessions before the select started. However it cannot guarantee to wait
for rows sent after the select started.

The waiting shall happen before opening and locking the select table(s) so that
the delayed insert thread(s) can access the table(s).

SELECT LOW_PRIORITY shall not conflict with other select options, even not
HIGH_PRIORITY. In case of SELECT LOW_PRIORITY HIGH PRIORITY, the statement would
first wait for pending delayed inserts and then queue up for the table locks
before all statements starting after that point in time. (Without HIGH_PRIORITY
later started INSERT/UPDATE/DELETE would bypass the select).

The motivation for this task is to provide an efficient way to assure that
delayed inserts are processed before other operations are done with the
table(s). Previous ways to do this were to sleep some time, optional in a loop
checking the 'Not_flushed_delayed_rows' system variable (which works server wide
however), or to flush the table(s).

An experimental patch is available, but it uses a different name for the new
select option, DELAYED instead of LOW_PRIORITY. With the above proposal, the
keyword DELAYED can later be used with SELECT for another purpose.

http://lists.mysql.com/commits/45357