concurrent READ and WRITE on MySQL Table
mysql, sql
Solution
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.
Problem
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