はじめに
こんにちは、ポーラ・オルビスホールディングスで内製開発エンジニアをやっている高田です。
私の所属するITプロダクト開発チーム(内製開発のチーム)で各々持ち寄った技術テーマを1人10分ずつのLT形式で発表しあう勉強会を定期的に開催しています。
今回のLT会は「webアプリケーションを開発する上での技術を扱おう（基本的な内容でもOK）」という方針で、私はトランザクション分離レベルをテーマに選びました。
LT会の中では、RDBMSの中でも今我々のチームが触れているMySQLにフォーカスしてみました。
このテーマが調べてみると結構面白くて、かつ10分のLTの中では掘り下げきれなかった内容もあったので、せっかくなので記事にしてみました。
LT会で発表したこと
LT会では一般的なDBのACID特性や、トランザクション分離レベルについて軽く説明したあと、以下の表を対象にして、ダーティリード、ファジーリード（Non-repeatable read）、ファントムリードをMySQLでそれぞれ実際に起こしてみて、その結果を発表しました。
| 分離レベル | ダーティリード | ファジーリード | ファントムリード |
|---|---|---|---|
| READ UNCOMMITTED | 〇 *1 | 〇 | 〇 |
| READ COMMITTED | × *2 | 〇 *3 | 〇 |
| REPEATABLE READ | × | × *4 | 〇 |
| SERIALIZABLE | × | × | × |
- *1 :
READ UNCOMMITTEDでダーティリードが起こる事 - *2 :
READ COMMITTEDでダーティリードが起こらないこと - *3 :
READ COMMITTEDでファジーリードが起こる事 - *4 :
REPEATABLE READでファジーリードが起こらないこと
*1 ~ *4 のダーティリード・ファジーリードの検証は上記の表の通りの結果が得られたのですが、ファントムリードはMySQLのREPEATABLE READでは発生しませんでした。
（READ COMMITTEDでは発生しました）
※ACID特性や、トランザクション分離レベルの説明についてはこの記事では割愛します
※MySQLの
REPEATABLE READでのファントムリードの成否検証のエビデンスは後に掲載しています
その後調べたこと
なぜMySQLではREPEATABLE READでファントムリードが発生しない？
MySQLではREPEATABLE READでファントムリードが発生しなかった原因を調べてみたところ、MySQLではスナップショット読み取りで独立性を確保し、ロック読み取りをする場合はさらにギャップロックやネクストキーロックといったロックの仕様を用いてファントムリードを防止しているとのことです。
また、MySQLのデフォルトの分離レベルはこのREPEATABLE READなので、デフォルトの状態で強い独立性を持っていることが分かります。
参考 : https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html
ほかのRDBMSではどう？
主要なRDBMSのデフォルトの分離レベルは以下の通り
| RDBMS | デフォルト分離レベル |
|---|---|
| MySQL | REPEATABLE READ |
| PostgreSQL | READ COMMITTED |
| Oracle | READ COMMITTED |
| SQL Server | READ COMMITTED |
PostgreSQLではデフォルトの分離レベルはREAD COMMITTEDですが、REPEATABLE READではやはりトランザクション開始時点のスナップショットを見ることでファントムリードの発生を抑止しているとのことでした。
参考 :
ここまでで
- RDBMSによってデフォルトの分離レベルは異なること
-
REPEATABLE READでもファントムリードが起こらない場合がある事
がわかりました。
実際に確かめてみたい
ここまでで実際に触って確かめたいと思ったことは以下の3つです。
- MySQLのネクストキーロックの挙動
- 各RDBMSのデフォルト分離レベルのファントムリードの成否
-
SERIALIZABLE以外にファントムリードを防げるレベルの有無
この記事では、2つ目と3つ目に対して、実際に主要RDBMSを触って検証していこうと思います。
SERIALIZABLE がファントムリードを防止できるのはSQL標準通りなので、この記事では割愛していきます。
また、1つ目の MySQLのネクストキーロックの挙動 については後日別の記事で触れていこうと思います。
各RDBMSのファントムリードの成否を検証
MySQL、PostgreSQL、Oracle、SQL Serverを対象に、デフォルトの分離レベルとREPEATABLE READを選択した時のファントムリードの成否を実施検証していきます。
MySQL
まず、MySQLのデフォルトの分離レベルがREPEATABLE READであることを確認します。
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
初期状態
mysql> SELECT * FROM items ORDER BY id;
+----+-------+
| id | value |
+----+-------+
| 1 | 90 |
| 2 | 200 |
| 3 | 300 |
| 4 | 99 |
| 5 | 999 |
+----+-------+
実施するテーブルの状態は上記の通りです。
SessionA
mysql> START TRANSACTION;
mysql> SELECT * FROM items WHERE value < 100;
+----+-------+
| id | value |
+----+-------+
| 1 | 90 |
| 4 | 99 |
+----+-------+
SessionAではvalue<100となるレコードを対象に参照していきます。
SessionB
mysql> START TRANSACTION;
mysql> INSERT INTO items (id, value) VALUES (6, 10);
mysql> COMMIT;
SessionBではvalue<100に該当するレコードを挿入してコミットします。
SessionA
mysql> SELECT * FROM items WHERE value < 100;
+----+-------+
| id | value |
+----+-------+
| 1 | 90 |
| 4 | 99 |
+----+-------+
mysql> COMMIT;
SessionBで挿入したレコードがSessionAでは参照されないことが確認できました。
これにてデフォルトの分離レベル=REPEATABLE READでファントムリードが発生しないことが確認できました。
またネクストキーロックの検証ではFOR UPDATEで読み取りロックをかけましたが、ロックをかけなくともファントム行が見えない仕様になっていることも確認できました。
（スナップショットを取っているため）
PostgreSQL
デフォルトの分離レベル(READ COMMITTED)
デフォルトの分離レベルがREAD COMMITTEDであることと、検証するテーブルの初期状態を確認します。
初期状態確認
testdb=# SHOW transaction_isolation;
transaction_isolation
-----------------------
read committed
testdb=# SELECT * FROM items ORDER BY id;
id | value
----+-------
1 | 90
2 | 200
3 | 300
4 | 99
5 | 999
テーブルの初期状態はMySQLで実施したときと同じです。
2つのセッションでファントムリードの発生を検証していきます。
SessionA
testdb=# BEGIN;
BEGIN
testdb=*# SELECT * FROM items WHERE value < 100 ORDER BY id;
id | value
----+-------
1 | 90
4 | 99
先ほどと同様にSessionAではvalue<100となるレコードを対象に参照していきます。
SessionB
testdb=# BEGIN;
BEGIN
testdb=*# INSERT INTO items (id, value) VALUES (6, 10);
INSERT 0 1
testdb=*# COMMIT;
COMMIT
SessionBではvalue<100に該当するレコードを挿入してコミットします。
SessionA
testdb=*# SELECT * FROM items WHERE value < 100 ORDER BY id;
id | value
----+-------
1 | 90
4 | 99
6 | 10
(3 rows)
testdb=*# COMMIT;
今度はSessionBで挿入したレコードがSessionAから参照できてしまいました。
分離レベルがREAD COMMITTEDであるためファントムリードが発生しました。
ファントムリードが抑止できるレベル
今度は明示的に分離レベルをREPEATABLE READにして試してみましょう。
SessionA
testdb=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
testdb=*# SELECT * FROM items WHERE value < 100 ORDER BY id;
id | value
----+-------
1 | 90
4 | 99
6 | 10
(3 rows)
先ほどと同様にSessionAではvalue<100となるレコードを対象に参照していきます。
SessionB
testdb=# BEGIN;
BEGIN
testdb=*# INSERT INTO items (id, value) VALUES (7, 20);
INSERT 0 1
testdb=*# COMMIT;
COMMIT
SessionBではvalue<100に該当するレコードを挿入してコミットします。
SessionA
testdb=*# SELECT * FROM items WHERE value < 100 ORDER BY id;
id | value
----+-------
1 | 90
4 | 99
6 | 10
(3 rows)
testdb=*# COMMIT;
COMMIT
testdb=# SELECT * FROM items WHERE value < 100 ORDER BY id;
id | value
----+-------
1 | 90
4 | 99
6 | 10
7 | 20
(4 rows)
今度はSessionBで挿入したレコードがSessionAから参照されないことが確認できました。
明示的に分離レベルをREPEATABLE READにした場合は、ファントムリードが発生しませんでした。
（もちろんCOMMITした後は追加行が参照可能になります）
SQL Server
デフォルトの分離レベル(READ COMMITTED)
まずはデフォルトの分離レベルを確認します。
確認
1> DBCC USEROPTIONS;
2> GO
Set Option
Value
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------
textsize
4096
language
us_english
dateformat
mdy
datefirst
7
lock_timeout
-1
ansi_null_dflt_on
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
分離レベルがREAD COMMITTEDであることが確認できました。
次に検証を行うテーブルを確認します。
テーブル確認
1> SELECT * FROM dbo.items ORDER BY id;
2> GO
id value
----------- -----------
1 90
2 200
3 300
4 99
5 999
これまでと同様に2つのセッションで検証していきます。
SessionA
1> BEGIN TRAN;
2> SELECT * FROM dbo.items WHERE value < 100 ORDER BY id;
3> GO
id value
----------- -----------
1 90
4 99
SessionAではvalue<100となるレコードを対象に参照していきます。
SessionB
1> BEGIN TRAN;
2> INSERT INTO dbo.items(id, value) VALUES (6, 10);
3> COMMIT;
4> GO
SessionBではvalue<100に該当するレコードを挿入してコミットします。
SessionA
1> SELECT * FROM dbo.items WHERE value < 100 ORDER BY id;
2> GO
id value
----------- -----------
1 90
4 99
6 10
(3 rows affected)
1> COMMIT;
2> GO
SessionBで挿入したレコードがSessionAから参照できてしまいました。
分離レベルがREAD COMMITTEDであるためファントムリードが発生しました。
REPEATABLE READの場合
今度は分離レベルをREPEATABLE READにして検証していきます。
SessionA
1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2> GO
1> BEGIN TRAN;
2> SELECT * FROM dbo.items WHERE value < 100 ORDER BY id;
3> GO
id value
----------- -----------
1 90
4 99
6 10
(3 rows affected)
明示的にREPEATABLE READを設定し、SessionAでvalue<100となるレコードを対象に参照していきます。
SessionB
1> BEGIN TRAN;
2> INSERT INTO dbo.items(id, value) VALUES (7, 20);
3> COMMIT;
4> GO
SessionBではvalue<100に該当するレコードを挿入してコミットします。
SessionA
1> SELECT * FROM dbo.items WHERE value < 100 ORDER BY id;
2> GO
id value
----------- -----------
1 90
4 99
6 10
7 20
(4 rows affected)
1> COMMIT;
2> GO
SessionBで挿入したレコードがSessionAから参照できてしまいました。
SQL ServerではREPEATABLE READでもファントムリードが発生してしまいました。
ファントムリードが抑止できるレベル
SQL ServerではSERIALIZABLE以外にSNAPSHOTという分離レベルを用いることでファントムリードを防げるとのことだったので、SNAPSHOTを試してみます。
SNAPSHOTはALTER DATABASEを実行して有効化する必要があるので、そこから実行していきます。
SessionA
1> ALTER DATABASE testdb SET ALLOW_SNAPSHOT_ISOLATION ON;
2> GO
1> SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
2> GO
1> BEGIN TRAN;
2> SELECT * FROM dbo.items WHERE value < 100 ORDER BY id;
3> GO
id value
----------- -----------
1 90
4 99
6 10
7 20
明示的にSNAPSHOTを設定し、SessionAでvalue<100となるレコードを対象に参照していきます。
SessionB
1> BEGIN TRAN;
2> INSERT INTO dbo.items(id, value) VALUES (8, 10);
3> COMMIT;
4> GO
SessionBではvalue<100に該当するレコードを挿入してコミットします。
SessionA
1> SELECT * FROM dbo.items WHERE value < 100 ORDER BY id;
2> GO
id value
----------- -----------
1 90
4 99
6 10
7 20
(4 rows affected)
1> COMMIT;
2> GO
今度はSessionBで挿入したレコードがSessionAから参照されないことが確認できました。
SQL Serverでは分離レベルをSNAPSHOTにした場合に、ファントムリードを防止することができました。
Oracle
デフォルトの分離レベル(READ COMMITTED)
まずは検証を行うテーブルを確認します。
テーブルの状態
SQL> SELECT * FROM items ORDER BY id;
ID VALUE
---------- ----------
1 90
2 200
3 300
4 99
5 999
これまでと同様に、2つのセッションを使って検証を進めていきます。
（Oracleではトランザクションは暗黙的に開始されるため BEGIN に相当するクエリを省略できます。）
SessionA
SQL> SELECT * FROM items WHERE value < 100 ORDER BY id;
ID VALUE
---------- ----------
1 90
4 99
SessionAでvalue<100となるレコードを対象に参照していきます。
SessionB
SQL> INSERT INTO items (id, value) VALUES (6, 10);
1 row created.
SQL> COMMIT;
Commit complete.
SessionBではvalue<100に該当するレコードを挿入してコミットします。
SessionA
SQL> SELECT * FROM items WHERE value < 100 ORDER BY id;
ID VALUE
---------- ----------
1 90
4 99
6 10
SessionBで挿入したレコードがSessionAから参照できてしまいました。
やはりデフォルトのREAD COMMITTEDではファントムリードが発生してしまいました。
ファントムリードが抑止できるレベル
OracleではREPEATABLE READがサポートされていませんでした。
READ ONLYがスナップショットを取るとのことなので、READ ONLYで検証してみました。
SessionA
SQL> SET TRANSACTION READ ONLY;
Transaction set.
SQL> SELECT * FROM items WHERE value < 100 ORDER BY id;
ID VALUE
---------- ----------
1 90
4 99
6 10
明示的にREAD ONLYを設定し、SessionAでvalue<100となるレコードを対象に参照していきます。
SessionB
SQL> INSERT INTO items (id, value) VALUES (7, 20);
1 row created.
SQL> COMMIT;
Commit complete.
SessionBではvalue<100に該当するレコードを挿入してコミットします。
SessionA
SQL> SELECT * FROM items WHERE value < 100 ORDER BY id;
ID VALUE
---------- ----------
1 90
4 99
6 10
今度はSessionBで挿入したレコードがSessionAから参照されないことが確認できました。
READ ONLYを設定することでファントムリードが抑止されました。
もちろんCOMMITしてトランザクションを一度閉じればid=7が見えるようになります
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM items WHERE value < 100 ORDER BY id;
ID VALUE
---------- ----------
1 90
4 99
6 10
7 20
わかったこと
各RDBMSのデフォルト分離レベルでのファントムリードの成否がわかりました。
MySQLではデフォルトの分離レベルでファントムリードが発生しないこと、
ほかのRDBMSはデフォルトの分離レベルではファントムリードが発生するものの、
SERIALIZABLE以外にもファントムリードを抑止できる分離レベルが実装されていることが分かりました。
まとめ
- RDBMSごとにデフォルトの分離レベルも、
REPEATABLE READ選択時の挙動も、ファントムリードの防ぎ方も異なる
各RDBMSのデフォルトの分離レベルとファントムリードの防ぎ方をまとめると以下のようになります。
| RDBMS | デフォルト分離レベル | デフォルトでのファントムリード | ファントムリードの抑止手段 |
|---|---|---|---|
| MySQL | REPEATABLE READ |
発生しない | デフォルトで抑止 |
| PostgreSQL | READ COMMITTED |
発生する |
REPEATABLE READかSERIALIZABLEの分離レベル |
| SQL Server | READ COMMITTED |
発生する |
SNAPSHOTかSERIALIZABLEの分離レベル |
| Oracle | READ COMMITTED |
発生する |
READ ONLYかSERIALIZABLEの分離レベル |
感想
RDBMSの個性がとてもよく現れる検証となったと思います。
この結果に優劣はなく、あくまで独立性と並行性のバランス調整の結果であるため、この調査・検証の結果をプロジェクト・プロダクトの特性に合ったRDBMSを選定する視点として活かしていきたいと思います。
また、webアプリケーションの開発を行う身としては、RESTful × ステートレスな設計と実装を意識して、DBに整合性を丸投げしないこと、DBの内部実装に依存しないことを引き続き心がけていきたいと思いました。
あとこういう検証をしてみると普段触らないRDBMSを触る機会になって面白いですね。