What is InnoDB and MyISAM in MySQL?

InnoDB is a transactional storage engine of MySQL whereas MyISAM is a non-transactional storage engine. In other words, InnoDB follows the ACID properties to maintain the integrity of data but MyISAM doesn't follow ACID properties thus failing to maintain the integrity of the data.

In an InnoDB (transactional) table, the transactional changes can be easily undone if a rollback is required. But changes made to a MyISAM (non-transactional) table cannot be undone when rolling back a transaction is required.

For example, you want to transfer money from your checking account to saving account. This is done by a transaction which includes 5 queries.

1 START TRANSACTION;
2 SELECT balance FROM checking WHERE customer_id = 10233276;
3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
5 COMMIT;

Suppose, the process crashes at step 4. If a InnoDB table was used here, a rollback would undo the changes and you are saved from the risk of losing money. Literally, the table is unaware of any crash as the changes will not be commited to the table unless step 5 is successfully executed.

But in the case of a MyISAM table, one cannot undo the transactional changes when a rollback is called or if there is a crash leading to the failure of the transaction. This means, if the transaction crashed at step 3, money will be deducted from your checking account. But money wouldnot have been added to your savings account.

Example courtesy: "High Performance MySQL: Optimization, Backups, and Replication" - Book by Arjen Lentz, Derek J. Balling, Jeremy Zawodny, Peter Zaitsev, and Vadim Tkachenko

When your MySQL server crashes, the data can be recovered much easier from a set of MyISAM tables than from that big InnoDB transaction file. Each MyISAM table has a separate file, and if no write operations were being made to this table during the crash - it will be totally unaffected. In case of InnoDB, the entire transaction file of the entire MySQL server has to be re-indexed or whatever it does after a crash. That can get quite messy.