外部キー制約を持つテーブルに対する DDL の MDL を観察してみた

Transcript

1. 外部キー制約を持つテーブルに対する DDL の MDL を観察してみた MySQL アンカンファレンス #9, 2025/07/08 yoshikai
2. こんばんは！初めての発表です • yoshikai • 新卒2年目 • DBA 歴ちょうど1年 • 昨年の今頃 MySQL 徹底入門 第4版 を 読み始めたくらい • 関連リンク • X • ブログ • （この内容で1つ目を書きました）
3. なんで観察しようと思った？ • “外部キー制約を持っているテーブルに SELECT するとそのテーブルの親 テーブルにもメタデータロック (MDL) を置く” と思っていた • 参考：https://yoku0825.blogspot.com/2020/08/mysql-80-vs-vs-alter-table.html • 子テーブル A に SELECT が走っているときに新しく子テーブル B を 作成しようとすると親テーブルの MDL 待ちになると考えていた SELECT * FROM 子A CREATE TABLE 子B 親
4. なんで観察しようと思った？ • CREATE TABLE 子B すると作成できた！？ • 思っていた挙動と違う、、？ SELECT * FROM 子A CREATE TABLE 子B 親
5. メタデータロック (MDL) とは • 主にテーブル定義を保護するために置かれるデータに依存しないロック • テーブルの実データサイズなどには影響を受けない • 共有ロックと排他ロックあり • トランザクション中にテーブルの構造が変更されることを防ぐ
6. 観察してみたこと • CREATE TABLE • 子テーブル B を作成 • 子テーブル A に SELECT が走っている場合 • 子テーブル A に INSERT が走っている場合 • ALTER TABLE • 親テーブルに ALTER TABLE • 子テーブル A に SELECT が走っている場合 • 子テーブル A に INSERT が走っている場合 • 子テーブル A に ALTER TABLE • 親テーブルに SELECT が走っている場合 • 親テーブルに INSERT が走っている場合 子A 子B 親
7. 観察準備 p: 親 c: 子A
8. 観察してみたこと • CREATE TABLE • 子テーブル B を作成 • 子テーブル A に SELECT が走っている場合 • 子テーブル A に INSERT が走っている場合 • ALTER TABLE • 親テーブルに ALTER TABLE • 子テーブル A に SELECT が走っている場合 • 子テーブル A に INSERT が走っている場合 • 子テーブル A に ALTER TABLE • 親テーブルに SELECT が走っている場合 • 親テーブルに INSERT が走っている場合 子A 子B 親
9. CREATE TABLE – 子テーブル B を作成する (1) • 子テーブル A に SELECT が走っている場合 • 子テーブル A の SHARED_READ がとられる • CREATE TABLE 子B は成功する SHARED_READ SELECT * FROM 子A CREATE TABLE 子B 親
10. CREATE TABLE – 子テーブル B を作成する (1) • 子テーブル A に SELECT が走っている場合 • 子テーブル A の SHARED_READ がとられる • CREATE TABLE 子B は成功する SHARED_READ Succeeded! SELECT * FROM 子A CREATE TABLE 子B 親
11. CREATE TABLE – 子テーブル B を作成する (2) • 子テーブル A に INSERT が走っている場合 • 子テーブル A の SHARED_WRITE, 親テーブルの SHARED_READ がとられる • CREATE TABLE 子B の親テーブルに対する EXCLUSIVE が待たされる SHARED_WRITE INSERT INTO 子A … SHARED_READ CREATE TABLE 子B 親
12. CREATE TABLE – 子テーブル B を作成する (2) • 子テーブル A に INSERT が走っている場合 • 子テーブル A の SHARED_WRITE, 親テーブルの SHARED_READ がとられる • CREATE TABLE 子B の親テーブルに対する EXCLUSIVE が待たされる SHARED_WRITE PENDING… INSERT INTO 子A … SHARED_READ EXCLUSIVE Lock wait timeout exceeded CREATE TABLE 子B 親
13. 観察してみたこと • CREATE TABLE • 子テーブル B を作成 • 子テーブル A に SELECT が走っている場合 • 子テーブル A に INSERT が走っている場合 • ALTER TABLE • 親テーブルに ALTER TABLE • 子テーブル A に SELECT が走っている場合 • 子テーブル A に INSERT が走っている場合 • 子テーブル A に ALTER TABLE • 親テーブルに SELECT が走っている場合 • 親テーブルに INSERT が走っている場合 子A 子B 親
14. ALTER TABLE - 親テーブルに ALTER TABLE をかける (1) • 子テーブル A に SELECT が走っている場合 • 子テーブル A の SHARED_READ がとられる • ALTER TALBE 親 の 子テーブル A への EXCLUSIVE が待たされる SHARED_READ SELECT * FROM 子A 親 ALTER TABLE 親 …
15. ALTER TABLE - 親テーブルに ALTER TABLE をかける (1) • 子テーブル A に SELECT が走っている場合 • 子テーブル A の SHARED_READ がとられる • ALTER TALBE 親 の 子テーブル A への EXCLUSIVE が待たされる SHARED_READ SELECT * FROM 子A SHARED_UPGRADABLE EXCLUSIVE PENDING… ALTER TABLE 親 … Lock wait timeout exceeded 親
16. ALTER TABLE - 親テーブルに ALTER TABLE をかける (2) • 子テーブル A に INSERT が走っている場合 • 子テーブル A の SHARED_WRITE, 親テーブルの SHARED_READ がとられる • ALTER TALBE 親 の 親テーブルへの EXCLUSIVE が待たされる SHARED_WRITE SHARED_READ INSERT INTO 子A … 親 ALTER TABLE 親 …
17. ALTER TABLE - 親テーブルに ALTER TABLE をかける (2) • 子テーブル A に INSERT が走っている場合 • 子テーブル A の SHARED_WRITE, 親テーブルの SHARED_READ がとられる • ALTER TALBE 親 の 親テーブルへの EXCLUSIVE が待たされる SHARED_WRITE EXCLUSIVE PENDING… ALTER TABLE 親 … Lock wait timeout exceeded SHARED_READ INSERT INTO 子A … 親
18. 観察してみたこと • CREATE TABLE • 子テーブル B を作成 • 子テーブル A に SELECT が走っている場合 • 子テーブル A に INSERT が走っている場合 • ALTER TABLE • 親テーブルに ALTER TABLE • 子テーブル A に SELECT が走っている場合 • 子テーブル A に INSERT が走っている場合 • 子テーブル A に ALTER TABLE • 親テーブルに SELECT が走っている場合 • 親テーブルに INSERT が走っている場合 子A 子B 親
19. ALTER TABLE - 子テーブル A に ALTER TABLE をかける (1) • 親テーブルに SELECT が走っている場合 • 親テーブルの SHARED_READ がとられる • ALTER TABLE 子A は成功する SHARED_READ SELECT * FROM 親 ALTER TABLE 子A …
20. ALTER TABLE - 子テーブル A に ALTER TABLE をかける (1) • 親テーブルに SELECT が走っている場合 • 親テーブルの SHARED_READ がとられる • ALTER TABLE 子A は成功する SHARED_READ SELECT * FROM 親 ALTER TABLE 子A … Succeeded!
21. ALTER TABLE - 子テーブル A に ALTER TABLE をかける (2) • 親テーブルに INSERT が走っている場合 • 親テーブルの SHARED_WRITE がとられる • ALTER TABLE 子A は成功する SHARED_WRITE INSERT INTO 親 … ALTER TABLE 子A …
22. ALTER TABLE - 子テーブル A に ALTER TABLE をかける (2) • 親テーブルに INSERT が走っている場合 • 親テーブルの SHARED_WRITE がとられる • ALTER TABLE 子A は成功する SHARED_WRITE ALTER TABLE 子A … Succeeded! INSERT INTO 親 …
23. MDL 観察まとめ SHARED_READ SELECT * FROM 子A SHARED_WRITE SHARED_READ INSERT INTO 子A … SHARED_READ SELECT * FROM 親 SHARED_WRITE INSERT INTO 親 … 子テーブル A に SELECT/INSERT 親テーブルに SELECT/INSERT
24. DDL の実行で気をつけること（外部キー制約あり） • 親/子A テーブルにロングトランザクションがないか？ SELECT * FROM 親 SELECT * FROM 子A INSERT INTO 親 INSERT INTO 子A CREATE TABLE 子B チェック不要 ALTER TABLE 親 ALTER TABLE 子A チェック不要 チェック不要 子A 子B 親