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
を実行したセッション1側は、レコード内容を見ることができます。
後から実行したセッション2は、レコードが確認できず、このままロックが解除されない場合はロックタイムアウトのエラーになります。
タイムアウトになる前にセッション1の方で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)