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