MySQLのトランザクション分離レベルについてまとめる

今更ながらトランザクション分離レベルについて実際に手を動かして確認したことについてまとめます。

既にまとめ記事が多数あるので目新しいものはありません。

環境

# mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL

トランザクション分離レベルについて

公式にはInnoDBの4つの分離レベルが記載されています。

1. READ UNCOMMITED
2. READ COMMITTED
3. REPEATABLE READ <- デフォルトはこれ
4. SERIALIZABLE

4になるほど厳密になります。

分離レベルの違いによって起こること

レベルごとにダーティリード、ファジーリード、ファントムリードが発生します。

分離レベル	ダーティリード	ファジーリード	ファントムリード
READ UNCOMMITED	○	○	○
READ COMMITTED	-	○	○
REPETABLE READ	-	-	○
SERIALIZABLE	-	-	-

それぞれ影響度合いは違いますが、同時接続した場合に読み取りのタイミングによって別のユーザが行った操作が影響する可能性があります。
下記にそれぞれの現象について記載します。

ダーティリード

トランザクションBでコミットされていないデータをトランザクションAで読み取ってしまう問題が起きる。

ファジーリード/ノンリピータブルリード

トランザクションAでデータを複数回読み取っている途中で、トランザクションBでデータを更新してコミットした場合、トランザクションAで違う結果のデータを読み取ってしまう問題が起きる。

ファントムリード

トランザクションAで一定範囲のレコードに対して処理を行っている途中で、トランザクションBでデータを追加・削除してコミットした場合、トランザクションAで幻影のようにデータが反映されるため、処理の結果が変わってしまう問題が起きる。

InnoDBではファントムリードはREPETABLE READでは起こらない

REPEATABLE READについて

まずは公式から

同じトランザクション内の Consistent reads は、最初の読取りによって確立された snapshot を読み取ります。 つまり、同じトランザクション内で複数のプレーン (非ロック) SELECT ステートメントを発行すると、これらの SELECT ステートメントも互いに一貫性が保たれます。

つまりトランザクションの開始時にスナップショットを取得して、コミットするまでは最初のスナップショットに基づいて操作するということ。

ここでConsistent reads（一貫性読み取り）という言葉が出てきたので、また公式を参照します。

snapshot 情報を使用して、同時に実行されている他のトランザクションによって実行された変更に関係なく、ある時点に基づいてクエリー結果を表示する読取り操作。
一貫性読み取りはアクセスするテーブルでロックを設定しないので、テーブルで一貫性読み取りが実行されている間、ほかのセッションはそれらのテーブルを自由に変更できます。

読み取り時にロックはしないため、ロックが必要な場合はSELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODEを使う必要がある。
ロックした場合は、

• インデックススキャンの場合はインデックスレコードのみをロックする（レコードロック）
• 範囲検索やフルスキャンの場合はギャップロック、ネクストキーロックをする

ギャップロックやネクストキーロックという単語が出てきたため、ここからは動作で確認します。

まずは初期データの用意

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 10 |    10 |
| 20 |    20 |
| 30 |    30 |
+----+-------+

最後までこのデータをもとに動作確認します。

まずはギャップロックについて確認します。
ターミナルAとBでそれぞれでMySQLクライアントを起動します。
それではAで排他ロックを獲得します。

A

mysql>begin;
mysql> select * from test where id=15 for update;
Empty set (0.00 sec)

次にBでデータを登録します。

B

