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';
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 as INT.
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 does ALTER TABLE or CREATE TABLE) from anywhere in the database,
Here is an excerpt from my old post How is INFORMATION_SCHEMA implemented in MySQL?
All tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. They are totally internal to MySQL, so the .frm mechanisms are handled in mysqld. In my answer, I first showed the table layout of INFORMATION_SCHEMA.TABLES. It is a temporary table in memory. It is manipulated using storage engine protocols. Thus, when mysqld is shutdown, all information_schema tables are dropped. When mysqld is started, all information_schema tables are created as TEMPORARY tables and repopulated with metadata for every table in the mysql instance.
Please note what is in bold
It is a temporary table in memory. It is manipulated using storage engine protocols.
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).
