DELETE LOW_PRIORITY row visibility

I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and actually delete the rows from disk when no clients are accessing the table, or is the point delaying removal of visibility?

From the docs:


If you specify LOW_PRIORITY, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking.

All this means is that the DELETE LOW_PRIORITY statement will not begin processing until all read locks are finished. If another read lock comes in before the DELETE LOW_PRIORITY statement begins, it will wait.

Once the DELETE LOW_PRIORITY statement has the lock though, it will acquire the table-lock and run until complete (or killed).

Under normal table lock situations, any write requests will be given higher priority than read requests. Example: if you have a write lock going on, then a read request goes in the read queue, and then another write request comes in, the second write request will execute before the read requests.

Using LOW_PRIORITY, the situation is reversed. If a read lock is going on, and a write comes in, it will wait. If another read comes in before the write gets the lock, the second read will execute and the write will wait.

So, if you are using DELETE LOW_PRIORITY on a table that is frequently read from, the DELETE LOW_PRIORITY statement could be waiting a long time.

Once a write lock is acquired in a table-locking situation, any reads will wait in the queue until the write lock is finished.

StackExchange Database Administrators Q#21483, answer score: 5