Description:
It seems EXPLAIN for the INSERT ... SELECT query that affects MyISAM table is waiting for the table level lock (and thus can be blocked):
mysql> show processlist;
+----+------+-----------------+------+---------+------+-------------------------
-----+----------------------------------------------------------+
| Id | User | Host | db | Command | Time | State
| Info |
+----+------+-----------------+------+---------+------+-------------------------
-----+----------------------------------------------------------+
| 39 | root | localhost:64584 | test | Query | 5 | Waiting for table level
lock | explain insert into tmi select sleep(10) from mysql.user |
| 40 | root | localhost:64690 | test | Query | 7 | User sleep
| insert into tmi select sleep(10) from mysql.user |
| 41 | root | localhost:65349 | test | Query | 0 | init
| show processlist |
+----+------+-----------------+------+---------+------+-------------------------
-----+----------------------------------------------------------+
3 rows in set (0.03 sec)
This is unexpected for EXPLAIN (and EXPLAIN for SELECT from MyISAM table is not blocked by concurrently running INSERT).
I do not see anywhere at http://dev.mysql.com/doc/refman/5.6/en/explain.html explanation of cases when EXPLAIN can be blocked by concurrent queries.
How to repeat:
Session 1:
create table tmi(c1 int) engine=MyISAM;
Session 2:
insert into tmi select sleep(100) from mysql.user;
Session 1:
explain insert into tmi select sleep(10) from mysql.user;
Session 3:
show processlist;
Suggested fix:
Do not try to set lock on MyISAM table for EXPLAIN? Or document this "feature" explicitly.
It's a pity to find out that EXPLAIN took XX minutes not because of some optimizer bug or problem with the plan, but because of some long running query against one of numerous tables used in the query that happened to be MyISAM.
Description: It seems EXPLAIN for the INSERT ... SELECT query that affects MyISAM table is waiting for the table level lock (and thus can be blocked): mysql> show processlist; +----+------+-----------------+------+---------+------+------------------------- -----+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+------------------------- -----+----------------------------------------------------------+ | 39 | root | localhost:64584 | test | Query | 5 | Waiting for table level lock | explain insert into tmi select sleep(10) from mysql.user | | 40 | root | localhost:64690 | test | Query | 7 | User sleep | insert into tmi select sleep(10) from mysql.user | | 41 | root | localhost:65349 | test | Query | 0 | init | show processlist | +----+------+-----------------+------+---------+------+------------------------- -----+----------------------------------------------------------+ 3 rows in set (0.03 sec) This is unexpected for EXPLAIN (and EXPLAIN for SELECT from MyISAM table is not blocked by concurrently running INSERT). I do not see anywhere at http://dev.mysql.com/doc/refman/5.6/en/explain.html explanation of cases when EXPLAIN can be blocked by concurrent queries. How to repeat: Session 1: create table tmi(c1 int) engine=MyISAM; Session 2: insert into tmi select sleep(100) from mysql.user; Session 1: explain insert into tmi select sleep(10) from mysql.user; Session 3: show processlist; Suggested fix: Do not try to set lock on MyISAM table for EXPLAIN? Or document this "feature" explicitly. It's a pity to find out that EXPLAIN took XX minutes not because of some optimizer bug or problem with the plan, but because of some long running query against one of numerous tables used in the query that happened to be MyISAM.