MySQL で delete 中に select を実行した場合の挙動を確認

MySQL (innodb) で delete 中に select を実行した際に、select がロックされるかを確認したメモ。
以下の例で、select はロックされない、select ... for update はレコード単位でロックされることが確認できました。

以下のテーブルを作成して、レコードを登録しておきます。

create table test_lock (
       id    varchar(100),
       r     int,
       primary key(id),
       index (r)
);

■実験1
以下の delete＋10秒のスリープ のトランザクションを実行中に select を行い、
delete のトランザクション完了する前に select の実行結果が得られるか調べます。

begin;
  delete from test_lock where r >= 900;
  select sleep(10);
commit;

begin;
  select * from test_lock where r >= 900 limit 10;
commit;

実行してみると、delete によってロックされずに select の結果が得られることが確認できました。

■実験2
一方、以下の select ... for update の場合には、delete のトランザクション終了後に
select の実行が完了しました。

begin;
  select * from test_lock where r >= 900 limit 10 for update;
commit;

■実験3
上記の delete、select はともに r >= 900 の条件で同一レコードを参照していますが、
select の条件を変更して delete の対象と重ならないようにすると、delete によってロックされずに
select の結果が得られます。

begin;
  select * from test_lock where r <= 100 limit 10 for update;
commit;