why plain select has Lock_time?

Question

I feel puzzled when I try to analyse slow_query_log in mysql. In my opinion, a plain(nonlocking) select has no need to lock any record because of mvcc, referece: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

But in slow_query_log, I find that a plain select has Lock_time. I use the sample database of mysql, namely sakila.

I execute the sql：

select * from customer limit 10;

then in the slow_query_log, I find the log:

# User@Host: root[root] @ localhost [::1]  Id:    13
# Query_time: 0.009601  Lock_time: 0.009362 Rows_sent: 10  Rows_examined: 10
SET timestamp=1650356898;
select * from customer limit 10;

I read the book High Performance Mysql (Baron Schwartz) (p735) and find that mysql will implicitly add shared locks for plain select.

so, what does nonlocking read (mvcc) actually mean?

Answer 1

I find answer by myself. A plain select will implicitly get table metadata lock.

A simple test:

first mysql connection:

mysql> lock table film write;
Query OK, 0 rows affected (0.00 sec)

second mysql connection:

mysql> select sleep(300) from film limit 1;

thrid mysql connection:

mysql> show processlist;
+----+-----------------+-----------------+--------+---------+-------+---------------------------------+-------------------------------------+
| Id | User            | Host            | db     | Command | Time  | State                           | Info                                |
+----+-----------------+-----------------+--------+---------+-------+---------------------------------+-------------------------------------+
|  4 | event_scheduler | localhost       | NULL   | Daemon  | 71205 | Waiting on empty queue          | NULL                                |
| 12 | root            | localhost:57270 | sakila | Sleep   |   116 |                                 | NULL                                |
| 29 | root            | localhost:62504 | sakila | Query   |    21 | Waiting for table metadata lock | select sleep(300) from film limit 1 |
| 30 | root            | localhost:62546 | sakila | Query   |     0 | starting                        | show processlist                    |
+----+-----------------+-----------------+--------+---------+-------+---------------------------------+-------------------------------------+
4 rows in set (0.00 sec)

A DML(select, insert, update, delete) will get shared table metadata lock, but a DDL(drop, alter...) will get exclusive table metadata lock. so, nonlocking read is still right, a write thread will not block a read thread. A nonlocking read will not get any lock except for shared table metadata lock.

Answer 2

Well, there are (intention) shared locks that are still needed. Here's the manual entry about locking in InnoDB: InnoDB Locking