36
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
36
Help us understand the problem. What are the problem?