1

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!

Peder
  • 2,739
  • 3
  • 31
  • 33
  • http://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql – Haim Evgi Jan 06 '10 at 14:58
  • i think i have found a potential solution which is to "set global variable low_priority_updates = 1". this should result in select queries not having to wait for the update statement. i'll report back if it works. – Peder Jan 06 '10 at 16:18
  • isolation levels are not very meaningful with myisam tables, use innodb if you need transaction support – nos Jan 06 '10 at 19:36
  • It seems to have fixed my problem. Here is good article that describes the general problem and solutions: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html I'd still be interested to know why it isn't possible to execute a query that ignores locking if I am willing to accept inconsistencies as is the case for my indexing scenario. – Peder Jan 06 '10 at 21:47
  • nos: i don't need transaction support--i was just trying to get the query to ignore all locks and not prevent concurrent writes. – Peder Jan 06 '10 at 21:49

1 Answers1

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!

Peder
  • 2,739
  • 3
  • 31
  • 33