updateがなぜ大変か〜分離レベルから検証してみる〜

# updateがなぜ大変か〜分離レベルから検証してみる〜

イミュータブルデータモデルについて資料を見ていると、いろいろなところでupdateが処理を複雑にするという記述を見かける。確かに競合を防ぐためにロックを取得しなければならないなど、コード量が増えるとは思いつつ具体的な大変さについて理解できていなかったので、updateに不可欠なトランザクションやロックについて勉強してまとめてみた。

なお、トランザクションや分離レベルについては既に多くのドキュメントがネットにあり、このドキュメントの内容は、それらを参考にRailsで実装した場合の動作検証なので以下のドキュメントを見た人には特に発見はない。

**参考にさせてもらったドキュメント（感謝）**

- [世界の何処かで MySQL（InnoDB）の REPEATABLE READ に嵌る人を1人でも減らすために](http://techblog.kayac.com/repeatable_read.html)
- [InnoDBのREPEATABLE READにおけるLocking Readについての注意点](http://nippondanji.blogspot.jp/2013/12/innodbrepeatable-readlocking-read.html)
- [MySQLのInnoDBのロック挙動調査](https://github.com/ichirin2501/doc/blob/master/innodb.md#%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E5%88%86%E9%9B%A2%E3%83%AC%E3%83%99%E3%83%AB)
- [現場で使えるMySQL](https://www.amazon.co.jp/%E7%8F%BE%E5%A0%B4%E3%81%A7%E4%BD%BF%E3%81%88%E3%82%8B-MySQL-DB-Magazine-SELECTION/dp/4798111139)

# はじめに

ミュータブルなテーブル設計をしていると、アプリケーションの業務ロジック中にレコードを更新する処理が頻繁に出てくる。その際、複数のリクエストが同時に来た場合でも正確な順序でデータを更新するために、各リクエストでトランザクションをはり、他のトランザクションからの更新をブロックすることで不正な更新を防ぐ。ただ、トランザクションの分離レベルをよく理解しないまま実装を行ってしまうと思わぬ挙動となってしまうので注意が必要。具体的なサンプルロジックを使いながら挙動について確かめていく。

### 前提

以下の環境で検証

- 言語：Ruby 2.5.0
- フレームワーク : Ruby on Rails 5.1.6
- DB : MySQL : 5.6.39
    - ストレージエンジン：InnoDB
    - トランザクション分離レベル：Repeatable Read

# トランザクションの分離レベルについて

詳しい説明は[いちりんめも](https://github.com/ichirin2501/doc/blob/master/innodb.md#%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E5%88%86%E9%9B%A2%E3%83%AC%E3%83%99%E3%83%AB)を読んで欲しい。

MySQLのInnoDBでは、Repeatable Readがデフォルトとなっている。各分離レベルごとの問題への対応レベルの違いは次の通り。

|分離レベル|ダーティリード|ファジーリード|ファントムリード|検索時のロック|
|:-------|:-----------|:------------|:-------------|:----------|
|Read Uncommitted|発生|発生|発生|かけない|
|Read Committed|発生しない|発生|発生|かけない|
|Repeatable Read|発生しない|発生しない|発生しない|かけない|
|Serializable|発生しない|発生しない|発生しない|かける|

※Repeatable Readでファントムリードが発生しないようになっているのはInnoDBの特徴

**補足**

- ダーティリード
    - 二つのトランザクションが合った時に、片方がもう一方の未コミットのデータにアクセスできること
    - 未コミットのデータはロールバックされることもあり、存在しないデータで処理が行われるのは危険
- ファジーリード
    - トランザクションの中で2つのselect文を発行した時に、別トランザクションが更新、削除してcommitされたデータに影響されて、異なる値が取得できること
- ファントムリード
    - トランザクションの中で2つのselect文を発行した時に、別トランザクションが挿入してcommitされたデータに影響されて、異なる値が取得できること
- 検索時のロック
    - select文を発行した時に、デフォルトでfor updateが使用されるか

# Repeatable Readで注意しなければならない点

## ロストアップデート

ロストアップデートは、ファジーリード・ファントムリードを防止する代わりに、複数のトランザクションが同時に存在した場合に片方の変更処理が別のトランザクションで見れないことにより発生する。

例えば、以下のようなコードで確認できる。

事前のテーブルの状態

Itemsテーブル

|id|name|stock|
|:--|:---|:----|
|1|hoge|1|

<font color="DeepPink">**トランザクションA**</font>

```
irb(main):081:0> Item.transaction do
irb(main):082:1*   item = Item.find(1)
irb(main):083:1>   item.update(stock: item.stock + 10)
irb(main):084:1>   sleep(10)
irb(main):085:1> end
   (0.3ms)  BEGIN
  Item Load (0.6ms)  SELECT  `items`.* FROM `items` WHERE `items`.`id` = 1 LIMIT 1
  SQL (0.5ms)  UPDATE `items` SET `stock` = 11, `updated_at` = '2018-05-26 07:40:22' WHERE `items`.`id` = 1

```

ここでsleepの待ち状態になる...

<font color="Blue">**トランザクションB**</font>

```
irb(main):084:0> Item.transaction do
irb(main):085:1*   item = Item.find(1)
irb(main):086:1>   item.update(stock: item.stock + 5)
irb(main):087:1> end
   (0.2ms)  BEGIN
  Item Load (0.3ms)  SELECT  `items`.* FROM `items` WHERE `items`.`id` = 1 LIMIT 1

```

ここで<font color="DeepPink">トランザクションA</font>のコミット待ち状態になる（InnoDBはInser/Update/Deleteに自動的に排他ロックがかかるため）

<font color="DeepPink">**トランザクションA**</font>

```
   (2.1ms)  COMMIT
```

<font color="DeepPink">トランザクションA</font>が終わる

<font color="Blue">**トランザクションB**</font>

```
  SQL (8365.3ms)  UPDATE `items` SET `stock` = 6, `updated_at` = '2018-05-26 07:40:23' WHERE `items`.`id` = 1
   (0.9ms)  COMMIT
```

ロックの解放後にUpdateが実行されるが、ファジーリードが回避されているため、`find`で読み込まれるのは<font color="DeepPink">トランザクションA</font>が更新する前のレコードとなり、`stock`の更新値は初期値の`1 + 5 = 6`となる。

そのため、**<font color="DeepPink">トランザクションA</font>が行った更新結果がなかったものとなってしまう。** これはファントムリードを防ぐため、MySQLが採用している一貫性を担保する仕組み上仕方のないこと。

### 解決策

`select ~ for update`にすることで、別トランザクションでコミットされた最新値を取得できる。ActiveRecordには`lock`というメソッドが準備されているため、`Item.lock.find(1)`と変更することでロストアップデートを回避することができる。

### 実装上の注意点

レコードの更新を行う処理を見つけたら、必ずロストアップデート問題が発生するか確認した方が良い。InnoDBの場合、Updateには自動的に排他ロックがかかるので、`transaction`で囲っていれば他のトランザクションが処理を追い越して値を不正に書き換えることはないが、`lock`を利用しないと**過去の値を元に処理が進んでしまう**ので、コードレビュー時でも特に気をつけたいところ。

また、生存期間の長い変数を利用している場合も要注意。`find`で取得した処理を元に、データベースの変更なしに画面側に値を返したい場合でも、他のリクエストによってDBの値が書き換わってしまい、すでに古い状態になっている可能性もある。そのため、`reload`などを使って最新データを取りに行く考慮をした方が良い。画面からのリクエストと、管理画面やバッチ処理などで並列に処理が行われるような機能だと考慮しておきたい。

**メモレベル：ロストアップデートによって問題が起きそうな処理**

- カラムの値のインクリメント処理
    - 上記のように保存されている値に加算すると、前の処理がなかったものとなってしまう
- カラムの値で分岐などをしている処理
    - 過去のデータを元に分岐処理が行われて、不適切な後続処理が行われてしまう
    - 取得した値で計算をしている場合でも古いデータでの計算処理になってしまう

# 複数のテーブルの更新をする場合の挙動

ロストアップデート対策として`lock`が有効であるが、`transaction`で囲う場合に全ての取得処理に`lock`が必要なわけではない。（毎回lockを書くのは面倒）

例えば購入処理を実装する場合に、商品の在庫数を引いて、同時にユーザーの残高も減らしたい場合、2つのテーブルを更新することになる。その場合の挙動を上と同様に確かめてみる。

Itemsテーブル

|id|name|stock|
|:--|:---|:----|
|1|hoge|10|

Moneyテーブル

|id|user_name|balance|
|:--|:---|:----|
|1|fuga|1000|


<font color="DeepPink">**トランザクションA**</font>

Itemテーブルのみにlockをかける。

```
irb(main):152:0> ActiveRecord::Base.transaction do
irb(main):153:1*   item = Item.lock.find(1)
irb(main):154:1>   item.update(stock: item.stock - 1)
irb(main):155:1>   money = Money.find_by(user_name: 'fuga')
irb(main):156:1>   money.update(balance: money.balance - 100)
irb(main):157:1>   sleep(20)
irb(main):158:1> end
   (0.6ms)  BEGIN
  Item Load (0.7ms)  SELECT  `items`.* FROM `items` WHERE `items`.`id` = 1 LIMIT 1 FOR UPDATE
  SQL (0.4ms)  UPDATE `items` SET `stock` = 9, `updated_at` = '2018-05-26 08:54:28' WHERE `items`.`id` = 1
  Money Load (1.9ms)  SELECT  `money`.* FROM `money` WHERE `money`.`user_name` = 'fuga' LIMIT 1
  SQL (0.7ms)  UPDATE `money` SET `balance` = 900, `updated_at` = '2018-05-26 08:54:28' WHERE `money`.`id` = 1

```

ここでsleepの待ち状態になる...

<font color="Blue">**トランザクションB**</font>

```

irb(main):117:0> ActiveRecord::Base.transaction do
irb(main):118:1*   item = Item.lock.find(1)
irb(main):119:1>   item.update(stock: item.stock - 2)
irb(main):120:1>   money = Money.find_by(user_name: 'fuga')
irb(main):121:1>   money.update(balance: money.balance - 50)
irb(main):122:1> end
   (0.3ms)  BEGIN
```

ここで<font color="DeepPink">トランザクションA</font>のコミット待ち状態になる。
※`lock`をかけているため、select文はまだ発行されない。

<font color="DeepPink">**トランザクションA**</font>

```
   (2.1ms)  COMMIT
```

<font color="DeepPink">トランザクションA</font>が終わる

<font color="Blue">**トランザクションB**</font>

```
Item Load (17798.0ms)  SELECT  `items`.* FROM `items` WHERE `items`.`id` = 1 LIMIT 1 FOR UPDATE
  SQL (0.4ms)  UPDATE `items` SET `stock` = 7, `updated_at` = '2018-05-26 08:54:48' WHERE `items`.`id` = 1
  Money Load (0.5ms)  SELECT  `money`.* FROM `money` WHERE `money`.`user_name` = 'fuga' LIMIT 1
  SQL (0.3ms)  UPDATE `money` SET `balance` = 850, `updated_at` = '2018-05-26 08:54:48' WHERE `money`.`id` = 1
   (0.5ms)  COMMIT
```

Item、Moneyの両テーブルとも、<font color="DeepPink">トランザクションA</font>のコミット後のレコードが取得されて値が更新されている。

## lockをかける場所には要注意

Moneyの取得処理にlockをかけなくても最新の値が取得できたのは、MySQLが実装している一貫性を担保するための仕組みに基づく[^1]。上記処理では、`transaction`内で初回にSQLを発行したタイミング（`Item.lock.find(1)`）で、データベースのスナップショットが作成され、以降の処理はそのスナップショットに対してアクセスが行われている。

そのため、ロックを取得するより前にSQLが発行されると、その時点のスナップショットが作成され、以降のSQLはスナップショット時点のデータに対してアクセスする。

具体的には以下のような処理を書いてしまうと、ロストアップデートになってしまう。

Itemsテーブル

|id|name|stock|
|:--|:---|:----|
|1|hoge|10|

Moneyテーブル

|id|user_name|balance|
|:--|:---|:----|
|1|fuga|1000|

Userテーブル

|id|name|
|:--|:---|
|1|Tatkuro|

<font color="DeepPink">**トランザクションA**</font>

Itemテーブルのみにlockをかける。（ここまでは一緒）

```
irb(main):152:0> ActiveRecord::Base.transaction do
irb(main):153:1*   item = Item.lock.find(1)
irb(main):154:1>   item.update(stock: item.stock - 1)
irb(main):155:1>   money = Money.find_by(user_name: 'fuga')
irb(main):156:1>   money.update(balance: money.balance - 100)
irb(main):157:1>   sleep(20)
irb(main):158:1> end
   (0.6ms)  BEGIN
  Item Load (0.7ms)  SELECT  `items`.* FROM `items` WHERE `items`.`id` = 1 LIMIT 1 FOR UPDATE
  SQL (0.4ms)  UPDATE `items` SET `stock` = 9, `updated_at` = '2018-05-26 08:54:28' WHERE `items`.`id` = 1
  Money Load (1.9ms)  SELECT  `money`.* FROM `money` WHERE `money`.`user_name` = 'fuga' LIMIT 1
  SQL (0.7ms)  UPDATE `money` SET `balance` = 900, `updated_at` = '2018-05-26 08:54:28' WHERE `money`.`id` = 1

```

ここでsleepの待ち状態になる...

<font color="Blue">**トランザクションB**</font>

Itemにロックをかける前に、**Userテーブルにselectする**。

```
irb(main):169:0> ActiveRecord::Base.transaction do
irb(main):170:1*   user = User.find_by(name: 'Takuro')
irb(main):171:1>   item = Item.lock.find(1)
irb(main):172:1>   item.update(stock: item.stock - 2)
irb(main):173:1>   money = Money.find_by(user_name: 'fuga')
irb(main):174:1>   money.update(balance: money.balance - 50)
irb(main):175:1> end
   (0.4ms)  BEGIN
  User Load (0.6ms)  SELECT  `users`.* FROM `users` WHERE `users`.`name` = 'Takuro' LIMIT 1
```

ここで<font color="DeepPink">トランザクションA</font>のコミット待ち状態になる。Itemテーブルへのロックの前にUserテーブルにselectしているため、コミット待ち状態より前にselect文が発行される。ここで、この`transaction`句のスナップショットが作成される。

<font color="DeepPink">**トランザクションA**</font>

```
   (2.1ms)  COMMIT
```

<font color="DeepPink">トランザクションA</font>が終わる

<font color="Blue">**トランザクションB**</font>

```
  Item Load (18785.3ms)  SELECT  `items`.* FROM `items` WHERE `items`.`id` = 1 LIMIT 1 FOR UPDATE
  SQL (0.5ms)  UPDATE `items` SET `stock` = 7, `updated_at` = '2018-05-26 09:14:31' WHERE `items`.`id` = 1
  Money Load (0.4ms)  SELECT  `money`.* FROM `money` WHERE `money`.`user_name` = 'fuga' LIMIT 1
  SQL (0.4ms)  UPDATE `money` SET `balance` = 950, `updated_at` = '2018-05-26 09:14:31' WHERE `money`.`id` = 1
   (0.7ms)  COMMIT
```

Itemテーブルには`for update`がついているため最新のデータが取得されるが、Moneyテーブルは`User.find_by`が実行された時点のスナップショットにアクセスするため、本来は`850`で更新されなければならないところが、`950`という古い値への更新処理になってしまっている。

このように処理のはじめに`lock`をかければ、テーブルをロックの取得できたタイミングでのスナップショットとなり、他のトランザクションからの操作を防止しつつ正確な変更処理が行えるが、**`lock`するより前**にDBにアクセスするとロストアップデートを防ぐことはできない。

ちなみにこのスナップショットへのアクセスは`transction`をネストした場合にも起きる。

```
ActiveRecord::Base.transaction do
  user = User.find_by(name: 'Takuro')

  ActiveRecord::Base.transaction do
    item = Item.lock.find(1)
    item.update(stock: item.stock - 2)
    money = Money.find_by(user_name: 'fuga')
    money.update(balance: money.balance - 50)
  end

end
```
そのため、処理をメソッドに切り分けている場合には、外だしされたロジック側で制御されていても呼び出し元で`transaction`が貼られていると意味がないので危険。`transaction`はネストしている場合でも、一つのトランザクションしか作られない。

# まとめ

- InnoDBはデフォルトでRepeatable Readになっている
- MySQLのRepeatable Readはファントムリードを発生させない仕組みになっている
- そのための副作用としてロストアップデートは常に注意しなければならない
- ロストアップデートには、`for update`を利用する`lock`メソッドで対策可能
- MySQLのトランザクションは、初回に発行されたSQLの時点でスナップショットを作成することにより一貫性を担保している。
- Railsで`transaction`をはってテーブルをロックしたい場合には、一番最初に`lock`を実行しなければならない。

アプリケーションが大きくなればなるほど、機能が増えてテーブルが多くなったり、色々な場所にコードが重複してまうのは結構ありがちなこと。加えて新しいエンジニアが増えてきてテーブル構成やDB知識にばらつきが出てき始めると一層上のような問題を知らないまま危険な香り漂うコードになってしまいそう。

イミュータブルデータモデルでupdateを避けた設計にする理由がある程度理解できたと思う。

[^1]: https://dev.mysql.com/doc/refman/5.6/ja/innodb-information-schema-rapidly_changing_internal_data.html