MySQL: MyISAM vs InnoDB

I was pondering what is the difference of MyISAM and InnoDB? They are both types of database in MySQL. For MySQL v5.4 and lower, the default is MyISAM; For MySQL v5.5 and higher, the default is InnoDB. What is their difference? How will I know what to choose? Choosing the right type is crucial in database designing.

	MyISAM	InnoDB
Performance	Better in terms of reading data	Better when lots of concurrent updates/inserts
Transactions	Better for normal transactions	Better for systems with big usage of transactions ex:Banks
Full Text Searching	Applicable	Not Applicable
Storage Space	More compact space usage	Uses more space
Data Security	Run a check table to ensure things stay stable	Not needed, it is already secured
Transaction Isolation	Table-level Locking	Row-level Locking

I think the most important part of choosing is the transaction isolation. In reality, there are a lot of users accessing the database reading, writing and retrieving data and how it is handled is very important. So what is the difference between table and row locking anyway? MySQL defined it as follows:

Row-level Locking allows multiple sessions and applications can read from and write to the same table simultaneously, without making each other wait or producing inconsistent results.

Table-level Locking do not have much overhead but only one session can write to a table at any one time. For better performance, use them primarily for tables that are queried often and rarely inserted into or updated.

Okay, so now we know what this locking means and how important it is. For large tables, table locking is often better than row locking, but there are some disadvantages:

1. Table locking enables lots of read concurrently but if a session wants to write to a table, that session might wait until all the other sessions are done. During the update, all other sessions that wants to access the particular table must wait until the update is done.
2. Table locking is also problematic if the session is waiting because disk is full. All Sessions will be left in a hanging state until more space is made available
3. A long select statement that takes time to finish will put other update or select statements in queue. In effect, it makes the other sessions appear unresponsive or slow.

With this in mind, it makes me lean more on using InnoDB. No wonder MySQL, version 5.5 and higher, chooses InnoDB as the default type.