DBのトランザクション分離レベルを勉強していた際、これは「他のトランザクションの影響を受けない」のか「他のトランザクションに影響を与えない」のか(あるいはその両方なのか)が気になりました。
結果を推測できるほどの知識はまだ全然ないため、ひとまず実際に MySQL (InnoDB) で試して確認しました。
※他のDBだと異なる結果になるかもしれません。
TL;DR
「他のトランザクションの影響を受けない」だけのようです。
- 他のトランザクションの分離レベルが低くても、自分の処理内容は自分の分離レベルに応じて安全が守られる(たぶん)
- 自分の読み込みのためにスナップショットを作ることがある
- 必要に応じて排他制御が働き、相手か自分自身が処理を待機する
- 自分の処理内容が他のトランザクションから見られてしまうことがある
- 実際に
SERIALIZABLE
でのコミット前の内容がREAD UNCOMMITTED
から見えた
- 実際に
実験準備
サンプルデータ
MySQL のリファレンスマニュアルから適当に持ってきました。
-- https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
サーバとクライアント
MySQLサーバを用意し、2つのクライアントから接続してクエリを投入できるようにします。
簡単に試せるといいので、今回はDockerでコンテナをひとつ立ち上げてその中で完結させます。
# サーバを起動
docker run --rm -d --name mysql-test-server \
-e MYSQL_ROOT_PASSWORD= \
-e MYSQL_ALLOW_EMPTY_PASSWORD=yes \
-e MYSQL_DATABASE=tx_test_db \
mysql:8.0.27
# (起動完了するのを確認)
docker logs -f mysql-test-server
# サンプルデータを投入
docker exec -i mysql-test-server mysql tx_test_db < samples.sql
# 同じコンテナ内でクライアントを実行して接続(別のターミナルからが良い)
docker exec -it mysql-test-server mysql tx_test_db
# --> 自由にクエリを投入して実験する
# サーバを停止し、コンテナを破棄
docker stop mysql-test-server
実験
以下では connection A と connection B から並行してクエリを投入します。
同じ分離レベル(確認)
READ COMMITTED
同士
まずはよくあるパターンを試しておきます。
READ COMMITTED
は名前通り、コミットされたデータを読みます。言い換えるとコミット前のデータは読むことがありません。
これは**非再現リード(non-repeatable read)**という現象が起こります。行儀よくひとりずつトランザクションを実行した際は、トランザクション内で連続して SELECT
すると常に同じ結果が返ります。しかし他の人が並行作業していると途中でデータが書き換わり、連続して SELECT
してもタイミング次第で異なる結果となり得ます。
-- connection A / connection B --
SET SESSION TRANSACTION
ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION
ISOLATION LEVEL READ COMMITTED;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#1)
BEGIN;
UPDATE t SET b = 2 WHERE b != 2;
--> 2 rows affected
--> Rows matched: 2 Changed: 2 Warnings: 0
BEGIN;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#2)
COMMIT;
SELECT * FROM t WHERE b = 2;
--> 5 rows in set (#3) !! non-repeatable read !!
COMMIT;
A側はトランザクション内で2回 SELECT
しました。B側がトランザクション内で UPDATE
しただけではA側には影響が出ませんでしたが( #2
)、B側がコミットするとA側に影響が出ました( #3
)。
REPEATABLE READ
同士
前節の現象は REPEATABLE READ
では起きないことも確認しておきます。
-- connection A / connection B --
SET SESSION TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#1)
BEGIN;
UPDATE t SET b = 2 WHERE b != 2;
--> 2 rows affected
--> Rows matched: 2 Changed: 2 Warnings: 0
BEGIN;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#2)
COMMIT;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#3)
COMMIT;
SELECT * FROM t WHERE b = 2;
--> 5 rows in set (#4)
B側でコミットした後の #3
でも #2
と同じ結果が返りました。トランザクションを終えた後の #4
では、B側のコミットが反映されています。
同じ結果が返る仕組みは、トランザクションの最初の #2
でスナップショットを作ってそこから読み取っているからだそうです。(一方で READ COMMITTED
では毎回スナップショットを作り直していて、 #2
と #3
とで異なっています)
異なる分離レベル
SERIALIZABLE
と READ UNCOMMITTED
(1)
これらは分離レベルの最も高いものと最も低いものです。 SERIALIZABLE
なトランザクションの中の作業は恐らく外部の影響を受けないでしょうが、逆に中の作業が外部に漏れる(影響を与える)ことがあるのかが気になります。
これまでと同じものをやってみます。本節はA側( SELECT
)が SERIALIZABLE
、B側( UPDATE
)が READ UNCOMMITTED
です。
-- connection A / connection B --
SET SESSION TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#1)
BEGIN;
UPDATE t SET b = 2 WHERE b != 2;
--> 2 rows affected
--> Rows matched: 2 Changed: 2 Warnings: 0
BEGIN;
SELECT * FROM t WHERE b = 2;
--> (wait) (#2)
COMMIT;
--> 5 rows in set (#2')
COMMIT;
A側は SELECT
が待機状態になり、B側のトランザクションが完了してから UPDATE
反映後の結果が返りました。
【理屈】
SERIALIZABLE
で普通の SELECT
を実行すると、 SELECT ... FOR SHARE
と扱われ共有ロックを取得します。しかし #2
のタイミングではB側の UPDATE
が排他ロックを取得していて競合するため、排他ロックがなくなるまで待つことになります。
SERIALIZABLE
と READ UNCOMMITTED
(2)
(1)では UPDATE
が先だったため SELECT
が待たされました。逆順も試してみます。
-- connection A / connection B --
SET SESSION TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#1)
BEGIN;
UPDATE t SET b = 2 WHERE b != 2;
--> (wait)
COMMIT;
--> 2 rows affected
--> Rows matched: 2 Changed: 2 Warnings: 0
COMMIT;
今度はB側の UPDATE
が待機状態になり、A側のトランザクションが終了してから処理されました。
SERIALIZABLE
と READ UNCOMMITTED
(3)
(1)と分離レベルを入れ替えてみます。A側( SELECT
)が READ UNCOMMITTED
、B側( UPDATE
)が SERIALIZABLE
です。
※ついでに今回だけB側はトランザクションをロールバックします。
-- connection A / connection B --
SET SESSION TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#1)
BEGIN;
UPDATE t SET b = 2 WHERE b != 2;
--> 2 rows affected
--> Rows matched: 2 Changed: 2 Warnings: 0
BEGIN;
SELECT * FROM t WHERE b = 2;
--> 5 rows in set (#2) !! dirty read !!
ROLLBACK;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#3)
COMMIT;
READ UNCOMMITTED
は #2
のタイミングで、 SERIALIZABLE
がコミットしていないデータを読み取れました(ダーティリード)。 SERIALIZABLE
の中は外部から隠蔽される、というわけではないようです。
SERIALIZABLE
と READ COMMITTED
念のため(3)からA側の分離レベルを READ COMMITTED
に上げた場合も試してみます。
-- connection A / connection B --
SET SESSION TRANSACTION
ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#1)
BEGIN;
UPDATE t SET b = 2 WHERE b != 2;
--> 2 rows affected
--> Rows matched: 2 Changed: 2 Warnings: 0
BEGIN;
SELECT * FROM t WHERE b = 2;
--> 3 rows in set (#2)
COMMIT;
SELECT * FROM t WHERE b = 2;
--> 5 rows in set (#3) !! non-repeatable read !!
COMMIT;
SEREALIZABLE
のコミット前のデータが読まれることはなく、最初の「 READ COMMITTED
同士」と同じようになりました。
まとめ
自分へ適切な分離レベルを設定した際、「他のトランザクションの影響を受けない」のは正しそうですが、「他のトランザクションに影響を与えない」というわけではありませんでした。影響を与えるかどうかは、相手の分離レベルが他から影響を受けるかどうか次第であり、自分が完全に制御できるわけではなさそうです。
別の見方をすれば、 READ UNCOMMITTED
に設定すれば他のトランザクション内の様子を覗き放題ということでしょうか。
参考
- MySQL 8.0 リファレンスマニュアル
- DockerHub