Edited at

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

More than 1 year has passed since last update.

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

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の動作については軽く触れられている程度です。