I am using a MyISAM database to store user data because I am using full text searching. I have looked in to options like Sphinx but cannot install this on my shared hosting plan which uses MySQL 5.5.36.

I have been researching the problems of MyISAM and have come across the full table locking.

By using MyISAM, will this mean that, say if multiple users are on my website and two users are searching and two users are trying to INSERT into the same table that they will be unable to do so?

Is anyone able to explain to me how this works?



One of the biggest problems with MyISAM is the low write concurrency. In the most general case, a single write (update, delete or insert) to a single MyISAM table will block all other writes and reads. Multiple reads, however, can happen concurrently.

You can force MyISAM to perform concurrent inserts at the end (not for updates and deletes), but it normally requires a defragmented table. Read more about concurrent inserts for MyISAM on the manual. Multiple writes can always happen to different tables.

InnoDB doesn't have those problems, and also has better memory management, corruption resilience, support for foreign keys and much more features. With InnodB you can write to the same table in concurrency as long as you do not write to the same exact row (disabled to avoid race conditions). It can provide also reads and writes at the same time, even on the same rows thanks to multi-versioning.

If MySQL FULLTEXT is good enough for you, I would recommend you to search for a hosting that provides MySQL 5.6, where InnoDB Fulltext is available. Otherwise, you will have to search for an alternative application, like the mentioned sphinx.

  • 2
    This is good, but to clarify a bit further based on what the OP seems to be asking: it just means your other task/process will wait a moment. The second user won't see an error, they'll experience a fraction of a second longer delay in accessing the database. MySQL queues queries and will process them (provided you're not under heavy load that is continually blocking access, or something like that). It's good to monitor and be aware of how this affects your application, but doesn't mean it will instantly break if two users access it simultaneously.
    – ibennetch
    Nov 29 '14 at 0:59
  • thanks for your responses, that clears a few things up! I was under the impression that the application would 'break' or prevent a user from uploading data but it's good that this isn't the case, I will have to monitor it to see the implications on performance thanks again!
    – neeko
    Nov 29 '14 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.