これはなに

業務上で得た知識をメモるだけ
他人が読む前提で書いていません

背景

大量レコードを新規テーブルにselect-insertする際、既存処理へ影響を出したくない
- 新規テーブルへのinsertだが、参照するのは既存テーブルなので、テーブルロックを回避したい
影響を本当に出さないなら夜中作業になるが、遅くまで営業さんが操作している
- ユーザが多くシステム停止判断は厳しい
- というか早く帰りたいから日中に実施したいよね

トランザクション分離レベル

READ UNCOMMITTED を選択
- ただしダーティリード、ファジーリード、ファントムリード、いずれも発生する
- 事前に確認用のSQLを作成し、insert実施後に漏れが無いか確認を行う必要がある

理解できずに調べたこと

READ UNCOMMITTEDは読み取り専用の操作に対してはロックを取得しない
- 他トランザクションによる未コミットの操作があっても読み取りがブロックされない
- つまり、他トランザクションが更新をしていても構わず読み取ることができる
ロックの種類をちゃんと認識していなかった
- 共有ロック
  - データを読み取る際に取得されるロックで、他のトランザクションがそのデータに対して書き込みを行うことを防ぐ
- 排他ロック
  - データを更新または削除する際に取得されるロックで、他のトランザクションがそのデータに対して読み取りや書き込みを行うことを防ぐ
トランザクション分離レベルの設定はスコープに注意
- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  - 接続しているセッション全体にトランザクション分離レベルが適用される
- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  - 次に実行するトランザクションのみトランザクション分離レベルが適用される
- ローカル環境で再現する際に再現できずに苦戦したためメモ

トランザクション分離レベル：設定なし

トランザクションを貼って純粋にselect-insertを流す
実行環境はMySQLなのでデフォルトの分離レベルは REPEATABLE READ

begin;

insert into fruits (food_id, apply_at)
select
    id,
    now()
from
    foods
where
    kind = 3
;

ロックの状態を確認

SHOW ENGINE INNODB STATUS;
-- 339553 row lock(s)

別トランザクションで対象のレコードをupdate

update foods
   set price = 500
 where id = 123;

テーブルロックされているためupdateが終わらないことを確認

トランザクション分離レベル：設定あり（READ UNCOMMITTED）

トランザクション分離レベルを設定した上でselect-insertを流す

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
begin;

insert into fruits (food_id, apply_at)
select
    id,
    now()
from
    foods
where
    kind = 3
;

ロックの状態を確認

SHOW ENGINE INNODB STATUS;
-- 10199 row lock(s)

別トランザクションで対象のレコードをupdate

update foods
   set price = 500
 where id = 123;

更新できた！

まとめ

READ UNCOMMITTED
デフォルトの分離レベル（MySQLの場合は REPEATABLE READ）
- SELECTクエリは共有ロックを取得する
- 他のトランザクションが同じ行に対して排他ロックを取得しようとすると競合が発生する
トランザクション分離レベルがデフォルトのまま
- SELECTクエリが foods テーブルの行に対して共有ロックを取得
- UPDATEクエリが foods テーブルの行に対して排他ロック取得を試みるが、1つ目のトランザクションが共有ロックを保持しているため、競合が発生し、ロック待ち状態になる
トランザクション分離レベルをREAD UNCOMMITTEDにする
- SELECTクエリが共有ロックを取得せず、他のトランザクションが行っている変更をそのまま読み取る
- UPDATEクエリが foods テーブルの行に対して排他ロック取得を試みた際、1つ目のトランザクションが共有ロックを取得していないため、競合が発生せず、ロック待ち状態にならない
通常はSELECT対象が多い状態で共有ロックを取りに行きテーブルロックが発生するが、READ UNCOMMITTEDは共有ロックを取得しないため、INSERT対象だけロックを取りに行くことでロック件数が大幅に減った、ということ