5

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.

Dev Null
  • 75
  • 7
  • 1
    Question may be already answered here: http://stackoverflow.com/questions/5043268/mysql-query-not-going-away-after-being-killed – Jimmy T. Feb 19 '15 at 19:16
  • "If the table email_data_test is InnoDB, then a lot of MVCC data was being written in ib_logfiles, which may not have occurred yet." – Jimmy T. Feb 19 '15 at 19:16
  • Thanks for the link; that's not one of the examples I found in my pre-post search, but it's similar to the ones I did find. The explanation they give in that thread for why it takes so long to kill - as apart from why the original query was taking so long in the first place - was "Killing the query only causes the InnoDB table email_data_inno_stage to execute a rollback." Which is just back to my original question: why would I need to rollback a select? – Dev Null Feb 19 '15 at 21:15
  • (Which, in retrospect, would have made a much better title for my original question.) – Dev Null Feb 19 '15 at 21:17
  • 1
    Enough so that I edited the title of the post. I hope that doesn't make this question hard to track for anyone, but I found a few posts on meta-SO that seemed to state that it was worth doing if you came up with a better title. Certainly feel free to change it back if that's inappropriate, or you think the new title is worse. (Specifically, I think this one is better because it distinguishes this question from a number which have already been answered on SO, but which were about INSERT / DELETES, where a rollback would be perfectly sensible.) – Dev Null Feb 19 '15 at 21:40
  • 2
    You don't need to rollback a SELECT. Just because terminating a write-query can often cause a long wait time while the database rolls back does not mean that a long wait time after terminating a read-query is also due to a rollback. It sounds like there's something else going on. – dg99 Feb 19 '15 at 21:41
  • @JimmyT. -- That other entry was about INSERTs being stuck. This is about SELECTs; there is a big difference since a SELECT should not have anything to "ROLLBACK". – Rick James Feb 20 '15 at 00:53

1 Answers1

2

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

David Soussan
  • 2,698
  • 1
  • 16
  • 19