もりた

 これはなにども、もりたです。
最近、仕事上の都合でオンラインDDLについて調べる機会がありました。その際、各種クエリがどんなロックをとっているのか、考えれば考えるほど分からなくなってしまったため、今回動かして確かめようと思います。

 調査の方針
 確認したいクエリとロッククエリ
CRUDひととおり
全行と単一行

DDL

ロックの対象
レコード
テーブル
メタデータ
その他
スキーマ、データベース、バックアップ、データスペースなどがあるらしいと聞いた



 どうやって調査するか？基本方針
各種クエリを実行中/待ち状態にし、どんなロックを取っているか確認する

どうやって「実行中/待ち状態」にするか？
DML
トランザクションを張ってから各種クエリを流したり、実行時間を意図的に長くして各種クエリを流す。そんでロックの確認をする。

DDL
DDLはトランザクションを張れないので、先行するDMLを意図的に詰まらせて処理待ちの状態にする。


どうやって「どんなロックを取っているか確認する」か？
以下のテーブルを確認する
メタデータ
performance_schema.metadata_locks

普通のロック
performance_schema.data_locks



その他懸念
先行するクエリがsleepの時とtransaction貼ってる時ってなんか違うか？
→両方試してみよう

DDLの実行にはフェーズがあり、後ろのフェーズはこれでは確認できないような気がする
https://medium.com/@hamidrezaniazi/behind-the-scenes-of-mysql-online-ddl-locks-638804b777b3
を読んでみるとか、あとはなんだ？　フェーズごとに引っかかるやつを用意する
あとはもう今回確認するのは諦めて、その説明を信じる

流すDDLの網羅性をどう担保するか
MySQL公式のオンラインDDLページに載っているものの中から代表的なものを流そう



 環境以下の記事で作成したやつを使う。8系です。

https://zenn.dev/levtech/articles/8bb56f30c2edd6

もりた

 やってみる
 対象この図

もりた

前提 - 何もしてない状態でどう表示されるか

-- デフォルト
/*

■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, performance_schema, metadata_locks, , 281472225367456, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 46, 291

■DL
なし
*/

metadata_locksを見るときもMDL（メタデータロック）をとるらしい。

もりた

SELECT

全行、トランザクション


-- SELECT　全行

begin;

select * from chapters;

rollback;
commit;
/*

■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, chapters, , 281472359563808, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 68, 19
↑これだけが追加された

■DL
なし

■結論
SELECTはメタデータロックを取るけど通常のロックは取ってない。これ勘違いしてたな...

*/

特定行、トランザクション


-- SELECT　特定行
begin;

select * from chapters where chapter_id = 1;

rollback;
commit;
/*

■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, chapters, , 281472359636304, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 68, 28

■DL
なし
*/

特定行、ステートメント


-- SELECT　特定行　ステートメント

select * from chapters where chapter_id = 1 and SLEEP(10);

/*

■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, chapters, , 281472362471408, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 73, 124


■DL
なし
*/

もりた

UPDATE

全行、トランザクション


-- UPDATE

begin;

update chapters set chapter_title = "おにく";

rollback;
commit;

/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, chapters, , 281472359597744, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 73, 64
TABLE, database, works, , 281472359508800, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 73, 64
TABLE, database, episodes, , 281472359631456, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 73, 64


■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215698512:44:1075:281473097734992, 3607, 73, 64, database, chapters, , , , 281473097734992, TABLE, IX, GRANTED, 
INNODB, 281473215698512:44:9:413:1:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, supremum pseudo-record
INNODB, 281473215698512:44:9:413:2:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, 101152
INNODB, 281473215698512:44:9:413:3:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, 101153
INNODB, 281473215698512:44:9:413:4:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, 101154
INNODB, 281473215698512:44:9:413:5:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, 101155
INNODB, 281473215698512:44:9:413:6:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, 101156
INNODB, 281473215698512:44:9:413:7:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, 101157
INNODB, 281473215698512:44:9:413:8:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, 101158
INNODB, 281473215698512:44:9:413:9:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, 101159
INNODB, 281473215698512:44:9:413:10:281472868490472, 3607, 73, 64, database, chapters, , , PRIMARY, 281472868490472, RECORD, X, GRANTED, 101160

■感想
すご...
テーブルレベルと行レベルでロックを取っている。けど一行指定してやってるのとノリ自体は同じだ。

*/

特定行、トランザクション

begin;

update chapters set chapter_title = "おにく" where chapter_id = 1;

rollback;
commit;

/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, chapters, , 281472359626464, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 68, 35
TABLE, database, works, , 281472359632912, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 68, 35
TABLE, database, episodes, , 281472359574336, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 68, 35

