Help us understand the problem. What is going on with this article?

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

hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。 https://hmatsu47.qrunch.io/
https://hmatsu47.hatenablog.com/
alieaters
Alibaba Cloudを上手に使うためのノウハウの共有を目的としたコミュニティ
https://www.alieaters.com
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
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  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
ユーザーは見つかりませんでした