Edited at

Using MySQL/InnoDB/行レベルロック: デッドロックを回避するために、実用上最低限必須の知識

More than 5 years have passed since last update.


デフォルトの isolation level (トランザクション分離レベル) はREAD COMMITTEDに変更するべき

内部が不透明な既存アプリケーションを変更する場合等、クリティカルな場合を除いて、InnoDBを利用する場合はデフォルトのisolation levelをREAD COMMITTEDに変更するべきである。

根拠は MySQL (InnoDB) でデッドロック検知される条件について - QA@IT ここに解説がある。

Railsでは、 以下のページに書いてあるようなモンキーパッチでデフォルト設定の変更が可能である。

Rails 4では、トランザクションにオプションを渡すことでも変更が可能である。或いは、my.cnfで設定することも可能である。しかし、これはDBレベルの話ではなくアプリケーションレイヤーの話なので、アプリケーション側で修正するのが望ましいと僕は思う。

参照: Rails & MySQL: トランザクション分離レベルをグローバルに設定する - tkrdの日記 (rails や ubuntu のことなど)


デッドロックが起こる状況

一番書きたかったことは前の項目で書き終わってしまったのだが、もう少し解説をしたい。

重要なことであるが、isolation level が何であれ、 「非ロック参照系 (non-locking read)」がいくら並列実行されてもデッドロックは絶対に起こらない。 何故ならば、そもそもnon-locking readではロック自体が起こりえないからだ。

しかし、 ロックする参照系 (locking read) では、isolation levelによっては 、デッドロックは起こり得る。 何故なら、これは、取得した値の整合性を保証するために、参照系であるのに関わらずロックを取得するからである。先ほどのリンクに実例があるので参照されたい。

MySQL用語目録の「deadlock」の項に、


The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations.

(筆者訳)デッドロックの可能性はisolation levelには影響されない。何故ならば、isolation levelは参照系の挙動を変えるものであり、デッドロックとは更新系の最中にのみ起こるものだからである。


と書いてあるが、 大嘘である。

locking read の項には、きちんと「デッドロックの可能性はisolation levelに影響 される 」と書いてある。これはドキュメントのバグというか、前者の項が考えられる全ての可能性を記載できていないのだろう。とにかくこれは大嘘である。


デッドロックは絶対に起こるもの

デッドロックはMySQL/InnoDBのようなトランザクショナルデータベースを利用している場合、絶対に不可避である。絶対に起こる。ただし、 実は怖がる必要は一切ない。

第一に、InnoDBに限って言えば、InnoDBはデッドロック検知機構が搭載されているので、デッドロックが発生した場合は どれかのトランザクション(=victim) が 安全にROLLBACKされる。つまり、 アプリケーション側でそれを検知出来る。 エラーハンドリングをきちんと実装しておき、そのような場合には再試行すれば良いだけだ。

第二に、 デッドロックの可能性は、いくつかのTipsを覚えておくことによって、格段に減らすことが出来る。

これについては全ての項目を説明することはしないので、以下のマニュアルページを参考にして頂きたい。

やれやれ、しかしながら、果たしてこれを本当に全て正しく理解しているエンジニア/Webサービスが、どれくらいの割合で存在するのだろうか。とても一般に浸透しているとは思えない知識である。

参照: MySQL :: MySQL 5.5 Reference Manual :: 14.3.5.11 How to Cope with Deadlocks


(補足)MySQL(InnoDB)だけがデフォルトのisolation levelを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になっているのもなるほどという感じです。


http://d.hatena.ne.jp/sh2/20090112

なるほど、こういうことらしいです。これって余計な処理が入ったりしないんですかね……。