どうも、今日も今日とて野毛で飲みながらブログを書いている@0kawaraです。
今日は、普段あまり意識してこなかったMySQLのInnoDBでのロックの振る舞いについて色々実験してみました。（もちろん、きっかは自分がドツボにはまったから）
ちゃんと理解するためには「共有・排他的ロックとは」って話や、「行ロックってつまりインデックスレコードロックだよね」などの話とか理解する必要があるんですが、それは
をちゃんと一読してもらえれば十分かと思います。
（というか、これが問題なく読めて理解できる人はこの記事読む必要ない….）
以下は上のドキュメント含め関連する記事などを読んで自分でInnoDBの行ロック周りについて、というかSELECT FOR UPDATEについて理解を深めるために手元で実験したことのまとめです。
技術的にちゃんとした理解を深めたい人は最後にまとめた参考サイトも是非ご覧になってください。
これを書いてる人はまだまだ理解が完璧ではないので怪しい部分がきっとあるんですが、ご指摘等は@okwraまで宜しくお願いします。
目次
- SELECT FOR UPDATEとLOCKING READ
- 条件なしでロックをかける
- 主キーの条件つきで行のロックをかける
- 制約のないキーのみの条件で行のロックをかける
- 制約のあるカラムとないカラムでの複数条件でロックをかける
- 参考
さて、今回はこちらのサイトを参考に、こんなデータを準備しました。
mysql> desc zuno_o; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | -- 主キー | rank | int(11) | YES | | NULL | | | name | varchar(10) | NO | UNI | NULL | | -- ユニークキー | grade | int(11) | YES | | NULL | | | total | int(11) | YES | | NULL | | | ave | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> select * from zuno_o; +----+------+--------------------------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+--------------------------------+-------+-------+------+ | 1 | 11 | 与田祐希 | 3 | 56 | 11 | | 2 | 20 | 中村麗乃 | 3 | 16 | 3 | | 3 | 1 | 久保史緒里 | 3 | 70 | 14 | | 4 | 7 | 伊藤かりん | 2 | 62 | 12 | | 5 | 8 | 伊藤理々杏 | 3 | 60 | 12 | | 6 | 3 | 佐々木琴子 | 2 | 66 | 13 | | 7 | 5 | 佐藤楓 | 3 | 64 | 13 | | 8 | 13 | 吉田綾乃クリスティー | 3 | 50 | 10 | | 9 | 12 | 向井葉月 | 3 | 54 | 11 | | 10 | 21 | 和田まあや | 2 | 12 | 2 | | 11 | 18 | 堀未央奈 | 2 | 28 | 6 | | 12 | 19 | 大園桃子 | 3 | 20 | 4 | | 13 | 5 | 山下美月 | 3 | 64 | 13 | | 14 | 1 | 山崎怜奈 | 2 | 70 | 14 | | 15 | 16 | 岩本蓮加 | 3 | 36 | 7 | | 16 | 8 | 新内眞衣 | 2 | 60 | 12 | | 17 | 15 | 梅澤美波 | 3 | 42 | 8 | | 18 | 17 | 渡辺みり愛 | 2 | 30 | 6 | | 19 | 10 | 相楽伊織 | 2 | 58 | 12 | | 20 | 3 | 鈴木絢音 | 2 | 66 | 13 | | 21 | 14 | 阪口珠美 | 3 | 45 | 9 | +----+------+--------------------------------+-------+-------+------+ 21 rows in set (0.00 sec)
ところでこのデータ間違えてます。
１億総乃木オタ時代を生きる皆さんならお気づきかと思います。
そうです。この人の情報が間違っています。
実は、今回参考にさせていただいた元サイトのデータには何期生というカラム(grade)自体なかったので、勝手に僕が追加しました。その際、３期生のレコードをUPDATEしたのち、それ以外(gradeがNULL)のレコードを２期生としてUPDATEしたことが原因です。
これでは元祖頭NO王に失礼(?)です。
ということで、まずはこの情報の修正と、そのついでにSELECT … FOR UPDATE文の基本的なユースケースを確認したいと思います。
SELECT FOR UPDATEとLOCKING READ
ここからA画面とB画面に分けて実験・検証していきます。
A) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update zuno_o set grade = 1 where name LIKE '和田まあや'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
この時点で「和田まあや」の行ロックがかかっています。
さてこの状態で、B画面からまあやの情報を見てみます。
B) mysql> select * from zuno_o where name LIKE '和田まあや'; -- Aでコミットされてないので2期生のまま +----+------+-----------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+-----------------+-------+-------+------+ | 10 | 21 | 和田まあや | 2 | 12 | 2 | +----+------+-----------------+-------+-------+------+ 1 row in set (0.00 sec) mysql> select * from zuno_o where grade = 1; -- こちらも当然まだ0人 Empty set (0.00 sec) mysql> select * from zuno_o where name LIKE '和田まあや' for update; ERROR 1317 (70100): Query execution was interrupted
注目すべきは最後のSELECT FOR UPDATEです。別のトランザクションで和田まあやの行がロックされているため、結果が返って来ません。上では強制的に中断しましたが、待機状態にしたままでAの更新情報をコミットしてみます。
A) mysql> commit; Query OK, 0 rows affected (0.00 sec) ------ B) mysql> select * from zuno_o where name LIKE '和田まあや' for update; +----+------+-----------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+-----------------+-------+-------+------+ | 10 | 21 | 和田まあや | 1 | 12 | 2 | +----+------+-----------------+-------+-------+------+ 1 row in set (2.42 sec)
一列だと感覚が伝わりにくいですが、BのSQLを叩いた状態で、Aのコミットを行うとすぐに結果が返って来ます。（当然、Bはまだトランザクション内。）そしてちゃんとまあやが一期生となって情報が更新されています。これをLOCKING READと言うそうです。
余談ですが最初、「何がLOCKING READなんだ？」と思ったんですが、これってつまり「ロックを取りに行くぞ！」READって認識でいいんですかね。別トランザクションからの更新を待っている...みたいな印象だったんですが、あくまでロックを取りに行くのを待機している、ここで言えば 「あっち(A)の更新が終わった瞬間にこっち(B)でまあやの行のロックを取りに行ったる！」 ってことみたいです。（うまく伝わってるか？）
では、Bをコミットする前にSELECT FOR UPDATEではない通常のSELECT文のSQL（こいつをNON-LOCKING READと呼ぶそうです)では結果はどうなるでしょうか。
B) -- (上の状態から続けて) mysql> select * from zuno_o where name LIKE '和田まあや'; +----+------+-----------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+-----------------+-------+-------+------+ | 10 | 21 | 和田まあや | 2 | 12 | 2 | +----+------+-----------------+-------+-------+------+ 1 row in set (0.00 sec)
予想通り、（Aのコミットに影響されず）二期生という誤った情報のままです。
このLOCKING READをうまく使うことでいわゆるロストアップデートを避けることができるわけです。
※ロストアップデートとは...
ただし、気をつけなければいけない点があります。それがファントムリード！
やばい、めっちゃかっこいい名前。（そこじゃない）
スター・ウォーズ エピソード1 ファントム・メナス (ディズニーストーリーブック)
- 作者:パトリシア・シー・リード
- 出版社/メーカー: 講談社
- 発売日: 2015/03/27
- メディア: 単行本
（ファントム違い）
上のBのトランザクションの中をまとめるとこうです。
B) -- NON-LOCKING READ mysql> select * from zuno_o where name LIKE '和田まあや'; +----+------+-----------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+-----------------+-------+-------+------+ | 10 | 21 | 和田まあや | 2 | 12 | 2 | +----+------+-----------------+-------+-------+------+ 1 row in set (0.00 sec) mysql> select * from zuno_o where grade = 1; Empty set (0.00 sec) ------ -- LOCKING READ mysql> select * from zuno_o where name LIKE '和田まあや' for update; +----+------+-----------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+-----------------+-------+-------+------+ | 10 | 21 | 和田まあや | 1 | 12 | 2 | +----+------+-----------------+-------+-------+------+ 1 row in set (2.42 sec) mysql> select * from zuno_o where grade = 1 for update; +----+------+-----------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+-----------------+-------+-------+------+ | 10 | 21 | 和田まあや | 1 | 12 | 2 | +----+------+-----------------+-------+-------+------+ 1 row in set (0.00 sec)
重要なのはこれが同一トランザクション内と言うこと。
つまり、同一トランザクション内で取得される結果が一致しない場合があるのです。
(これを「完全な一貫性読み取りではない」とか言うらしい。)
ファントムリードとは他のトランザクションのセッションで追加されたデータがコミット前であっても取得できてしまう、諸刃の剣（？）と言うわけです。
今回はめっちゃわかりやすいケースで検証した（SQLベタ打ちだし）から良いものの、もっと複雑で実戦的なケース（トランザクションがネストしてるとか）だとしたらこの罠に陥る確率がグッと上がることでしょう。
さて、ここまでだいぶ単純な？ケースでSELECT FOR UPDATE文による行ロックについて確認して来ました。では、いくつかのケースでこの行ロックが振る舞いを変えることを今度は確認していきます。
条件なしでロックをかける
A) mysql > begin; B) mysql > begin;
まずはじめにA画面で条件なしで行をロックしてみます、つまり実質これは テーブルロックと同じ振る舞い になります（裏のロジックは違います）。
A) mysql> select * from zuno_o for update; +----+------+--------------------------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+--------------------------------+-------+-------+------+ | 1 | 11 | 与田祐希 | 3 | 56 | 11 | | 2 | 20 | 中村麗乃 | 3 | 16 | 3 | | 3 | 1 | 久保史緒里 | 3 | 70 | 14 | | 4 | 7 | 伊藤かりん | 2 | 62 | 12 | | 5 | 8 | 伊藤理々杏 | 3 | 60 | 12 | （中略）... +----+------+--------------------------------+-------+-------+------+ 21 rows in set (0.00 sec)
この状態でB画面で適当にある行を持ってこようとすると...。
B) mysql> select * from zuno_o where id = 5 for update; ERROR 1317 (70100): Query execution was interrupted -- (select ... for updateでなければ取得は可能) mysql> select * from zuno_o where id = 5; +----+------+-----------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+-----------------+-------+-------+------+ | 5 | 8 | 伊藤理々杏 | 3 | 60 | 12 | +----+------+-----------------+-------+-------+------+ 1 row in set (0.00 sec)
FOR UPDATEがなければトランザクションに入る前のスナップショット？から情報を引っ張ってこられます（という認識で合ってる？）。
しかし、FOR UPDATEだとA画面のほうの処理がcommitされるまで待機させられます。（ここでは強制終了。）
主キーの条件つきで行のロックをかける
今度はidカラムで条件つきのSELECT FOR UPDATE文を試してみます。
A) mysql> select * from zuno_o where id = 8 for update; +----+------+--------------------------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+--------------------------------+-------+-------+------+ | 8 | 13 | 吉田綾乃クリスティー | 3 | 50 | 10 | +----+------+--------------------------------+-------+-------+------+ 1 row in set (0.00 sec)
この状態で、異なる主キーの行を取得してみます。
B) mysql> select * from zuno_o where id = 3 for update; +----+------+-----------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+-----------------+-------+-------+------+ | 3 | 1 | 久保史緒里 | 3 | 70 | 14 | +----+------+-----------------+-------+-------+------+ 1 row in set (0.00 sec)
問題なし。当然ですが、
B) mysql> select * from zuno_o where id = 8 for update; ERROR 1317 (70100): Query execution was interrupted
こちらはロックがかかっているため取得できません。 ちゃんと行でロックがかかっていることがわかりました。
ではユニークキーで定められたカラムの条件で取得はできるでしょうか？
B) mysql> select * from zuno_o where name LIKE '堀%' for update; +----+------+--------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+--------------+-------+-------+------+ | 11 | 18 | 堀未央奈 | 2 | 28 | 6 | +----+------+--------------+-------+-------+------+ 1 row in set (0.00 sec)
取得できました。
無制約なカラムの条件で取得を試みると...。
B) mysql> select * from zuno_o where rank > 17 for update; ERROR 1317 (70100): Query execution was interrupted
今度は、取得できません。だんだん挙動がわかって来たでしょうか。
（僕が手を動かしたのでだいぶ掴んで来ました。）
制約のないキーのみの条件で行のロックをかける
A) mysql> select * from zuno_o where rank = 17 for update; +----+------+-----------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+-----------------+-------+-------+------+ | 18 | 17 | 渡辺みり愛 | 2 | 30 | 6 | +----+------+-----------------+-------+-------+------+ 1 row in set (0.01 sec)
B) -- (主キーで絞っても無理) mysql> select * from zuno_o where id > 5 for update; ERROR 1317 (70100): Query execution was interrupted -- (ユニークキーで絞っても無理) mysql> select * from zuno_o where name LIKE '鈴木%' for update; ERROR 1317 (70100): Query execution was interrupted -- (無制約なカラムで絞っても無理) mysql> select * from zuno_o where rank = 1 for update; ERROR 1317 (70100): Query execution was interrupted
ここからわかるのは特に制約のないキーで行ロックをかけると全ての行がロックされてしまうということです。（あー危険！危険！）
制約のあるカラムとないカラムでの複数条件でロックをかける
ではここまで来ると気になるのが複数条件の場合。
主キー(id)と無制約なカラム(grade)で条件を設けてみました。
A) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from zuno_o where id > 10 and grade=3 for update; +----+------+--------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+--------------+-------+-------+------+ | 12 | 19 | 大園桃子 | 3 | 20 | 4 | | 13 | 5 | 山下美月 | 3 | 64 | 13 | | 15 | 16 | 岩本蓮加 | 3 | 36 | 7 | | 17 | 15 | 梅澤美波 | 3 | 42 | 8 | | 21 | 14 | 阪口珠美 | 3 | 45 | 9 | +----+------+--------------+-------+-------+------+ 5 rows in set (0.00 sec)
B) mysql> begin; Query OK, 0 rows affected (0.00 sec) -- (主キーの条件範囲外の行は取得できる) mysql> select * from zuno_o where id = 9 for update; +----+------+--------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+--------------+-------+-------+------+ | 9 | 12 | 向井葉月 | 3 | 54 | 11 | +----+------+--------------+-------+-------+------+ 1 row in set (0.00 sec) -- (主キーの条件範囲内の行は取得できない) mysql> select * from zuno_o where id = 11 for update; ERROR 1317 (70100): Query execution was interrupted -- (無制約なカラムの方で引っかかっていなくても主キーの条件に引っかかれば取得できない) mysql> select * from zuno_o where id > 10 and grade=2 for update; ERROR 1317 (70100): Query execution was interrupted -- (主キーの条件に引っ掛かられなければ無制約なカラムの方で引っかかっても取得できる) mysql> select * from zuno_o where id < 10 and grade=3 for update; +----+------+--------------------------------+-------+-------+------+ | id | rank | name | grade | total | ave | +----+------+--------------------------------+-------+-------+------+ | 1 | 11 | 与田祐希 | 3 | 56 | 11 | | 2 | 20 | 中村麗乃 | 3 | 16 | 3 | | 3 | 1 | 久保史緒里 | 3 | 70 | 14 | | 5 | 8 | 伊藤理々杏 | 3 | 60 | 12 | | 7 | 5 | 佐藤楓 | 3 | 64 | 13 | | 8 | 13 | 吉田綾乃クリスティー | 3 | 50 | 10 | | 9 | 12 | 向井葉月 | 3 | 54 | 11 | +----+------+--------------------------------+-------+-------+------+ 7 rows in set (0.00 sec)
実はここまでの検証記録？は世間でいうしゅっしゅー問題を理解するための布石、準備のようなもののつもりでした。
ただ実験して「へー」ってなっただけの記事となってしまったので、次回はちゃんとインデックスレコードとかギャップロックとかをその辺を考えつつ、しゅっしゅー問題とは何かの理解を深めたいと思います。
参考
MySQLの行ロックのふしぎ挙動で夜も安心して眠れない - 三鷹台でひきこもるプログラマの日記
トランザクションについて(SELECT FOR UPDATE） - K-ONE BLOG
InnoDBで行ロック/テーブルロックになる条件 - (ﾟ∀ﾟ)o彡 sasata299's blog
PHP + PDO + MySQL のトランザクション制御方法まとめ
InnoDBにおけるロックの種類 - Sojiro’s Blog
世界の何処かで MySQL（InnoDB）の REPEATABLE READ に嵌る人を1人でも減らすために - KAYAC engineer's blog
