QA@IT
«回答へ戻る

回答を投稿

解決されてしばらく経っていますが、あとから見にきた方のために少しだけ補足します。

今回の例でデッドロックが発生するのは、トランザクション分離レベルがREPEATABLE READの場合です。InnoDBはREPEATABLE READがデフォルト設定となっていますが、ここをあえてREAD COMMITTEDに設定するとこのデッドロックは発生しなくなります。これはREAD COMMITTEDの場合、最初のSELECT FOR UPDATE文がギャップロックを確保しないためです。

もちろんREAD COMMITTEDではギャップロックを確保しないことによりファントムリードが発生しますし、さらにファジーリードも発生しますので、既存のプログラムに対していきなりトランザクション分離レベルを変更することは難しいです。

個人的には、過去のしがらみのない新規プログラムであればREAD COMMITTEDを選択し、ファジーリードを起こしたくないところは適宜LOCK IN SHARE MODEかFOR UPDATEをかける、ファントムリードについては妥協するか1トランザクション内で同じテーブルを何度も参照しないよう設計面で注意する、といった対応にした方が面倒が少ないのではないかと考えています。Oracle Database、Microsoft SQL Server、PostgreSQLはデフォルトがREAD COMMITTEDですので、移植性も良くなります。

なおREAD COMMITTEDの場合はバイナリログのフォーマットがROWになるので、バイナリログの容量増加について注意する必要があります。

解決されてしばらく経っていますが、あとから見にきた方のために少しだけ補足します。

今回の例でデッドロックが発生するのは、トランザクション分離レベルがREPEATABLE READの場合です。InnoDBはREPEATABLE READがデフォルト設定となっていますが、ここをあえてREAD COMMITTEDに設定するとこのデッドロックは発生しなくなります。これはREAD COMMITTEDの場合、最初のSELECT FOR UPDATE文がギャップロックを確保しないためです。

もちろんREAD COMMITTEDではギャップロックを確保しないことによりファントムリードが発生しますし、さらにファジーリードも発生しますので、既存のプログラムに対していきなりトランザクション分離レベルを変更することは難しいです。

個人的には、過去のしがらみのない新規プログラムであればREAD COMMITTEDを選択し、ファジーリードを起こしたくないところは適宜LOCK IN SHARE MODEかFOR UPDATEをかける、ファントムリードについては妥協するか1トランザクション内で同じテーブルを何度も参照しないよう設計面で注意する、といった対応にした方が面倒が少ないのではないかと考えています。Oracle Database、Microsoft SQL Server、PostgreSQLはデフォルトがREAD COMMITTEDですので、移植性も良くなります。

なおREAD COMMITTEDの場合はバイナリログのフォーマットがROWになるので、バイナリログの容量増加について注意する必要があります。