Why mysql has Lock_time in slow query log when execute select sql?
Time: 2018-11-22T15:06:32.654503Z
User@Host: root[root] @ localhost
[] Id: 3
Query_time: 0.058138 Lock_time: 0.000287 Rows_sent: 0 Rows_examined: 3115 SET timestamp=1542899192;
select * from COLUMNS where COLUMN_TYPE ='int';
Best Answer
From the looks of the query, my guess is that you are looking up all the defined columns in
INFORMATION_SCHEMA.COLUMNS
that are defined asINT
.The reason why there was a lock on
INFORMATION_SCHEMA.COLUMNS
???The table
INFORMATION_SCHEMA.COLUMNS
is defined as a temporary memory table. Memory tables undergo full tables locks when being accessed for change (such as when one doesALTER TABLE
orCREATE TABLE
) from anywhere in the database,Here is an excerpt from my old post How is INFORMATION_SCHEMA implemented in MySQL?
Please note what is in bold
When you look up the MEMORY storage engine in the Docs, it shows that Lock Granularity is Table. So, every table in the INFORMATION_SCHEMA, including
INFORMATION_SCHEMA.COLUMNS
will received a lock at some point, EVEN FOR A SELECT (just like a MyISAM table).