Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

The rows remain visible. Test (1) shows that the delete does not prevent any rows from being visible to subsequent queries. Test (2) illustrates the table-lock taken immediately by a normal delete - the query waits until the delete finishes and returns a zero count.

testbed and long running query:

create database stack;
use stack;
--
create table my_table (id int auto_increment primary key, varchar_val varchar(10));
insert into my_table (varchar_val)
select 'HELLO'
from (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s1,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s2,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s3,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s4;
--
select avg(t1.id), avg(t2.id), count(*) from my_table t1 cross join my_table t2;
--

test (1): with low_priority:

--session 2:
delete low_priority from stack.my_table;
/*
Query OK, 10000 rows affected (21.84 sec)
*/

--session 3:
select count(*) from stack.my_table;
/*
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)
*/

test (2): without low_priority

--session 2:
delete low_priority from stack.my_table;
/*
Query OK, 10000 rows affected (21.15 sec)
*/

--session 3:
select count(*) from stack.my_table;
/*
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (18.10 sec)

*/
share|improve this answer
add comment

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.

share|improve this answer
    
It's not about 'visibility of rows'. You first need to understand how table locks work. –  Derek Downey Jul 25 '12 at 15:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.