Inserting Data - Learning MySQL and MariaDB - page 131

Lowering the priority of an insert
For an example of LOW_PRIORITY , supposethat we've just received a file from a large
bird-watcher group with thousands of rows of data related to bird sightings. The table is a
MySQL dump file, a simple text file containing the necessary SQL statements to insert the
data into a table in MySQL. We open the dump file with a text editor and see that it con-
tains one huge INSERT statement that will insert all of the bird sightings (i.e.,
bird_sightings ) with one SQL statement into a table on our server. We haven't cre-
ated a table like this yet, but you can imagine what it might contain.
When the INSERT statement in the dump file from the large bird-watcher group is run, it
might tie up our server for quite a while. If there are users who are in the middle of re-
trieving data from the bird_sightings table, we might prefer that those processes
finish before starting our huge INSERT statement. The LOW_PRIORITY option instructs
MySQL to enter the rows when it's finished with whatever else it's doing. Here's an ab-
breviated version of how we would do that:
INSERT LOW_PRIORITY INTO bird_sightings
…
Of course, a real INSERT will have all the column and value listings you want where I
left the ellipsis (three dots).
The LOW_PRIORITY flag puts the INSERT statement in a queue, waiting for all of the
current and pending requests to be completed before it's performed. If new requests are
made while a low priority statement is waiting, they are put ahead of it in the queue.
MySQL does not begin to execute a low priority statement until there are no other re-
quests waiting.
The table is locked and any other requests for data from the table that come in after the
INSERT statement starts must wait until it's completed. MySQL locks the table once a
low priority statement has begun so it will prevent simultaneous insertions from other cli-
ents. The server doesn't stop in the middle of an insert to allow for other changes just be-
cause of the LOW_PRIORITY setting. Incidentally, LOW_PRIORITY and
HIGH_PRIORITY aren't supported by InnoDB tables. It's unnecessary because it doesn't
lock the table, but locks the relevant rows.
One potential inconvenience with an INSERT LOW_PRIORITY statement is that your
mysql client will be tied up waiting for the statement to be completed successfully by the
server. So if you're inserting data into a busy server with a low priority setting using the
mysql client, your client could be locked up for minutes, maybe even hours, depending on
how busy the server is at the time. Using LOW_PRIORITY causes your client to wait until