MySQLのトランザクションと分離レベル

非常に基本的なことなのですが、データベースのトランザクションの分離レベルの話をしていてちょっと混乱してしまったことがありました。ので、基本に立ち返り、トランザクション周りを整理しようと思ったわけです。

環境

本記事の環境は以下のとおりです。

• MySQL on docker
  • docker client version : 19.03.8
  • docker engine version : 19.03.8
  • mysql image version : 8.0.18

トランザクション制御のステートメント

MySQLでは次のステートメントでトランザクションを制御できます。

• START TRANSACTION か、 BEGIN により新しいトランザクションを開始
• COMMIT でトランザクションをコミットし、変更を永続化
• ROLLBACK でトランザクションをロールバックし、変更を取り消す

実際に使ってみるとこうなります。

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

mysql> SELECT * FROM books;
+----+-------------------------+--------------------------+---------------+
| id | name                    | publisher                | author        |
+----+-------------------------+--------------------------+---------------+
|  1 | Refactoring 2nd Edition | OBJECT TECHNOLOGY SERIES | Martin Fowler |
|  2 | Test Driven Development | Addison-Wesley           | Kent Beck     |
+----+-------------------------+--------------------------+---------------+
2 rows in set (0.00 sec)

mysql> DELETE FROM books WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM books;
+----+-------------------------+--------------------------+---------------+
| id | name                    | publisher                | author        |
+----+-------------------------+--------------------------+---------------+
|  1 | Refactoring 2nd Edition | OBJECT TECHNOLOGY SERIES | Martin Fowler |
+----+-------------------------+--------------------------+---------------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM books;
+----+-------------------------+--------------------------+---------------+
| id | name                    | publisher                | author        |
+----+-------------------------+--------------------------+---------------+
|  1 | Refactoring 2nd Edition | OBJECT TECHNOLOGY SERIES | Martin Fowler |
|  2 | Test Driven Development | Addison-Wesley           | Kent Beck     |
+----+-------------------------+--------------------------+---------------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

分離レベルとリード現象

トランザクションには分離レベルを設定できます。分離レベルとは、複数のトランザクションが互いに与える影響の度合いのことで、ACID特性のI（隔離性）に該当します。一般的なデータベースシステムでは複数の分離レベルを設定値として持っていてトランザクションごとに分離レベルを設定できます。

