When a query runs against a MyISAM table, the entire table in which it is querying will be locked. If you want to execute the second query, first of all, the first query must be completed then the second query will run.
When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. It shows queries may run at the same time on the same table. Just one condition they have to follow “do not use the same row at the same time.”
What are some of the other differences are there between the database engines?
· It supports Table-level Locking.
· It designed for the need of speed.
· MyISAM does not support foreign keys. Hence we call MySQL with MyISAM is DBMS.
· MyISAM stores its tables, data, and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
· MyISAM does not support transaction. You cannot commit and rollback with MyISAM.
· MyISAM supports full-text search.
· InnoDB supports Row-level Locking.
· It designed for maximum performance when processing the high volume of data.
· InnoDB support foreign keys hence we call with MySQL.
· It is RDBMS InnoDB stores its tables and indexes in a tablespace.
· InnoDB supports transaction. You can commit and rollback with InnoDB.
Why/When would you choose to use the MyISAM engine versus the InnoDB engine?
MyISAM is designed with the idea that your database is queried far more than its updated and as a result, it performs very fast read operations. If you read to write(insert|update) ratio is less than 15% it’s better to use MyISAM.
InnoDB uses row-level locking, has commit, rollback, and crash-recovery capabilities to protect user data. It supports transaction and fault tolerance.
Also, if a MyISAM table has a problem, the problem will be limited to that table. Affecting the functionality of other tables or databases as can sometimes happen when an InnoDB table has a problem. For this reason, if you have possible in servers with multiple sites, MyISAM is recommended.