I have encountered this problem during an interview with one famous company, the question is:

Why MyISAM storage engine does not support row level locking as InnoDB storage engine

As far as I know the main difference is that InnoDB has clustered index whereas MyISAM does not have.

I found from some blog saying that the reason why is:

Since MyISAM only has secondary index, in other words, the data and index are stored separately, it is will hurt the performance when lock the data and secondary index at the same time.

I cannot concur with this hint as I think some InnoDB table also has secondary index.

CodePudding user response：

MyISAM is an read optimized storage engine, non-MVCC, crash unsafe, storage engine that shouldn't be written to.

Having row locking is excessive to these purposes.

CodePudding user response：

The clustering of the PK is not the main difference between the Engines.

MyISAM was a quick-and-dirty Engine that worked fine most of the time. For avoiding conflicts, a simple "table-level" locking mechanism was invented.

The main differences (and many of the secondary differences) stem from ACID. InnoDB adds crash-safe features. The Question expected you to understand that ne expoind on it.

Row-level locking allows more concurrency between write operations, while also providing other aspects of ACID.

Atomic: If MyISAM dies in the middle of doing UPDATE t SET x=x 1, some of the rows have been incremented; some have not. There is no recovery (without reloading the data.) InnoDB stands on its head to guarantee "all or none".

Etc for Concistency, Isolation, and Durability.

Having the PRIMARY KEY "clustered" with the data is not a requirement, even for an ACID-complient DB. (See competing vendors and how they use "row numbers".) With InnoDB, certain queries run faster due to this clustering of the PK.

Yes, InnoDB has secondary indexes. Both the main data and each secondary index is implemented via a B Tree. But for secondary indexes, the key's column(s) plus the PK's column(s) are to be found in the leaf nodes. There are pros and cons to this design, but it is not required

MyISAM's indexes (PK and secondary are identical) are all in one file (.MYI) and contain the key's columns plus a 'pointer' to the data. It is in a BTree structure. The Data is stored in a separate file (.MYD). Rows are 'pointed' to either by a byte offset into the file or by a row number (if only fixed-length columns).