why mysql queries takes table lock in isolation read uncommitted?

Question

Is there any way to get mysql queries to not take any locks on myisam tables?

My problem seems to be that my Sphinx Search indexer takes a lock on one of my myisam tables for over 60s which seem to make update statements wait on the locks to be released which in turn makes other queries wait for the update statements to complete. The indexer uses mysql_use_result and does much of the indexing work before calling mysql_free_result. This is my best guess of what is happening at least but please let me know if this seems wrong to you.

One way to solve the problem seems to be to subdivide the indexer queries by id ranges. But it seems that I shouldn't have to do this since I really dont need this query to prevent any other query to run at the same time. Performance is much more important than consistency in this use case.

Thanks!

Answer 1

It seems that mysql queries always take read locks on myisam tables.

The solution to my problem was to set low-priority-updates = 1 in the [mysqld] section of my.cnf.

I am still wondering if there is a way to do queries in mysql without taking read locks but I'll open another question with a better title for that.

Thanks!