MySQL

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

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

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

実験

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

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

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

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

処理前の状態
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を開始します。

トランザクション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されたとします。

トランザクション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を行うと…

トランザクション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> 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します。

トランザクション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します。

トランザクション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すると変化はありませんが…

トランザクション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したレコードが見えてしまいます。

トランザクション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 Avdent Calendar 2013」(Advent Calendarの元エントリは既にありません)の20日目、以下の記事に書かれています。

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