How to copy a table in a production server without WRITE lock and without writing to a file I'm trying to copy a table in a production table. When I'm performing on InnoDB tables:

INSERT INTO new_table (SELECT * FROM table)


I have a write lock on table. Is it possible to avoid this lock? I am aware there is a solution to write to a file, but due to disk space limitation, I would have to pass on that solution. Also, are there any flags I might be able to add that will make it possible to kill the query during execution without performing rollback?
I suppose you mean you have a "read lock on the table" (you can't write, also known as shared locks). InnoDB writes like yours create read locks (writes have to wait). Reads can work continue without problem. Be careful with the terminology, a write lock is an "exclusive lock".

You can avoid most of the lock problems if you avoid the extra locks that innodb sets because of the statement based replication:

binlog-format = ROW
transaction-isolation = READ-COMMITTED
innodb_autoinc_lock_mode = 2


Be careful, because if the server is a master, or if you need point in time recovery, in certain cases it may create inconsistences. It also changes the locking logic, so be careful, as it may change the application behaviour. However, if you are not using replication or it is a development database, it will be safe. With this, you will be able to update and insert new records on the table at the same time the command runs.

The second part (kill the query without doing a rollback) is not possible for InnoDB due to internal consistency requirements (atomicity of transactions). However, there are several tricks to overcome it. One is to restart the server with innodb_force_recovery = 4 and then drop the table. A better approach would be to do the copy in chunks - a tool like pt-archiver with the --no-delete option, which allows you to copy in several transactions controlling the impact on the server (by the way, probably solving your first problem, too). The tool is a free and open source perl script, easy to use.