Hatena::ブログ(Diary)

SH2の日記 RSSフィード

2009-01-12 MySQL InnoDBのネクストキーロック おさらい このエントリーを含むブックマーク

MySQLのInnoDBストレージエンジンは行ロックをサポートしています。しかしOracleと同じ感覚でアプリケーションを作っていると、思わぬところでデッドロックに出くわすことがあります。これはInnoDBのロック範囲がOracleよりも微妙に広いためです。

実際の例で確認してみましょう。

mysql> select * from t;
+----+------+
| c1 | c2   |
+----+------+
| 10 | a    |
| 15 | a    |
| 20 | a    |
| 25 | a    |
| 30 | a    |
| 35 | a    |
| 40 | a    |
| 45 | a    |
| 50 | a    |
+----+------+

c1列は主キーになっています。1つめのセッションで以下のSQLを実行します。

mysql> set tx_isolation = 'repeatable-read';
mysql> set autocommit = 0;
mysql> update t set c2 = 'b' where c1 < 30;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

2つめのセッションで以下のSQLを実行すると…

mysql> set tx_isolation = 'repeatable-read';
mysql> set autocommit = 0;
mysql> update t set c2 = 'c' where c1 = 30;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

このようにc1 = 30の行のロックを獲得することができません。

1つめのセッションではc1 < 30の行だけロックをすればいいのですが、実際にはc1 = 30の行もロックされてしまっています。これはInnoDBのアーキテクチャからもたらされている制限事項で、このロックのことをネクストキーロックといいます。ある範囲をロックする際に、一つ先の行までロックをかけることで「範囲」というものを表現する仕組みです。

MySQL 5.0、バイナリログON、トランザクション分離レベルREAD COMMITTED(RC)とREPEATABLE READ(RR)、SERIALIZABLE(S)でいくつかのパターンを試してみました。

先行トランザクション後続トランザクションRCRRS
update t set c2 = 'b' where c1 = 30select * from t where c1 = 30
update t set c2 = 'c' where c1 = 25
update t set c2 = 'c' where c1 = 35
insert into t values (29, 'c')
insert into t values (31, 'c')
update t set c2 = 'b' where c1 < 30select * from t where c1 < 30
update t set c2 = 'c' where c1 = 30×××
update t set c2 = 'c' where c1 = 35
insert into t values (29, 'c')×
insert into t values (31, 'c')
update t set c2 = 'b' where c1 > 30select * from t where c1 > 30
update t set c2 = 'c' where c1 = 25
update t set c2 = 'c' where c1 = 30
insert into t values (29, 'c')
insert into t values (31, 'c')×
update t set c2 = 'b' where c1 <= 30select * from t where c1 <= 30
update t set c2 = 'c' where c1 = 35×××
insert into t values (29, 'c')×
insert into t values (31, 'c')×××
update t set c2 = 'b' where c1 >= 30select * from t where c1 >= 30
update t set c2 = 'c' where c1 = 25
insert into t values (29, 'c')
insert into t values (31, 'c')×

ネクストキーロックで待たされてしまっているのは、c1 < 30のときのc1 = 30、c1 <= 30のときのc1 = 35、INSERT 31です。特にc1 <= 30のときの挙動はなかなか強烈だと思います。

不等号の向きによって挙動が変わる点も注意したいところです。c1 > 30、c1 >= 30のときはネクストキーロックは観測されません。ネクストキーロックは一つ先の行をロックする仕組みなので、一つ前の行には何もしません。

△について少し補足すると、REPEATABLE READでc1 < 30のときにINSERT 29がブロックされるのは、REPEATABLE READがファジーリードを許さないためです。ファジーリードを防ぐ仕組みをどのように実装するかはRDBMSによって異なるのですが、InnoDBでは最初からINSERTやUPDATEをさせないという方針になっています。INSERTも止めてしまうので、ファントムリードも発生しないことになります。「InnoDBのREPEATABLE READはファントムリードを起こさない実装」というのはこのことを指しています。

さて、表をじっくり見ると、READ COMMITTEDとREPEATABLE READが実はまったく同じことに気づくかと思います。READ COMMITTEDはファントムリードを許すので、例えばc < 30のときのINSERT 29は本来許可されるべきです。そうなっていないのは、MySQL 5.0におけるバイナリログの制限が原因になっています。MySQL 5.0までのバイナリログはこのようなトランザクションに対してログの整合性を担保できないことがあるため、InnoDB側であえてロックの範囲を広げているのです。結局のところREAD COMMITTEDとREPEATABLE READで挙動が変わらないのですから、InnoDBのデフォルト分離レベルがREPEATABLE READになっているのもなるほどという感じです。

MySQL 5.1では行ベースレプリケーションという新機能でこの制限が緩和されています。MySQL 5.1で起動パラメータbinlog_formatにmixedまたはrowを指定して同じパターンの動作確認をしてみると、以下のようになります。

先行トランザクション後続トランザクションRCRRS
update t set c2 = 'b' where c1 = 30select * from t where c1 = 30
update t set c2 = 'c' where c1 = 25
update t set c2 = 'c' where c1 = 35
insert into t values (29, 'c')
insert into t values (31, 'c')
update t set c2 = 'b' where c1 < 30select * from t where c1 < 30
update t set c2 = 'c' where c1 = 30×××
update t set c2 = 'c' where c1 = 35
insert into t values (29, 'c')
insert into t values (31, 'c')
update t set c2 = 'b' where c1 > 30select * from t where c1 > 30
update t set c2 = 'c' where c1 = 25
update t set c2 = 'c' where c1 = 30
insert into t values (29, 'c')
insert into t values (31, 'c')
update t set c2 = 'b' where c1 <= 30select * from t where c1 <= 30
update t set c2 = 'c' where c1 = 35×××
insert into t values (29, 'c')
insert into t values (31, 'c')××
update t set c2 = 'b' where c1 >= 30select * from t where c1 >= 30
update t set c2 = 'c' where c1 = 25
insert into t values (29, 'c')
insert into t values (31, 'c')

このようにREAD COMMITTEDが大幅に改善されています。ネクストキーそのものについては変化がありませんが、それ以外の行については後続トランザクションが問題なくロックを獲得できるようになっています。MySQL 5.1、なかなか頑張っています。

InnoDBのこうした特性についてはあまり参考資料がないのが現状で、MySQLを業務に活用されようとしている方に対して少しでも参考になればと思い筆をとりました。InnoDBのトランザクション制御はまだまだ奥が深くて、私もごく浅いところしか理解できていません。「c1列が主キーでない場合は?」「UPDATEではなくDELETEだった場合は?」など、いろいろ試してみると面白いと思います。