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 big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action because the whole table is locked.

Is there a possibility or a workaround to bypass that table lock without getting a corrupt table? It seems that InnoDB is the right way (because of row locking), but InnoDB includes fulltext search only with a mysql version greater than 5.6 and this is not included in the current debian repository, even more the new debian wheezy has not mysql 5.6 included.

What can I do in my case?

share|improve this question

migrated from stackoverflow.com Mar 14 '13 at 9:28

This question came from our site for professional and enthusiast programmers.

    
I'm sure you can install 5.6 even though it's not in the official repository. –  a_horse_with_no_name Mar 14 '13 at 10:04

2 Answers 2

Since your main issue is the FULLTEXT index, I have two storage engines suggestions

SUGGESTION #1 : MyISAM

The MyISAM storage engine does a full table lock on the following:

  • ALTER TABLE
  • DELETE
  • UPDATE
  • INSERT

The only exception to the table lock blocking everything would be setting concurrent_insert. (See MySQL Documentation on Concurrent Inserts)

Try setting this in my.cnf

[mysqld]
concurrent_insert = ALWAYS

or

[mysqld]
concurrent_insert = 2

Adding new rows to a MyISAM table bypasses table locking only in this circumstance. You will have to experiment with adding rows to the table while updating fulltext indexed columns in another row of the table.

SUGGESTION # 2 : InnoDB

As of Feb 5, 2013, MySQL 5.6 is not out of beta and is now GA. InnoDB in MySQL 5.6 supports FULLTEXT indexing. Since doing this in an ACID-compliant Database is what you essentially need, look into upgrading. Do no load any preinstalled MySQL 5.6 packages if they predate Feb 5, 2013.

share|improve this answer

Maybe use an InnoDB table for some of the columns, and use a trigger to replicate the text data to a separate MyISAM table for fulltext searches?

Fulltext is relatively new in mysql, and its implementation is not as feature rich as in some other RDBMs. It sounds like you are running up against some fundamental limitations of mysql, and in the long term, if you expect your application to scale further, it may be worth looking into another tool than mysql. Postgres, for example, offers more control over locking and provides a better fulltext implementation.

share|improve this answer

Your Answer

 
discard

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