■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215700128:14:1075:281473097747376, 3605, 68, 35, database, chapters, , , , 281473097747376, TABLE, IX, GRANTED, 
INNODB, 281473215700128:14:9:6:251:281473097744576, 3605, 68, 35, database, chapters, , , PRIMARY, 281473097744576, RECORD, X,REC_NOT_GAP, GRANTED, 1


*/

特定行、ステートメント


-- UPDATE 特定行 ステートメント

update chapters set chapter_title = "おにsi" where chapter_id = 1 and SLEEP(20);

/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
GLOBAL, , , , 281472362471408, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_base.cc:3059, 73, 131
TABLE, database, chapters, , 281472360015632, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 73, 131
TABLE, database, works, , 281472362367824, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 73, 131
TABLE, database, episodes, , 281472359741440, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 73, 131

■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215698512:66:1075:281473097734992, 3620, 73, 132, database, chapters, , , , 281473097734992, TABLE, IX, GRANTED, 
INNODB, 281473215698512:66:9:6:231:281473097732080, 3620, 73, 132, database, chapters, , , PRIMARY, 281473097732080, RECORD, X,REC_NOT_GAP, GRANTED, 1

■感想
ステートメント単位にしたらGLOBAL追加された...！
ただこのSLEEPの挙動ちょっとわからんな。SLEEPをつけると更新されない。
*/

もりた

INSERT


-- INSERT
begin;


INSERT INTO `database`.`browsing_histories`
(`episode_id`,`user_id`,`created_at`,`updated_at`,`deleted_at`,`create_user_id`,`update_user_id`,`delete_user_id`)
VALUES (1, 1, now(),now(),null,1,1,null);

rollback;
commit;
/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, browsing_histories, , 281472359508800, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 73, 74
TABLE, database, episodes, , 281472359567760, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 73, 74
TABLE, database, users, , 281472359534128, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 73, 74


■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215698512:46:1076:281473097735080, 3610, 73, 74, database, episodes, , , , 281473097735080, TABLE, IS, GRANTED, 
INNODB, 281473215698512:46:1080:281473097734992, 3610, 73, 74, database, browsing_histories, , , , 281473097734992, TABLE, IX, GRANTED, 
INNODB, 281473215698512:46:1070:281473097735168, 3610, 73, 74, database, users, , , , 281473097735168, TABLE, IS, GRANTED, 
INNODB, 281473215698512:46:4:5:2:281473097732424, 3610, 73, 74, database, users, , , PRIMARY, 281473097732424, RECORD, S,REC_NOT_GAP, GRANTED, 1
INNODB, 281473215698512:46:10:6:2:281473097732080, 3610, 73, 74, database, episodes, , , PRIMARY, 281473097732080, RECORD, S,REC_NOT_GAP, GRANTED, 1

■感想
スキーマレベルのMDLをとる、というのが実行タイミングによって出たり出なかったりする。。けどほぼない。。
またUPDATEとかは更新対象がインデックス行かどうかというのも一つの違いだろうなと思った。
まあ全てのパターンを網羅するのは現実的ではない。どっちかっていうと原理の方を理解したらいいんだろうな。

*/

もりた

DELETE


-- DELETE

begin;

DELETE FROM `database`.`browsing_histories`
WHERE episode_id = 1 and user_id = 1;

rollback;
commit;
/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, browsing_histories, , 281472362471408, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 73, 120


■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215698512:60:1080:281473097734992, 3618, 73, 120, database, browsing_histories, , , , 281473097734992, TABLE, IX, GRANTED, 
INNODB, 281473215698512:60:14:6:366:281473097732080, 3618, 73, 120, database, browsing_histories, , , PRIMARY, 281473097732080, RECORD, X,REC_NOT_GAP, GRANTED, 1, 1

*/

もりた

DDLの検証

MySQLによるDDLはトランザクションがはれないため、DDLとぶつかるようなトランザクションを先に取っておいて、詰まったDDLのロックを確認する。
だいたいUPDATEかINSERTをかけてるが、THREAD_IDで先行するクエリなのか見分けてもらえると良い

-- デフォルト

/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, chapters, , 281473152070160, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 75, 158
TABLE, database, works, , 281473151509104, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 158
TABLE, database, episodes, , 281473145639760, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 158
TABLE, performance_schema, metadata_locks, , 281472296949696, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 71, 217

■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215700128:52:1075:281473097747376, 3625, 75, 158, database, chapters, , , , 281473097747376, TABLE, IX, GRANTED, 
INNODB, 281473215700128:52:9:6:231:281473097744576, 3625, 75, 158, database, chapters, , , PRIMARY, 281473097744576, RECORD, X,REC_NOT_GAP, GRANTED, 1

