Mysql – Bypass MyISAM table lock

full-text-searchinnodbmyisamMySQL

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?

Best Answer

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.

Related Question