concurrent READ and WRITE on MySQL Table

Question

If a table is frequently written from front end and same table has to be as frequently searched as well. Both are performance critical.

For example a POST table which is searchable having Full Text index on its "content" column ? If few users are writing posts, it goes to same table and then other users will search on table same time for keywords.

Will UPDATE/INSERT operation lock SELECT queries in above case ?

database - MySQL

Answer 1

This depends on the Storage Engine of the Table

InnoDB

InnoDB supports MVCC and 4 Transaction Isolation Levels

• READ-UNCOMMITTED
• READ-COMMITTED
• REPEATABLE-READ (default)
• SERIALIZABLE

This allows for INSERTs, UPDATEs, DELETEs, and SELECTs to live harmoniously 99.999% of time

MyISAM

This is a totally different playing field. By default, every INSERT, UPDATE, and DELETE locks the entire table. INSERTs can have table locking disabled by setting concurrent_insert to 2. (See Concurrent Inserts for more information). Otherwise, UPDATEs and DELETEs can still wreak some havoc doing full table locks.