Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
"InnoDB writes like yours create read locks (writes have to wait)" - are you sure about that? I thought with InnoDB readers would never block writers (and vice versa). –  a_horse_with_no_name Nov 15 '13 at 7:35
    
I should just change that in my.cnf? Is it possible to take effect without restart of mysql? –  Noam Nov 15 '13 at 7:39
    
@a_horse_with_no_name Readers never block writer. Writers (like this case) block other writers-that is a shared aka read lock. A read lock is a lock that prevents writers to execute. Please be careful with the vocabulary. BTW, readers may sometimes block writers in very specific cases due to metadata locks, but that is another story. –  jynus Nov 15 '13 at 7:53
    
But the statement never writes to table, only to new_table and the question says "I have a write lock on table" (not new_table). And I'm surprised that a simple insert would block other inserts or updates - I assumed MySQL would use row-level locks –  a_horse_with_no_name Nov 15 '13 at 7:55
    
@Noam, technically you can do it in a hot way (dynamically). But reducing the tx_isolation requires changing the binlog_format to take effect, and changing the binlog_format while the server is running is a potential source of problems. I do not recommend it for a production server, but that depends on how feasible is to restart your server (you seem to be experiencing problems right now). –  jynus Nov 15 '13 at 7:57
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.