【MySQL】SELECT ~ FOR UPDATEによるロックの挙動

MySQLでは`SELECT`文の後ろに`FOR UPDATE`を追加することで排他ロックをかけることができます。
以前このSQLの挙動に関連して不具合が発生しましたので、調査したことを備忘録として記載します。
# SELECT ~ FOR UPDATE 概要
+ `SELECT`対象のレコードに対して排他ロックをかけるときに使用
+ `COMMIT`もしくは`ROLLBACK`を行うことでロックを解除
+ `SELECT`の対象にされたレコードは他のトランザクションから`SELECT ~ FOR UPDATE`や`UPDATE`、`DELETE`できない
+ **ただし単なる`SELECT`文からは参照可能**

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

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

![テーブル.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/d031a253-9582-384d-b371-b47777c5c3be.png)



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

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


| ![占有ロック状態-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/9a6eca42-b1de-23d7-ef10-f857448df126.png)　|　![selectタイムアウトエラー.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/2131b7e1-ffb6-84b9-e069-877711457b8f.png) |
|:-:|:-:|
| セッション1  |  セッション2 |


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

| ![占有ロック状態-2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/5cbe880f-f99f-7a6d-85e9-689b9f0cfd87.png) | ![select_占有外のfor_update.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/8db63d46-a0fd-2f9f-0c66-fb06ddd7015d.png) |
|:-:|:-:|
| セッション1 | セッション2 |

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

| ![占有ロック状態-3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/834215fb-5ef8-d5e6-cd5a-d2548e6fc8d8.png) | ![update_ロックエラー.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/053dd82d-a03c-fd78-5d20-d8f25af5f626.png) |
|:-:|:-:|
| セッション1 | セッション2 |

# 4. SELECT ~ FOR UPDATEの対象になっているレコードに対してSELECTを実行
後から`SELECT`を実行したセッション側でもロック解除を待つことなくレコード内容を参照することができます。

| ![占有ロック状態-4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/ed7f77e2-ef9b-afdb-c322-f9bc1d903c9f.png) | ![通常select.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/1e6648a2-2527-0bf2-daec-baf197a28208.png) |
|:-:|:-:|
| セッション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になっている**

| ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/8ed87824-6368-392f-12c6-49832a335ada.png) | ![image-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/a2c44d76-2718-2f22-6f8e-8ba884435bd7.png) |
|:-:|:-:|
| セッション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になっている**

| ![image-2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/427c0a1d-edec-2ed7-4e6d-c4cf57d1012b.png) | ![image-3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3676048/a1e8b5ce-7fdc-b2b8-1b01-2659d11f54f6.png) |
|:-:|:-:|
| セッション1 | セッション2 |

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

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

参考サイト：[Railsのロック制御を完全理解する　Chapter 05　MySQLのロック機構（SELECT FOR UPDATE）](https://zenn.dev/shuhei_takada/books/3ff66325f45adc/viewer/55e4f6)

https://zenn.dev/shuhei_takada/books/3ff66325f45adc/viewer/55e4f6