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.
[9 Oct 2013 8:01]
MySQL Verification Team
Hello Valeriy
Thank you for the bug report and test case.
Verified as described.
Thanks,
Umesh
Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders. It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.