MySQL には InnoDB ロックに加えて、テーブルスキーマなどを対象とするメタデータロックがある。
MySQL InnoDBのロックの挙動 - sambaiz-net
Docker で MySQL を起動する。 8.0 から tx_isolation が廃止されたので transaction_isolation でトランザクション分離レベルを確認している。
$ 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)
テーブルを作成しデータを入れて SELECT してみる。現状メタデータ と InnoDB 共にロックはかかっていない。
> 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)
autocommit = 0 または BEGIN で明示的にトランザクションを作り SELECT を含むクエリを実行すると共有メタデータロックがかかる。
> 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)
InnnoDB のロックはかかっていないので他のスレッドから INSERT 等はできるが、ALTER TABLE のような DDL は 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'); -- 実行される
Query OK, 1 row affected (0.02 sec)
> ALTER TABLE a ADD COLUMN age INT; -- ブロックされる
> 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)