MySQL has metadata locks that target table schemas and other objects in addition to InnoDB locks.
MySQL InnoDBのロックの挙動 - sambaiz-net
Start MySQL using Docker. Since tx_isolation was removed in MySQL 8.0, I use transaction_isolation to check the transaction isolation level.
$ docker run -d --name mysql-lock-test -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -e MYSQL_DATABASE=testdb mysql:8.0
$ docker exec -it mysql-lock-test mysql testdb
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
Create a table, insert some data, and SELECT from it. Currently, there are no metadata or InnoDB locks.
> CREATE TABLE a (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(128) NOT NULL);
> INSERT INTO a (name) VALUES ('Alice'), ('Bob'), ('Charlie');
> SELECT * FROM a WHERE id = 1;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
+----+-------+
1 row in set (0.00 sec)
> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA = 'testdb';
Empty set (0.00 sec)
> SELECT THREAD_ID, OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS FROM performance_schema.data_locks;
Empty set (0.00 sec)
When you set autocommit = 0 or explicitly create a transaction with BEGIN and execute a query containing SELECT, a shared metadata lock is acquired.
> BEGIN; -- or SET autocommit = 0;
> SELECT * FROM a WHERE id = 1;
...
1 row in set (0.00 sec)
> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA = 'testdb';
+-------------+-------------+-------------+-----------------+
| OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+-------------+-------------+-----------------+
| a | SHARED_READ | GRANTED | 48 |
+-------------+-------------+-------------+-----------------+
1 row in set (0.00 sec)
> SELECT THREAD_ID, OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS FROM performance_schema.data_locks;
Empty set (0.00 sec)
Since there are no InnoDB locks, other threads can INSERT, but DDL statements like ALTER TABLE will be PENDING.
> SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID();
+-----------+
| THREAD_ID |
+-----------+
| 50 |
+-----------+
1 row in set (0.01 sec)
> INSERT INTO a (name) VALUES ('Delta'); -- executes successfully
Query OK, 1 row affected (0.02 sec)
> ALTER TABLE a ADD COLUMN age INT; -- gets blocked
> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA = 'testdb';
+-------------+---------------------+-------------+-----------------+
| OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+---------------------+-------------+-----------------+
| a | SHARED_READ | GRANTED | 49 |
| NULL | INTENTION_EXCLUSIVE | GRANTED | 50 |
| a | SHARED_UPGRADABLE | GRANTED | 50 |
| #sql-1_a | EXCLUSIVE | GRANTED | 50 |
| a | EXCLUSIVE | PENDING | 50 |
+-------------+---------------------+-------------+-----------------+
5 rows in set (0.00 sec)
> SHOW PROCESSLIST;
+----+-----------------+-----------+--------+---------+-------+---------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------+---------+-------+---------------------------------+----------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 10157 | Waiting on empty queue | NULL |
| 9 | root | localhost | testdb | Query | 0 | init | SHOW PROCESSLIST |
| 10 | root | localhost | testdb | Query | 17 | Waiting for table metadata lock | ALTER TABLE a ADD COLUMN age INT |
+----+-----------------+-----------+--------+---------+-------+---------------------------------+----------------------------------+
3 rows in set, 1 warning (0.00 sec)