*/

もりた

テーブル追加



-- テーブル追加
CREATE TABLE sample_locks_2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chapter_id INT unsigned,
    name VARCHAR(10),
    created_at DATE,
    FOREIGN KEY (chapter_id) REFERENCES chapters(chapter_id)
);

/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, chapters, , 281473151850368, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 75, 165
TABLE, database, works, , 281473151504944, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 165
TABLE, database, episodes, , 281473151505808, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 165
GLOBAL, , , , 281472362476528, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_base.cc:5476, 73, 240
BACKUP LOCK, , , , 281472360436304, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5483, 73, 240
SCHEMA, database, , , 281472359534128, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5463, 73, 240
TABLE, database, sample_locks, , 281472360716416, EXCLUSIVE, TRANSACTION, GRANTED, sql_parse.cc:6142, 73, 240
SCHEMA, database, , , 281472360707536, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:9944, 73, 240
TABLE, database, chapters, , 281472362409152, EXCLUSIVE, STATEMENT, PENDING, sql_table.cc:9936, 73, 240
TABLE, performance_schema, metadata_locks, , 281472297017408, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 71, 225

■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215700128:52:1075:281473097747376, 3625, 75, 158, database, chapters, , , , 281473097747376, TABLE, IX, GRANTED, 
INNODB, 281473215700128:52:9:6:231:281473097744576, 3625, 75, 158, database, chapters, , , PRIMARY, 281473097744576, RECORD, X,REC_NOT_GAP, GRANTED, 1

*/

もりた

カラム名変更


-- カラム名変更
ALTER TABLE sample_locks
CHANGE COLUMN name user_name VARCHAR(10);

/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, sample_locks, , 281473151504944, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 75, 174
SCHEMA, database, , , 281473151505808, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, dd_schema.cc:108, 75, 174
TABLE, database, chapters, , 281473151136528, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 174
GLOBAL, , , , 281472628020064, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_base.cc:5476, 76, 48
BACKUP LOCK, , , , 281472628035024, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5483, 76, 48
SCHEMA, database, , , 281472640910352, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5463, 76, 48
TABLE, database, sample_locks, , 281472640910256, SHARED_UPGRADABLE, TRANSACTION, GRANTED, sql_parse.cc:6142, 76, 48
TABLESPACE, , database/sample_locks, , 281472640935872, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, lock.cc:807, 76, 48
SCHEMA, database, , , 281472640914736, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:1312, 76, 48
TABLE, database, chapters, , 281472628053984, SHARED_UPGRADABLE, STATEMENT, GRANTED, sql_table.cc:1304, 76, 48
TABLE, database, #sql-1_27, , 281472630090848, EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:17126, 76, 48
TABLE, database, sample_locks, , 281472628048448, EXCLUSIVE, TRANSACTION, PENDING, mdl.cc:3762, 76, 49
TABLE, performance_schema, metadata_locks, , 281472296851328, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 71, 237


■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215700128:56:1083:281473097747376, 3653, 75, 174, database, sample_locks, , , , 281473097747376, TABLE, IX, GRANTED, 
INNODB, 281473215700128:56:1075:281473097747464, 3653, 75, 174, database, chapters, , , , 281473097747464, TABLE, IS, GRANTED, 
INNODB, 281473215700128:56:9:6:231:281473097744576, 3653, 75, 174, database, chapters, , , PRIMARY, 281473097744576, RECORD, S,REC_NOT_GAP, GRANTED, 1


*/

-- カラム名変更（インデックス列）
ALTER TABLE sample_locks
CHANGE COLUMN id sample_id INT AUTO_INCREMENT PRIMARY KEY;

/*
できない。削除して追加してる。
*/

もりた

インデックス追加


-- インデックスの追加
CREATE INDEX idx_user_name ON sample_locks(user_name);


/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
GLOBAL, , , , 281472562231776, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_base.cc:5476, 77, 50
BACKUP LOCK, , , , 281472560956320, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5483, 77, 50
SCHEMA, database, , , 281472562479248, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5463, 77, 50
TABLE, database, sample_locks, , 281472562232560, SHARED_UPGRADABLE, TRANSACTION, GRANTED, sql_parse.cc:6142, 77, 50
TABLESPACE, , database/sample_locks, , 281472561024336, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, lock.cc:807, 77, 50
SCHEMA, database, , , 281472562291616, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:1312, 77, 50
TABLE, database, chapters, , 281472561037104, SHARED_UPGRADABLE, STATEMENT, GRANTED, sql_table.cc:1304, 77, 50
TABLE, database, #sql-1_28, , 281472562297456, EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:17126, 77, 50
TABLE, database, sample_locks, , 281472562226160, EXCLUSIVE, TRANSACTION, PENDING, mdl.cc:3762, 77, 50
TABLE, performance_schema, metadata_locks, , 281472296851328, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 71, 283
TABLE, database, sample_locks, , 281473151126160, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 75, 211
TABLE, database, chapters, , 281473151502592, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 211


