Zenn
Closed14

Railsでadd_indexをした時のロックの挙動

Rails
MySQL 8
ピン留めされたアイテム
たすくんたすくん

まとめ

add_indexについて

  • オプションなしで add_index すると、CREATE INDEX index_suppliers_on_name ON suppliers(name) みたいなSQLを発行する
  • 基本的にセカンダリインデックスの作成または追加の場合は、ALGORITHM = INPLACE, LOCK=NONE と同等のオプションになることが多い

ALGORITHMとLOCK

  • ALGORITHMは以下の3種類あり、オプションを指定しないと、上から順番に使用できるものから選ばれる
    • INSTANT
    • INPLACE
    • COPY
  • LOCKは以下の3種類あり、オプションを指定しないと、上から順番に使用できるものから選ばれる（要検証）
    • NONE
    • SHARED
    • EXCLUSIVE

メタデータロックについて

  • MySQLのロックの種類には、InnoDBロックとメタデータロック(MDL)の2種類がある
  • MDLには共有MDLと排他MDLの2つがある
    • 共有MDL: SELECT, INSERT, UPDATE, DELETE
    • 排他MDL: ALTERなどのDDL
  • MDLがかかる期間はトランザクションの終了まで
    • 明示的にトランザクションをはらないと一瞬でMDLが取得~解放される
    • 明示的にトランザクションをはるとロックの期間を伸ばせる
      • ちなみに ALTER などは実行した時点で暗黙的に COMMIT されるため、ロックの期間を伸ばすことはできない
  • MDLの取得状況は performance_schema.metadata_locks で確認できる

INPLACEについて

  • DDL操作で ALGORITHM = INPLACE, LOCK=NONE をつけると、テーブルに対してInnoDBロックを取らないので、同時DML(SELECT, INSERT, UPDATE, DELETE) が実行できるようになる
  • DDL操作の開始と終了時に排他MDLが取得されるため、そのタイミングで他のMDLが取られている場合はDDLの操作は待たされる

注意点

  • ALGORITHM = INPLACE, LOCK=NONE を付けたからといって安心してマイグレーションできるとは限らない
    • ロック解除待ちになれば、結局後続のSELECTやトランザクションもロック解除待ちになってしまう
    • MDLを頻繁に取るようなテーブルに対してDDLを実行すると、クエリが詰まって障害になる可能性が高い
  • DDLを使う時は、対象のテーブルに対してMDLが取られている頻度やタイミングを見て、慎重に検討する必要がある
たすくんたすくん

ALGORITHM としては INPLACE が使用される

特徴

  • テーブル全体のコピーが必要ない
  • 実行中に DML (SELECT, INSERT, UPDATE, DELETE) が実行できる
たすくんたすくん

MySQL を使ってても algorithm オプションを指定することはできた
add_index :posts, %i[user_id], algorithm: :inplace

たすくんたすくん

LOCK オプションを付けていなくても ALGORITHM = INPLACE を付けていれば、ロックは取られなそうだが、正確な挙動は不明

たすくんたすくん

DDLの制限実行について

  • INPLACEのDDL終了する前に、テーブルのメタデータロックを保持するトランザクションがコミットまたはロールバックされるまで待機する必要がある
  • DDLでは、実行フェーズ中にテーブルに対する排他的メタデータロックが短時間必要になる場合がある
  • テーブル定義の更新時には常にDDLの終了時に排他的メタデータロックが必要
  • 待機することにより、DDLがタイムアウトすることがある

https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-limitations.html

このスクラップは5ヶ月前にクローズされました