地方エンジニアの学習日記

興味ある技術の雑なメモだったりを書いてくブログ。たまに日記とガジェット紹介。

【MySQL】読み取り専用トランザクションもmetadata lockを取る

dev.mysql.com

BEGINなどをせずにSELECTを実行しただけでも読み取り専用トランザクションというのが開始されるらしい。スロークエリが実行されているテーブルに対してDDLを実行するとmetadata lockが取られて以降のDMLが全てブロックされる問題があるがBEGINとかしてない場合は問題ないのでは?と思ったので調べてみた。

TrxA > SELECT SLEEP(3600) from HOGE;

とやってHOGEに対してDDLを発行すると3600sの間DDLは待たされ以降のDMLを全て待たされる。読み取り専用トランザクション中にALTERが実行されるとトランザクションの分離を実現できなくなるので考えてみればそれはそうという感じである。

実験してみる

こんなテーブルにALTER TABLEを実行してみる

mysql> desc test_table;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(20)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20) | NO   |     | NULL    |                |
| created_at | datetime    | YES  |     | NULL    |                |
| updated_at | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

DMLを実行する。レコード数が少ないのでsleepでスロークエリを再現している

select sleep(30) from test_table;

ここでinformation_schemeを見てみる。trx_is_read_onlyが1でトランザクションが開始されているのがわかる。

mysql> select * from INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 421891224008528
                 trx_state: RUNNING
               trx_started: 2023-01-14 02:49:41
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 2
                 trx_query: select sleep(30) from test_table
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 1
trx_autocommit_non_locking: 1
1 row in set (0.00 sec)

このタイミングでALTER TABLEを実行してみる。DDLは待たされてWaiting for table metadata lockとなっている。

mysql> show processlist;
+----+------+-----------+--------------------+---------+------+---------------------------------+---------------------------------------------------------+
| Id | User | Host      | db                 | Command | Time | State                           | Info                                                    |
+----+------+-----------+--------------------+---------+------+---------------------------------+---------------------------------------------------------+
|  2 | root | localhost | test               | Query   |   10 | User sleep                      | select sleep(30) from test_table                        |
|  3 | root | localhost | information_schema | Query   |    0 | starting                        | show processlist                                        |
|  4 | root | localhost | test               | Query   |    9 | Waiting for table metadata lock | ALTER TABLE test_table ADD PhoneNumber VARCHAR(20) NULL |
+----+------+-----------+--------------------+---------+------+---------------------------------+---------------------------------------------------------+

metadata lockはperformance_schemaから見ることができるので有効にしてみてみるとtest_tableに対してロックを取得されているというのがわかる。

mysql> UPDATE performance_schema.setup_instruments
    -> SET ENABLED = 'YES', TIMED = 'YES'
    -> WHERE NAME = 'wait/lock/metadata/sql/mdl';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: test_table
OBJECT_INSTANCE_BEGIN: 140415164543808
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 27
       OWNER_EVENT_ID: 13