How to recover from two queries stuck in kill state causing all the database to not respond? During a INSERT INTO new_table (f1, f2, ..., fn) (SELECT f1, f2, ..., fn FROM table) I clicked the new_table in php-myadmin which ran a simple SELECT * FROM new_table LIMIT 0, 30 which ofcourse couldn't be completed because new_table was not ready.

I tried Killing the query, but it just kept itself in Kill state and therefore I assume it caused some sort of rollback in new_table.

The copy data query is for a 200GB table and was running for about 10hours till this interruption (I think it was almost finished). I tried to also kill that query thinking it will free things up, but again it got stuck.

I'm seeing this in SHOW ENGINE INNODB STATS 

    ---TRANSACTION 5254EC173, ACTIVE 39375 sec rollback
mysql tables in use 2, locked 2
ROLLING BACK 4169557 lock struct(s), heap size 371276216, 34345786 row lock(s), undo log entries 26939759


Is there anything I can do? Any time estimation? Will it try to rollback the whole table and then try to return that whole data? Will it try to use diskspace for all these operations? Is there anything to avoid the lock? would it be a good idea to do mysql restart or will this just start the operation over again?
Please seem my answer to your other similar question involving innodb_force_recovery = 4 https://dba.stackexchange.com/a/53355/30545