How to copy data from another table without lock the table in MYSQL 6.2?

Question

I have a table that has all history data in Mysql server and it is very huge (about 700 million rows). I am creating a new table with the same columns but with partitioning, then I need to copy all data from the old table into the new partitioned table. I have already got the right script to do that but I think it might lock the table. I don't want that happen because it is on production server. What should I do to avoid locking the table?

Answer 1

Give that the tables have the exact same columns you can do something like this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
INSERT INTO NEW_TABLE (SELECT * FROM OLD_TABLE);
COMMIT ;

I've included some additional explanation based on Wistar's comment. The read levels that can be used here are:

• READ COMMITTED: A somewhat Oracle-like isolation level with respect to consistent (nonlocking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot
• READ UNCOMMITTED: SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.
• REPEATABLE READ: This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.
• SERIALIZABLE: This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

I hope this helps.