mysql>begin;
mysql> insert into test value(11, 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ロック解除待ちでタイムアウトになります。
結論から言うと、ギャップロックによって登録できないようになっています。

ギャップロックとはインデックスレコード間（今回は10<n<20）のid=11~19にデータを挿入することを阻止するために11~19をロックする動作のことです。
そのためid=9のデータは登録できるが、id=11のデータは登録できません。

B

mysql> insert into test value(9, 20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test value(11, 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

次にネクストキーロックについてみていきます。
こちらもAで排他ロックを獲得して、ロックの状況を見てみます。

A

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

mysql> select * from test where id > 10 for update;

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
|        49 | test        | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
|        49 | test        | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
|        49 | test        | PRIMARY    | RECORD    | X         | GRANTED     | 20                     |
|        49 | test        | PRIMARY    | RECORD    | X         | GRANTED     | 30                     |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+

LOCK_MODEがXとは排他ロックを意味しております。
続いてBでデータを登録・更新します。

B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set value=50 where id=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test value(19, 30);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test set value=50 where id=30;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test value(29, 30);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test value(31, 30);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

id=20,30は更新できず、それ以外の新規データも登録できません。
つまり10<nがロックされている状態です。
ここのid=20,30が更新できない状態がネクストキーロックです。

なぜ、ギャップロックとネクストキーロックが起こるのかについてまでは調べていないですが、ギャップロックのおかげ後ほど動作確認するファントムの発生を防いでいるような印象を受けます。

READ COMMITTEDについて

まずは公式から

各読取り一貫性は、同じトランザクション内であっても、独自の新しいスナップショットを設定して読み取ります。 読取り一貫性の詳細は、セクション15.7.2.3「一貫性非ロック読み取り」 を参照してください。
ロック読取り (FOR UPDATE または FOR SHARE を使用した SELECT)、UPDATE ステートメントおよび DELETE ステートメントの場合、InnoDB はインデックスレコードのみをロックし、その前のギャップはロックしないため、ロックされたレコードの横に新しいレコードを自由に挿入できます。 ギャップロックは、外部キー制約チェックおよび重複キーチェックにのみ使用されます。

スナップショットの読み取りとギャップロックについてがREPEATABLE READとの違いでしょうか？？

こちらも手元で確認していきます。
まず初期データに戻して、ターミナルA&Bでトランザクション分離レベルを変更します。

A&B

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | READ-COMMITTED          |
+--------------------------------+-------------------------+
1 row in set (0.01 sec)

次にAとBでSELECTします。

A&B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 10 |    10 |
| 20 |    20 |
| 30 |    30 |
+----+-------+
3 rows in set (0.00 sec)

次にターミナルBでデータを更新します。(コミットはしません)

B

mysql> update test set value=50 where id=20;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

この状態でAでSELECTします。

A

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 10 |    10 |
| 20 |    20 |
| 30 |    30 |
+----+-------+
3 rows in set (0.00 sec)

まだ変更は反映されていないです。
ここまではBの変更がAに影響を及ぼすような現象は見受けられません。

次にBでコミットします。

B

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

この状態でAでSELECTします。

A

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 10 |    10 |
| 20 |    50 |
| 30 |    30 |
+----+-------+
3 rows in set (0.00 sec)

先ほどBで更新したid=20のデータが変更されています。(ファジーリード)
トランザクション内でスナップショットを取り直して、そこから読み取るためファジーリードが発生するのでしょうか。

このままファントムリードについてみていきます。
Aで合計値を取得します。

A

mysql> select count(id), sum(value) from test;
+-----------+------------+
| count(id) | sum(value) |
+-----------+------------+
|         3 |         90 |
+-----------+------------+
1 row in set (0.01 sec)

次にBでデータを挿入します。

B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(40, 100);
Query OK, 1 row affected (0.00 sec)

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

再度Aで合計値を取得します。

A

mysql> select count(id), sum(value) from test;
+-----------+------------+
| count(id) | sum(value) |
+-----------+------------+
|         4 |        190 |
+-----------+------------+
1 row in set (0.01 sec)

するとAではトランザクション開始時点ではなかったデータが結果に反映されています。（ファントムリード）
ここまでは排他ロックなど設定しない状態で見ていきました。
ついでにFOR UPDATEして動作確認したいと思います。

公式ではREPEATABLE READと違い、ギャップロックしないと記載されています。

まずAでロックを獲得します。

A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id between 12 and 19 for update;
Empty set (0.00 sec)

REPEATABLE READならid=11~19までギャップロックされているはずです。
READ COMMITTEDではどうでしょうか。

続いてBでデータを登録します。

B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(11, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(19, 10);
Query OK, 1 row affected (0.00 sec)

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

ロック解除待ちにならず、登録できました。
公式に記載ある通りにインデックスレコードのみロックして、ギャップはロックしないことを確認できました。
最後にAでデータを確認します。

A

mysql> select * from test where id between 12 and 19 for update;
+----+-------+
| id | value |
+----+-------+
| 19 |    10 |
+----+-------+
1 rows in set (0.00 sec)

もちろんファントムとしてid=19のデータが結果に表示されました。

READ UNCOMMITTEDについて

こちらも公式から

SELECT ステートメントは非ロックの方法で実行されますが、以前のバージョンの行が使用される可能性もあります。 そのため、この分離レベルを使用すると、このような読み取りには一貫性がありません。 これは、ダーティー読み取りとも呼ばれます。 そうでなければ、この分離レベルは READ COMMITTED のように機能します。

この分離レベルにするとファジーリード、ファントムリード＋ダーティリードも発生します。
では、早速みていきます。

まず初期データに戻して、ターミナルA&Bでトランザクション分離レベルを変更します。

A&B

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | READ-UNCOMMITTED        |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

AとBでSELECTします。

A&B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 10 |    10 |
| 20 |    20 |
| 30 |    30 |
+----+-------+
3 rows in set (0.00 sec)

次にBでデータを登録します。
コミットはしません。

B

mysql> insert into test values(40, 40);
Query OK, 1 row affected (0.01 sec)

そしてAで再度SELECTします。

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 10 |    10 |
| 20 |    20 |
| 30 |    30 |
| 40 |    40 |
+----+-------+
4 rows in set (0.00 sec)

Bでコミットしていない操作がAの結果に出力されました。
つまり、ダーティリードが発生しました。

Bではコミットしていないので、rollbackすればAの結果からも消えます。

B

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

A

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 10 |    10 |
| 20 |    20 |
| 30 |    30 |
+----+-------+
3 rows in set (0.00 sec)

SERIALIZABLEについて

まずは公式から

このレベルは REPEATABLE READ と似ていますが、autocommit が無効になっている場合、InnoDB はすべてのプレーン SELECT ステートメントを SELECT ... FOR SHARE に暗黙的に変換します。 autocommit が有効な場合、SELECT は独自のトランザクションです。 したがって、読み取り専用であることがわかっているため、一貫性のある (非ロック) 読み取りとして実行された場合は直列化することができ、ほかのトランザクションのためのブロックは必要ありません。

つまり、読み取りでは必ず共有ロックモードになると記載されております。

ここからは動作をみていきます。

まず初期データに戻して、ターミナルA&Bでトランザクション分離レベルを変更します。

A&B

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | SERIALIZABLE            |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

次にAでSELECTします。

A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 10 |    10 |
| 20 |    20 |
| 30 |    30 |
+----+-------+
3 rows in set (0.00 sec)

ここでロックの獲得状況を見てみます。

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
|        50 | test        | NULL       | TABLE     | IS        | GRANTED     | NULL                   |
|        50 | test        | PRIMARY    | RECORD    | S         | GRANTED     | supremum pseudo-record |
|        50 | test        | PRIMARY    | RECORD    | S         | GRANTED     | 10                     |
|        50 | test        | PRIMARY    | RECORD    | S         | GRANTED     | 20                     |
|        50 | test        | PRIMARY    | RECORD    | S         | GRANTED     | 30                     |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+

LOCK_MODEがSになっており、これは共有ロックを表しています。

共有ロックとは、他のトランザクションからの読み取りと共有ロックを獲得することを許可するけど、書き込みは許可しない状態のことです。

次にBから登録を試します。

B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(15, 15);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Aが共有ロックをしているため、Bはロック待ちでタイムアウトしました。
今度はBでSELECTを試します。

B

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 10 |    10 |
| 20 |    20 |
| 30 |    30 |
+----+-------+
3 rows in set (0.00 sec)

取得できました。
ついでにロックの状況を見てみます。

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
|        51 | test        | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
|        51 | test        | PRIMARY    | RECORD    | S         | GRANTED     | supremum pseudo-record |
|        51 | test        | PRIMARY    | RECORD    | S         | GRANTED     | 10                     |
|        51 | test        | PRIMARY    | RECORD    | S         | GRANTED     | 20                     |
|        51 | test        | PRIMARY    | RECORD    | S         | GRANTED     | 30                     |
|        50 | test        | NULL       | TABLE     | IS        | GRANTED     | NULL                   |
|        50 | test        | PRIMARY    | RECORD    | S         | GRANTED     | supremum pseudo-record |
|        50 | test        | PRIMARY    | RECORD    | S         | GRANTED     | 10                     |
|        50 | test        | PRIMARY    | RECORD    | S         | GRANTED     | 20                     |
|        50 | test        | PRIMARY    | RECORD    | S         | GRANTED     | 30                     |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+

先ほどとは違いセッションA,Bの両方で共有ロックを獲得しているのがわかります。
ここでAをコミットして、Bで登録を試します。

A

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

B

mysql> insert into test values(15, 15);
Query OK, 1 row affected (0.00 sec)

Aの共有ロックが無くなったため、Bの書き込みが正常に行われました。

このようにSERIALIZABLEでは読み取る行すべてに共有ロックをかけるため、ファントムリード、ファジーリード、ダーティリードが発生する可能性がありません。

所感

ここまで見るとREPEATABLE READが丁度いい分離レベルなんだと理解できた気がします。

• ダーティリード、ファジーリード、ファントムリードを発生しない
• 共有ロック、排他ロックを任意でかけられる
• ただし、ギャップロックとネクストキーロックは起こるため想定外のロックに注意する
  といった形でしょうか。

参考

https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html