MySQLのB-Treeインデックスにおけるロック競合の仕組みと、マルチテナントアプリケーションでの実際の影響について。

インデックスロックの発生メカニズム

大量DELETE実行時、以下の手順でインデックスが更新される:

1. WHERE句で指定された行のインデックスエントリを削除
2. B-Treeのノードを再構成
3. インデックスページに排他ロックを取得

競合が発生する条件

同じインデックスページに存在する場合のみ競合:

• InnoDBのページサイズ: 16KB (デフォルト)
• varchar(255)のインデックスエントリ: 約40バイト
• 1ページあたり約400エントリが共存可能

例: tenant_idインデックスで、作成時期が近い約400個のテナントが同じページに格納される。

マルチテナント環境での影響

tenant_A の10万件削除中に tenant_B が挿入を実行:

• 同じインデックスページの場合: 数ミリ秒の待機が発生
• 異なるインデックスページの場合: 競合なし、並列実行可能

ロック保持時間:

• 削除はページごとにバッチ処理される
• 1ページあたりのロック時間: 数マイクロ秒〜数ミリ秒
• 実測での待機時間: 通常0〜5ミリ秒、高負荷時で10〜50ミリ秒

重要な認識

1. 行レベルロック: WHERE句で絞り込まれた行のみがロックされる
2. tenant_id による分離: 他テナントのデータへの影響は実質的に最小限
3. インデックスページレベルの競合: 理論上は発生するが、実用上は無視できるレベル
4. DDL操作: テーブルレベルのメタデータロックにより、ALTER TABLEなどは待機するが、通常のCRUD操作には影響なし

結論

tenant_id でのWHERE句による大量削除(10万件程度)は、他テナントへの実質的な影響はほぼゼロ。インデックスロックによる数ミリ秒の待機は、ユーザー体感では認識できないレベル。