■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215700128:62:1075:281473097747464, 3693, 75, 211, database, chapters, , , , 281473097747464, TABLE, IS, GRANTED, 
INNODB, 281473215700128:62:1083:281473097747376, 3693, 75, 211, database, sample_locks, , , , 281473097747376, TABLE, IX, GRANTED, 
INNODB, 281473215700128:62:9:6:231:281473097744576, 3693, 75, 211, database, chapters, , , PRIMARY, 281473097744576, RECORD, S,REC_NOT_GAP, GRANTED, 1

*/

もりた

カラム追加


-- カラム追加
ALTER TABLE sample_locks
ADD COLUMN phone_number VARCHAR(15);


/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, sample_locks, , 281473151502592, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 75, 219
TABLE, database, chapters, , 281473147654240, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 219
GLOBAL, , , , 281472362471408, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_base.cc:5476, 78, 15
BACKUP LOCK, , , , 281472359621936, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5483, 78, 15
SCHEMA, database, , , 281472359617088, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5463, 78, 15
TABLE, database, sample_locks, , 281472359637200, SHARED_UPGRADABLE, TRANSACTION, GRANTED, sql_parse.cc:6142, 78, 15
TABLESPACE, , database/sample_locks, , 281472362409152, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, lock.cc:807, 78, 15
SCHEMA, database, , , 281472362367824, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:1312, 78, 15
TABLE, database, chapters, , 281472359665536, SHARED_UPGRADABLE, STATEMENT, GRANTED, sql_table.cc:1304, 78, 15
TABLE, database, #sql-1_29, , 281472362442256, EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:17126, 78, 15
TABLE, database, sample_locks, , 281472360716240, EXCLUSIVE, TRANSACTION, PENDING, mdl.cc:3762, 78, 16
TABLE, performance_schema, metadata_locks, , 281472296802176, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 71, 298


■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215700128:64:1075:281473097747464, 3706, 75, 219, database, chapters, , , , 281473097747464, TABLE, IS, GRANTED, 
INNODB, 281473215700128:64:1083:281473097747376, 3706, 75, 219, database, sample_locks, , , , 281473097747376, TABLE, IX, GRANTED, 
INNODB, 281473215700128:64:9:6:231:281473097744576, 3706, 75, 219, database, chapters, , , PRIMARY, 281473097744576, RECORD, S,REC_NOT_GAP, GRANTED, 1

もりた

カラム削除

-- カラム削除
ALTER TABLE sample_locks
DROP COLUMN phone_number;


/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, chapters, , 281472359621936, SHARED_UPGRADABLE, STATEMENT, GRANTED, sql_table.cc:1304, 78, 47
TABLE, database, #sql-1_29, , 281472362575872, EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:17126, 78, 47
TABLE, database, sample_locks, , 281472362675584, EXCLUSIVE, TRANSACTION, PENDING, mdl.cc:3762, 78, 48
TABLE, performance_schema, metadata_locks, , 281472296802176, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 71, 306
TABLE, database, sample_locks, , 281473151126160, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 75, 226
TABLE, database, chapters, , 281473146403536, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 226
GLOBAL, , , , 281472362442256, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_base.cc:5476, 78, 47
BACKUP LOCK, , , , 281472360729088, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5483, 78, 47
SCHEMA, database, , , 281472360674688, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5463, 78, 47
TABLE, database, sample_locks, , 281472360365424, SHARED_UPGRADABLE, TRANSACTION, GRANTED, sql_parse.cc:6142, 78, 47
TABLESPACE, , database/sample_locks, , 281472359665536, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, lock.cc:807, 78, 47
SCHEMA, database, , , 281472360713120, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:1312, 78, 47


■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215700128:66:1075:281473097747464, 3718, 75, 226, database, chapters, , , , 281473097747464, TABLE, IS, GRANTED, 
INNODB, 281473215700128:66:1083:281473097747376, 3718, 75, 226, database, sample_locks, , , , 281473097747376, TABLE, IX, GRANTED, 
INNODB, 281473215700128:66:9:6:231:281473097744576, 3718, 75, 226, database, chapters, , , PRIMARY, 281473097744576, RECORD, S,REC_NOT_GAP, GRANTED, 1

