MySQLのデフォルトトランザクション分離レベルはREPEATABLE READだけど…

こちらのエントリを見て思い出しました。

 * **[アプリケーションエンジニアが知っておくべきMySQLのロック](http://qiita.com/tikamoto/items/f867050ff77d06a94215)**

MySQL（InnoDB）のデフォルトトランザクション分離レベルは表題の通り**REPEATABLE READ**ですが、その場合でも、**UPDATEとDELETEはREAD COMMITTED相当の動作**になります。

# 実験

以下のようなアプリケーションがあったとします。

 * 入会申請の都度、テーブルにレコードがINSERTされる。
 * 受付処理を行うときに、当該レコードをUPDATEして処理済みにする。

※説明を簡単にするために、入会申請（entry）テーブルには、通し番号（entry_no）・名前（name）・処理フラグ（proc_flag）の3つのカラムしか持たないものとします。

処理前に、こんな感じでレコードが入っているとします。

```mysql:処理前の状態
mysql> SELECT * FROM iso_level_test.entry;
+----------+--------------+-----------+
| entry_no | name         | proc_flag |
+----------+--------------+-----------+
|        1 | 鈴木一郎     |         1 |
|        2 | 佐藤二郎     |         0 |
|        3 | 吉田三郎     |         0 |
+----------+--------------+-----------+
3 rows in set (0.00 sec)
```

※鈴木一郎さんはすでに入会受付処理が完了しています。

ここで、入会受付処理をすべく、トランザクションAを開始します。

```mysql:トランザクションA
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM iso_level_test.entry WHERE proc_flag=0;
+----------+--------------+-----------+
| entry_no | name         | proc_flag |
+----------+--------------+-----------+
|        2 | 佐藤二郎     |         0 |
|        3 | 吉田三郎     |         0 |
+----------+--------------+-----------+
2 rows in set (0.00 sec)
```

このSELECT結果を元に、佐藤二郎さんと吉田三郎さんの入会処理を行います（詳細は省略）。

入会処理と並行して、トランザクションBにて、新たな入会申請レコードがINSERTされたとします。

```mysql:トランザクションB
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO iso_level_test.entry SET name='山田四郎', proc_flag=0;
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
```

ここで、トランザクションAの処理の続きで、処理フラグのUPDATEを行うと…

```mysql:トランザクションA
mysql> UPDATE iso_level_test.entry SET proc_flag=1 WHERE proc_flag=0;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM iso_level_test.entry;
+----------+--------------+-----------+
| entry_no | name         | proc_flag |
+----------+--------------+-----------+
|        1 | 鈴木一郎     |         1 |
|        2 | 佐藤二郎     |         1 |
|        3 | 吉田三郎     |         1 |
|        4 | 山田四郎     |         1 |
+----------+--------------+-----------+
4 rows in set (0.00 sec)
```

入会処理が済んでいない、**山田四郎さんの処理フラグもUPDATE**されてしまいました（巻き込み事故発生!!）。

UPDATEがREAD COMMITTED相当（UPDATE時点でCOMMITされている情報が読み込まれる）で動作したことが分かります。

これを防ぐには、最初のSELECTを**SELECT…FOR UPDATE**または**SELECT…LOCK IN SHARE MODE**に変更し、ロックを掛けてからUPDATEするか（ロストアップデートの対処と同じですね）、SELECTしたレコードのコレクションについて、1行毎にUPDATEを行います。

# おまけ

実は、**SELECT…FOR UPDATEもREAD COMMITTED相当で動作**します。

最初に戻って、

```mysql:処理前の状態
mysql> SELECT * FROM iso_level_test.entry;
+----------+--------------+-----------+
| entry_no | name         | proc_flag |
+----------+--------------+-----------+
|        1 | 鈴木一郎     |         1 |
|        2 | 佐藤二郎     |         0 |
|        3 | 吉田三郎     |         0 |
+----------+--------------+-----------+
3 rows in set (0.00 sec)
```

先ほどと同様、トランザクションAでSELECTします。

```mysql:トランザクションA
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM iso_level_test.entry WHERE proc_flag=0;
+----------+--------------+-----------+
| entry_no | name         | proc_flag |
+----------+--------------+-----------+
|        2 | 佐藤二郎     |         0 |
|        3 | 吉田三郎     |         0 |
+----------+--------------+-----------+
2 rows in set (0.00 sec)
```

そして、トランザクションBでINSERTします。

```mysql:トランザクションB
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO iso_level_test.entry SET name='山田四郎', proc_flag=0;
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
```

トランザクションAに戻って、普通にSELECTすると変化はありませんが…

```mysql:トランザクションA
mysql> SELECT * FROM iso_level_test.entry WHERE proc_flag=0;
+----------+--------------+-----------+
| entry_no | name         | proc_flag |
+----------+--------------+-----------+
|        2 | 佐藤二郎     |         0 |
|        3 | 吉田三郎     |         0 |
+----------+--------------+-----------+
2 rows in set (0.00 sec)
```

SELECT…FOR UPDATEをすると、トランザクションBでCOMMITしたレコードが見えてしまいます。

```mysql:トランザクションA
mysql> SELECT * FROM iso_level_test.entry WHERE proc_flag=0 FOR UPDATE;
+----------+--------------+-----------+
| entry_no | name         | proc_flag |
+----------+--------------+-----------+
|        2 | 佐藤二郎     |         0 |
|        3 | 吉田三郎     |         0 |
|        4 | 山田四郎     |         0 |
+----------+--------------+-----------+
3 rows in set (0.00 sec)
```

もっとも、同一トランザクション内で、SELECT後にあらためて同じ範囲のレコードをSELECT…FOR UPDATEすることはないと思いますが…。

より詳しい解説は（ちょっと古いですが）Oracleの奥野さんが**「MySQL Casual Advent Calendar 2013」**（Advent Calendarの元エントリは既にありません）の20日目、以下の記事に書かれています。

 * **[InnoDBのREPEATABLE READにおけるLocking Readについての注意点（漢(オトコ)のコンピュータ道）](http://nippondanji.blogspot.jp/2013/12/innodbrepeatable-readlocking-read.html)**

※ここで取り上げたUPDATE・DELETEの動作については軽く触れられている程度です。