Why do I need to rollback a SELECT in mysql (InnoDB)?

Question

I ran an ill-advised SELECT * on a large InnoDB table in a mysql database.

So after 10 minutes or so I realised the error, found the connectionid with show processlist, and attempted to kill both the connection and the query with the kill command. Then I ran another query on the same table.

show processlist shows the original select to have received the Killed flag, but is stuck in the "Sending data" state. The followup query is waiting for a lock. This has been going on for several hours.

Now I understand why, if my original query had been modifying the table in any way, that what I'd be describing would be waiting for the rollback.

But this was a select; what would it even mean to rollback a select ?

So I wondered if anyone could tell me what I'm waiting for, and if there are any convenient ways to just cancel a select query.

Answer 1

You are not waiting for a transaction rollback, as others have said. I also have encountered this problem: call it a bug because it is one really though MySQL do not agree.

Now I can not give you anything but an educated guess for the reason you get this 'deadlock', but I can give you a solution:

UNLOCK TABLES http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html