ansaurus

Question

Why do reads block other reads in MyISAM?

Answer 1

+4 A:

All the selects are locked because they are waiting for your UPDATE to finish, even though the update is not running yet

I guess the order of your queries was:

SELECT COUNT(*) FROM ...
UPDATE metaward_award ...
SELECT metaward_alias...
...

query1 is executing.. it takes long time

query2 comes, want to get a write lock, but cant, because there is a read, so it waits and signal it's waiting to lock

query3 comes, want to read but cant, because query2 already signal a lock

now you will say.. MyISAM is concurrent reads, query3 should read while query1 is reading.. but if that was the case, and query3 took long time too, you could make a "starvation" on query2, meaning it will never get executed if query1 & query3 took a long time and go after each other, example:

assuming query1, query3 takes 5 secs to exec

second | action
1 | query1 starting
2 | query2 cant start, waiting
3 | query3 starting
4 | 
5 |
6 | qurey1 finished (query2 cant start because query3 is still reading)
7 | another call for query1 starting
8 | qurey3 finished (query2 cant start because the new query1 is still reading)

amikazmi 2009-09-16 08:16:06

you can improve your performance here by running a slave and executing your long-running queries on there.

longneck 2009-10-21 20:04:49

Answer 2

+2 A:

You can use the server option --low-priority-updates to give SELECT statements priority over table modifying operations (INSERT, REPLACE, DELETE and UPDATE)

You can also use the LOW_PRIORITY modifier after your statement (e.g. UPDATE LOW_PRIORITY)

Edit: there is more information here: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

meanstreakuk 2009-09-16 08:29:18

Can I make my big long SELECT run in a low priority?

Paul Tarjan 2009-09-16 17:54:46

LOW PRIORITY for SELECT doesn't make much sense. once a long SELECT starts, it's still going to take a long time. what would it take a back seat to?

longneck 2009-10-21 20:04:12

The OP has a problem, as explained in the accepted answer, where the update that is waiting for the select to finish is blocking all the other selects from starting.Setting the '--low-priority-updates' option in the my.cnf file would bump any update operations to the back of the queue and the selects waiting to happen would be allowed to start.

meanstreakuk 2009-10-22 10:14:58

ansaurus

tags:

views:

answers:

Why do reads block other reads in MyISAM?

related questions