これはなに
- 業務上で得た知識をメモるだけ
- 他人が読む前提で書いていません
背景
- 大量レコードを新規テーブルにselect-insertする際、既存処理へ影響を出したくない
- 新規テーブルへのinsertだが、参照するのは既存テーブルなので、テーブルロックを回避したい
- 影響を本当に出さないなら夜中作業になるが、遅くまで営業さんが操作している
- ユーザが多くシステム停止判断は厳しい
- というか早く帰りたいから日中に実施したいよね
トランザクション分離レベル
-
READ UNCOMMITTED を選択
- ただしダーティリード、ファジーリード、ファントムリード、いずれも発生する
- 事前に確認用のSQLを作成し、insert実施後に漏れが無いか確認を行う必要がある
理解できずに調べたこと
- READ UNCOMMITTEDは読み取り専用の操作に対してはロックを取得しない
- 他トランザクションによる未コミットの操作があっても読み取りがブロックされない
- つまり、他トランザクションが更新をしていても構わず読み取ることができる
- ロックの種類をちゃんと認識していなかった
- 共有ロック
- データを読み取る際に取得されるロックで、他のトランザクションがそのデータに対して書き込みを行うことを防ぐ
- 排他ロック
- データを更新または削除する際に取得されるロックで、他のトランザクションがそのデータに対して読み取りや書き込みを行うことを防ぐ
- 共有ロック
- トランザクション分離レベルの設定はスコープに注意
- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
- 接続しているセッション全体にトランザクション分離レベルが適用される
- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
- 次に実行するトランザクションのみトランザクション分離レベルが適用される
- ローカル環境で再現する際に再現できずに苦戦したためメモ
- SET SESSION 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
- 他のトランザクションがコミットしていない変更を読み取ることができる
- つまり読み取り専用のクエリに対してロックの競合を減らすことができる
- SELECTクエリは共有ロックを取得しない
- デフォルトの分離レベル（MySQLの場合は REPEATABLE READ）
- SELECTクエリは共有ロックを取得する
- 他のトランザクションが同じ行に対して排他ロックを取得しようとすると競合が発生する
- トランザクション分離レベルがデフォルトのまま
- SELECTクエリが foods テーブルの行に対して共有ロックを取得
- UPDATEクエリが foods テーブルの行に対して排他ロック取得を試みるが、1つ目のトランザクションが共有ロックを保持しているため、競合が発生し、ロック待ち状態になる
- トランザクション分離レベルをREAD UNCOMMITTEDにする
- SELECTクエリが共有ロックを取得せず、他のトランザクションが行っている変更をそのまま読み取る
- UPDATEクエリが foods テーブルの行に対して排他ロック取得を試みた際、1つ目のトランザクションが共有ロックを取得していないため、競合が発生せず、ロック待ち状態にならない
- 通常はSELECT対象が多い状態で共有ロックを取りに行きテーブルロックが発生するが、READ UNCOMMITTEDは共有ロックを取得しないため、INSERT対象だけロックを取りに行くことでロック件数が大幅に減った、ということ