排他ロックFOR UPDATEと
共有ロックLOCK IN SHARE MODEの使いどころ | MySQLの場合

※この記事は、MySQLバージョン5.6、データベースエンジンInnoDB、トランザクション分離レベルREPEATABLE READ、オートコミットON、ロック対象は「テーブル」でなく「行」を想定しています。

排他ロック(FOR UPDATE)の使用例

FOR UPDATEによる排他ロックは、複数のトランザクションから同じレコードに対して同時に更新が行われる際に、データの整合性を保つために使用される。

SELECT * FROM parent WHERE parent_id = 1 FOR UPDATE;

parentテーブル(プライマリキーがparent_id)において、トランザクション中に、このクエリを発行するとparent_id=1のレコードに排他ロックがかかる。

排他ロックは他のトランザクションからのparent_id=1のレコードに対する読み取り・書き込みを禁止する。

そのため、複数トランザクションが同じparent_id=1のレコードをSELECTしてUPDATEしようとした場合でも、他のトランザクションは最初のトランザクションの終了(COMMIT or ROLLBACK)まで、SELECTの時点で待たされる。

結果として、parent_id=1のレコードの更新を、データの整合性を維持しつつ行うことができる。

共有ロック(LOCK IN SHARE MODE)

LOCK IN SHARE MODEによる共有ロックを、排他ロックと同じように使ってみるとどうなるか。

SELECT * FROM parent WHERE parent_id = 1 LOCK IN SHARE MODE;

parentテーブル(プライマリキーがparent_id)において、トランザクション中に、このクエリを発行するとparent_id=1のレコードに共有ロックがかかる。

共有ロックは他のトランザクションからのparent_id=1のレコードに対する読み込みを許可(書き込みは禁止)するため、複数のトランザクションが同じparent_id=1のレコードをSELECTできる。

トランザクションAとトランザクションBがparent_id=1のレコードをそれぞれ共有ロックで読み取ったあとで、トランザクションAがparent_id=1のレコードをUPDATE、トランザクションBがparent_id=1のレコードをUPDATEすると、デッドロックでエラーになってしまう。

ある行をUPDATEするためにロックしたい場合、排他ロック(FOR UPDATE)を使用した方がよい。

では、共有ロック(LOCK IN SHARE MODE)はどのような時に使うべきか?

参照整合性

LOCK IN SHARE MODEによる共有ロックは「参照整合性」を確保するために使われる。

「参照整合性」とは、テーブル間のデータの整合性のことだ。

例えば、childテーブルとparentテーブルがあるとして、childテーブルにparent_idが5のレコードがある場合、parentテーブルには必ずparent_id=5のレコードが存在しなければならない。

存在しなければ、「参照整合性」が破壊された整合性のないデータということである。

共有ロック(LOCK IN SHARE MODE)の使用例

childテーブルに、parent_id=5である新しいchildレコードを追加する場合の処理を考えてみよう。

SELECT * FROM parent WHERE parent_id = 5 LOCK IN SHARE MODE;

parentテーブルのparent_id=5のレコードを共有ロックすることで、読み取りはできるものの、書き込み(UPDATE、DELETE)はできなくなる。

INSERT INTO child (child_id, parent_id) VALUES (1, 5);

childテーブルに、parent_id=5のchildレコードをINSERTしトランザクションを終了すれば、SELECTとINSERTの一連の処理において、childテーブルとparentテーブルの「参照整合性」が確保される。

トランザクションAが、parentテーブルからparent_id=5のレコードをSELECTした後で、トランザクションBが、parentテーブルからparent_id=5のレコードを削除し、トランザクションAがchildテーブルに、parent_id=5のレコードをINSERTする、ということが起こらないようにできるのだ。

参考

MySQL 5.6 リファレンスマニュアル / 14.2.5 ロック読み取り (SELECT … FOR UPDATE および SELECT … LOCK IN SHARE MODE)