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
