MySQLではSELECT文の後ろにFOR UPDATEを追加することで排他ロックをかけることができます。
以前このSQLの挙動に関連して不具合が発生しましたので、調査したことを備忘録として記載します。

SELECT ~ FOR UPDATE 概要

SELECT対象のレコードに対して排他ロックをかけるときに使用
COMMITもしくはROLLBACKを行うことでロックを解除
SELECTの対象にされたレコードは他のトランザクションからSELECT ~ FOR UPDATEやUPDATE、DELETEできない
ただし単なるSELECT文からは参照可能

実際の挙動についてMySQL上で確認してみます。

実際の挙動

以下のテーブルを対象に2つのセッションからSQLを実行してみます。

1. 2つとも同じレコードに対してSELECT FOR UPDATEを実行

先にSELECT ~ FOR UPDATEを実行したセッション１側は、レコード内容を見ることができます。
後から実行したセッション２は、レコードが確認できず、このままロックが解除されない場合はロックタイムアウトのエラーになります。

タイムアウトになる前にセッション１の方でCOMMITを実行すると、待機させられていた処理が実行されます。


セッション1	セッション2

2. 別々のレコードに対してSELECT ~ FOR UPDATEを実行

排他ロックが重複していないので、どちらのSELECT ~ FOR UPDATEも実行でき、それぞれのレコード内容を見ることができます。


セッション1	セッション2

3. SELECT ~ FOR UPDATEの対象になっているレコードに対してUPDATEを実行

先にSELECT ~ FOR UPDATEがかかっているレコードに対してUPDATEを実行しようとするとロック解除待機状態になり、ロックが解除されない場合はロックタイムアウトのエラーになります。


セッション1	セッション2

4. SELECT ~ FOR UPDATEの対象になっているレコードに対してSELECTを実行

後からSELECTを実行したセッション側でもロック解除を待つことなくレコード内容を参照することができます。


セッション1	セッション2

参照するだけでは特に問題ありませんが、SELECTで取得した値を使用する場合を考えます。

5. SELECTで取得した値を使ってUPDATEを実行

SELECT ~ FOR UPDATEの対象になっているレコードに対して、現在のorder_idの値(102)をもとに更新することを考えます。

① セッション1の方でSELECT ~ FOR UPDATEを実行し、order_idの値を取得(変数orderIdにセット)
② セッション2の方でSELECTを実行し、order_idの値を取得(変数orderId2にセット)
③ セッション1の方でorderIdの値に1を足して更新してCOMMIT
④ 現在のorder_idの値が103になっていることを確認
⑤ セッション2の方でorderId2の値に2を足して更新
⑥ 現在のorder_idの値が104になっている


セッション1	セッション2

正しい処理ではセッション1,2の2つの処理が順番に反映されてorder_idが105になってほしいのですが、最終的に③の更新結果が無視されています。(ロストアップデート)
SELECTだとロックがかかっている更新前レコードも参照できてしまうのでこのような不整合が発生してしまいます。

6. SELECT ~ FOR UPDATEで取得した値を使ってUPDATEを実行

5の②の処理をSELECTでなくSELECT ~ FOR UPDATEを使って同様の処理を実行してみます。

① セッション1の方でSELECT ~ FOR UPDATEを実行し、order_idの値を取得(変数orderIdにセット)
② セッション2の方でSELECT ~ FOR UPDATEを実行し、order_idの値を取得(変数orderId2にセット)
③ セッション1の方でorderIdの値に1を足して更新してCOMMIT
④ 現在のorder_idの値が103になっていることを確認
⑤ セッション2の方でorderId2の値に2を足して更新
⑥ 現在のorder_idの値が105になっている


セッション1	セッション2

この処理ではセッション1がCOMMITされるまでセッション2の②の処理が待機するので、不整合なく処理されます。

まとめ

今回はMySQLのSELECT ~ FOR UPDATEの挙動について紹介しました。
別々のトランザクションでSELECT ~ FOR UPDATEとSELECTが同じレコードを参照・更新する場合、不整合が発生する可能性があるので、今後ネイティブなSQLを書くときは意識していただけると幸いです。

参考サイト：Railsのロック制御を完全理解する　Chapter 05　MySQLのロック機構（SELECT FOR UPDATE）