Please help me to understand why READ lock is required to maintain data consistency while backup with MyISAM tables and InnoDB tables does not require READ lock while backup.
The reason for this difference is that myisam has neither transactions, nor row level locking. Only table level locking is available. While innodb supports both transactions and row level locks, offering an alternative to table locks. However, you may choose to read lock innodb tables before backing them up, it is up to you.
As mysql manual on backup methods say on myisam:
FLUSH TABLES tbl_list WITH READ LOCK;You need only a read lock; this enables other clients to continue to query the tables while you are making a copy of the files in the database directory. The flush is needed to ensure that the all active index pages are written to disk before you start the backup.
On innodb you do not have to lock the entire table to get a consistent read of the data because with the appropriate transaction isolation mode and using a single transaction this is ensured by the innodb engine. The result is the same as if you locked the table, but you have better concurrency.
Again, the above linked documentation explains:
For InnoDB tables, it is possible to perform an online backup that takes no locks on tables using the --single-transaction option to mysqldump.
The documentation on single-transaction option contains further details:
This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.
When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.