分離レベルを変更するには SET TRANSACTION ステートメントを使用します。基本的な使い方は次のとおりです。[level] にはいずれかの分離レベルを指定します。詳しくは公式のドキュメントを参照してください。(https://dev.mysql.com/doc/refman/5.6/ja/set-transaction.html)

SET TRANSACTION ISOLATION LEVEL [level]

MySQLで設定できる分離レベルは４つになります。以下の順序はMySQLのドキュメントに示されている使用される頻度順です。

1. REPEATABLE READ
2. READ COMMITTED
3. READ UNCOMMITTED
4. SERIALIZABLE

それぞれの分離レベルでは異なるリード現象が発生します。リード現象とは、同時に走る２つ以上のトランザクションが互いに変更したデータを読み取る時の現象のことです。ANSI/ISO standard SQL 92 では次に示す３つのリード現象が定義されています。

名前	概要
ダーティーリード	別のトランザクション内でコミットされていないデータを読み取る
ノンリピータブルリード	トランザクション内で２回以上同じ行を取得した際、行の値が異なる結果を読み取る
ファントムリード	トランザクション内で２回以上同じ検索結果を取得した際、行が追加・削除された結果を読み取る

MySQLでの分離レベルとリード現象の対応は次のとおりです。

分離レベル	ダーティーリード	ノンリピータブルリード	ファントムリード
READ UNCOMMITTED	○	○	○
READ COMMITTED	×	○	○
REPEATABLE READ	×	×	×
SERIALIZABLE	×	×	×

※ × 発生しない ○ 発生する

ダーティーリード

ダーティーリードは別のトランザクション内でコミットされていないデータを読み取る現象です。他のリード現象と比べるとトランザクションの隔離性が一番低い現象です。分離レベルが READ UNCOMMITTED の時にダーティーリードが発生します。実際にやってみた結果を次に示します。

/* Transaction 1 */
mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

/* Transaction 1 */
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

/* Transaction 1 */
mysql> SELECT * FROM books;
+----+-------------------------+--------------------------+---------------+
| id | name                    | publisher                | author        |
+----+-------------------------+--------------------------+---------------+
|  1 | Refactoring 2nd Edition | OBJECT TECHNOLOGY SERIES | Martin Fowler |
|  2 | Test Driven Development | Addison-Wesley           | Kent Beck     |
|  3 | Building Microservices  | O'Reilly Media           | Sam Newman    |
+----+-------------------------+--------------------------+---------------+
3 rows in set (0.00 sec)

                            /* Transaction 2 */
                            mysql> BEGIN;
                            Query OK, 0 rows affected (0.00 sec)

                            /* Transaction 2 */
                            mysql> DELETE FROM books WHERE id = 3;
                            Query OK, 1 row affected (0.01 sec)

/* Transaction 1 */
mysql> SELECT * FROM books;
+----+-------------------------+--------------------------+---------------+
| id | name                    | publisher                | author        |
+----+-------------------------+--------------------------+---------------+
|  1 | Refactoring 2nd Edition | OBJECT TECHNOLOGY SERIES | Martin Fowler |
|  2 | Test Driven Development | Addison-Wesley           | Kent Beck     |
+----+-------------------------+--------------------------+---------------+
2 rows in set (0.00 sec)

ノンリピータブルリード

ノンリピータブルリードはトランザクション内で２回以上同じ行を取得した際、行の値が異なる結果を読み取る現象です。分離レベルが READ COMMITTED, READ UNCOMMITTED の時にノンリピータブルリードが発生します。ダーティーリードと似たような現象ですが、ダーティーリードではコミットされていないデータを読み取る点に対し、ノンリピータブルリードではコミットされたデータを読み取る点で動作が異なります。

実際にやってみた結果を次に示します。

/* Transaction 1 */
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

/* Transaction 1 */
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

/* Transaction 1 */
mysql> SELECT * FROM customers;
+----+--------------+---------+
| id | name         | version |
+----+--------------+---------+
|  1 | Aurel Croson |       4 |
+----+--------------+---------+
1 row in set (0.00 sec)

                            /* Transaction 2 */
                            mysql> BEGIN;
                            Query OK, 0 rows affected (0.00 sec)

                            /* Transaction 2 */
                            mysql> UPDATE customers SET version = 5 WHERE id = 1;
                            Query OK, 1 row affected (0.00 sec)
                            Rows matched: 1  Changed: 1  Warnings: 0

                            /* Transaction 2 */
                            mysql> COMMIT;
                            Query OK, 0 rows affected (0.01 sec)

/* Transaction 1 */
mysql> SELECT * FROM customers;
+----+--------------+---------+
| id | name         | version |
+----+--------------+---------+
|  1 | Aurel Croson |       5 |
+----+--------------+---------+
1 row in set (0.00 sec)

ファントムリード

ファントムリードはトランザクション内で２回以上同じ検索結果を取得した際、行が追加・削除された結果を読み取る現象です。分離レベルが READ COMMITTED, READ UNCOMMITTED の時にファントムリードが発生します。（※MySQLではREPEATABLE READ の分離レベルでもファントムリードが発生しません。）

実際にやってみた結果を次に示します。

/* Transaction 1 */
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

/* Transaction 1 */
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

/* Transaction 1 */
mysql> SELECT * FROM customers;
+----+--------------+---------+
| id | name         | version |
+----+--------------+---------+
|  1 | Aurel Croson |       5 |
+----+--------------+---------+
1 row in set (0.00 sec)

                            /* Transaction 2 */
                            mysql> BEGIN;
                            Query OK, 0 rows affected (0.00 sec)

                            /* Transaction 2 */
                            mysql> INSERT INTO customers VALUES (2, 'Griz Dradey', 1);
                            Query OK, 1 row affected (0.00 sec)

                            /* Transaction 2 */
                            mysql> COMMIT;
                            Query OK, 0 rows affected (0.02 sec)

/* Transaction 1 */
mysql> SELECT * FROM customers;
+----+--------------+---------+
| id | name         | version |
+----+--------------+---------+
|  1 | Aurel Croson |       5 |
|  2 | Griz Dradey  |       1 |
+----+--------------+---------+
2 rows in set (0.00 sec)

おわりに

分離レベルの事を調査していたらリード現象になるものを知り、思っていたより整理に時間がかかりました。データベースにはまだまだ自分の知らない領域があるので引き続き調査して行きたいと思っています。

参考

• MySQL 5.6 リファレンスマニュアル | 13.3.1 START TRANSACTION、COMMIT、および ROLLBACK 構文
• MySQL 5.6 リファレンスマニュアル | 13.3.6 SET TRANSACTION 構文
• MySQL 5.6 リファレンスマニュアル | 用語集 ファントム
• wiki | Isolation (database systems)