Mysql changing table engine MyISAM to InnoDB

Question

On my site I have a visitor's table with 10 million rows.
Every request to the site inserts row to the table, in case the table is locked (usually in optimize query) visitors can't get into the site
The table engine is MyISAM and I want to change it to InnoDB
I have few questions:

• How can I change the table engine without stoping my site from working
• There is a way to optimize InnoDB table without locking it

Kaliope

Answer 1

The easiest way is

ALTER TABLE table_name ENGINE = InnoDB;

If you use InnoDB engine you should not worry about locking tables, because this engine locks data by rows.

Oilbird

If I will change the engine from MyISAM to InnoDB the table will be locked? – Kaliope

Yes I think so. But you can do that at time when your site has less visitors. – Oilbird

I just want to be sure, optimizing of InnoDB table will lock the table for writing or not? – Kaliope

If you want to make sure that table will be locked. Before running ALTER run LOCK TABLE table_name WRITE; and after run UNLOCK TABLES;. – Oilbird

Answer 2

oleksii.svarychevskyi is right, InnoDB uses row level locks, but if you do

ALTER TABLE table_name ENGINE = InnoDB;

to change table_name from MyIsam to InnoDB, there will be a metadata locking (at table level) because the original table engine was MyIsam.
If you try to do an UPDATE over table_name, this UPDATE will be enqueued until the ALTER TABLE ends (if you do a SHOW FULL PROCESSLIST you will see a "Waiting for table metadata lock" message associated to the UPDATE).

Dorthea