*/

もりた

テーブル削除


-- テーブル削除
DROP TABLE sample_locks;


/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, sample_locks, , 281473151010752, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 75, 233
TABLE, database, chapters, , 281473147651728, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 75, 233
GLOBAL, , , , 281472360436864, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_base.cc:5476, 78, 79
BACKUP LOCK, , , , 281472362471408, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5483, 78, 79
SCHEMA, database, , , 281472360674688, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5463, 78, 79
TABLE, database, sample_locks, , 281472362409152, EXCLUSIVE, TRANSACTION, PENDING, sql_parse.cc:6142, 78, 79
TABLE, performance_schema, metadata_locks, , 281472296802176, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 71, 314


■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281473215700128:68:1075:281473097747464, 3729, 75, 235, database, chapters, , , , 281473097747464, TABLE, IS, GRANTED, 
INNODB, 281473215700128:68:1083:281473097747376, 3729, 75, 235, database, sample_locks, , , , 281473097747376, TABLE, IX, GRANTED, 
INNODB, 281473215700128:68:9:6:231:281473097744576, 3729, 75, 235, database, chapters, , , PRIMARY, 281473097744576, RECORD, S,REC_NOT_GAP, GRANTED, 1

*/

もりた

 もりた的なまとめ
DDLはロックを取らずMDLだけをとっている
また、トランザクションも貼れない
SELECTもロックを取らない


 別途調べること
インテンションロック？　みたいなやつ
BUCKUP LOCKとか

もりた

補遺 - DDLのINSTANCE指定

INSTANCEは処理速度が上がるってだけで、結局占有ロックは取っている。
オンラインDDLは占有ロックをとる取らないというより、処理のメイン部分でロックを取らないようにして、そこで並行DDLを受け付けることができる機能なんだな

-- カラム追加
ALTER TABLE sample_locks
ADD COLUMN phone_number_2 VARCHAR(15), algorithm=instant;


/*
■MDL
# OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID
TABLE, database, sample_locks, , 281471889812400, SHARED_WRITE, TRANSACTION, GRANTED, sql_parse.cc:6142, 47, 60
TABLE, database, chapters, , 281471889950272, SHARED_READ, TRANSACTION, GRANTED, sql_base.cc:4654, 47, 60

GLOBAL, , , , 281471823351280, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_base.cc:5476, 51, 16
BACKUP LOCK, , , , 281471825265296, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5483, 51, 16
SCHEMA, database, , , 281471824874656, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, sql_base.cc:5463, 51, 16
TABLE, database, sample_locks, , 281471823703072, SHARED_UPGRADABLE, TRANSACTION, GRANTED, sql_parse.cc:6142, 51, 16
TABLESPACE, , database/sample_locks, , 281471824898288, INTENTION_EXCLUSIVE, TRANSACTION, GRANTED, lock.cc:807, 51, 16
SCHEMA, database, , , 281471822795936, INTENTION_EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:1312, 51, 16
TABLE, database, chapters, , 281471822659728, SHARED_UPGRADABLE, STATEMENT, GRANTED, sql_table.cc:1304, 51, 16
TABLE, database, #sql-1_e, , 281471824920944, EXCLUSIVE, STATEMENT, GRANTED, sql_table.cc:17126, 51, 16
TABLE, database, sample_locks, , 281471825148512, EXCLUSIVE, TRANSACTION, PENDING, mdl.cc:3762, 51, 17

TABLE, performance_schema, metadata_locks, , 281472158482880, SHARED_READ, TRANSACTION, GRANTED, sql_parse.cc:6142, 49, 43


■DL
# ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
INNODB, 281472709296936:32:1085:281472627966928, 4133, 47, 60, database, sample_locks, , , , 281472627966928, TABLE, IX, GRANTED, 
INNODB, 281472709296936:32:1075:281472627967016, 4133, 47, 60, database, chapters, , , , 281472627967016, TABLE, IS, GRANTED, 
INNODB, 281472709296936:32:9:6:231:281472627964016, 4133, 47, 60, database, chapters, , , PRIMARY, 281472627964016, RECORD, S,REC_NOT_GAP, GRANTED, 1


*/

もりた

 追加調査 - INPLACEで並行DQLを許可しないものはあるか？
 どう調査するか調査対象
INPLACEがはいになっていて、DML許可がいいえになっているDDL

期待値
DQLを許可しない、読み込みすらできないDDLがあるのではないか？


 結果Now Loading...

このスクラップは2025/02/03にクローズされました