Zenn
🤝

トランザクションを試してみた

2021/11/15に公開
MySQL
トランザクション
tech

はじめに

普段 DB を使っている身として、トランザクションという用語と仕組みは知っているが、細かい話は触れずに過ごしてきた。ここらで一回、勉強がてら試してみようと思った。

トランザクションとは

トランザクションの内容については詳しく述べないが、下記のテーブルが有名であろう。

分離レベル ダーティリード ファジーリード ファントムリード ロストアップデート
READ UNCOMMITTED 発生 発生 発生 発生
READ COMMITTED x 発生 発生 発生
REPEATABLE READ x x 発生 発生
SERIALIZABLE x x x x
※法則性を考えると SERIALIZABLE のロストアップデートが発生となりそうだがならない。

準備

テスト用のコンテナを作成。今回は MySQL の最新版(8.0.27)を使う。

$ docker run --name tx-mysql -e MYSQL_ROOT_PASSWORD=test -d mysql:latest

コンテナの中に入って、 mysql にも入る。

$ docker exec -it tx-mysql bash
$ mysql -u root -ptest

※今回はパスワードはバレているのでコマンドライン上に書いているが、本来はコマンドラインで書くものではない。

テスト用のデータベース、テーブル、データを作成。

CREATE DATABASE tx;
USE tx;
CREATE TABLE data (id INT PRIMARY KEY, num INT);
INSERT INTO data (id, num) VALUES (1, 100), (2, 200), (3, 250);
SELECT * FROM data;
+----+------+
| id | num  |
+----+------+
|  1 |  100 |
|  2 |  200 |
|  3 |  250 |
+----+------+

現在のトランザクションレベルをみておく。

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+

※MySQL はデフォルトで REPEATABLE-READ に設定されている。

これからは二つトランザクション(txA, txB)を使うので、二つのターミナルか何かで mysql に入っている状態である。

ダーティリード

あるトランザクション(txB)で更新されたデータがコミットされる前に、他のトランザクション(txA)から更新後の内容が見れてしまうもの。これは分離レベル READ UNCOMMITTED のみに発生する。

-- トランザクションの分離レベルの変更(txA, txB で実行)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-UNCOMMITTED               | READ-UNCOMMITTED        |
+--------------------------------+-------------------------+

-- txA
BEGIN;
SELECT * FROM data WHERE id = 1;
-- 100

				-- txB
				BEGIN;
				SELECT * FROM data WHERE id = 1;
				-- 100
				UPDATE data SET num = 150 WHERE id = 1;
				-- 150

-- txA
SELECT * FROM data WHERE id = 1;
-- 150
-- txB のコミット前に更新後のデータが見れてしまう

				-- txB
				ROLLBACK;

-- txA
SELECT * FROM data WHERE id = 1;
-- 100

次に分離レベルを READ COMMITTED に変更したらダーティリードが起こらないかを見る。

-- トランザクションの分離レベルの変更(txA, txB で実行)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-COMMITTED                 | READ-COMMITTED          |
+--------------------------------+-------------------------+

-- txA
BEGIN;
SELECT * FROM data WHERE id = 1;
-- 100

				-- txB
				BEGIN;
				SELECT * FROM data WHERE id = 1;
				-- 100
				UPDATE data SET num = 150 WHERE id = 1;
				-- 150

-- txA
SELECT * FROM data WHERE id = 1;
-- 100
-- txB のコミット前なら更新前のデータが見れる

				-- txB
				ROLLBACK;

-- txA
SELECT * FROM data WHERE id = 1;
-- 100
-- txB がロールバックされてもそのまま

ファジーリード

さっき見た例だと txB がロールバックしたことによって更新が行われなかったが、コミットされていた場合は txA がデータ取得するタイミングによって値が変わると言うことが起こる。これは READ COMMITTED 以下の分離レベルで起きる。

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-COMMITTED                 | READ-COMMITTED          |
+--------------------------------+-------------------------+

-- txA
BEGIN;
SELECT * FROM data WHERE id = 1;
-- 100

				-- txB
				BEGIN;
				SELECT * FROM data WHERE id = 1;
				-- 100
				UPDATE data SET num = 150 WHERE id = 1;
				-- 150

-- txA
SELECT * FROM data WHERE id = 1;
-- 100
-- txB のコミット前なら更新前のデータが見れる

				-- txB
				COMMIT;

-- txA
SELECT * FROM data WHERE id = 1;
-- 150
-- txB のコミット後なら更新後のデータとなってしまう



-- txA
-- 更新したデータを元に戻しておく
UPDATE data SET num = 100 WHERE id = 1;
COMMIT;

次に分離レベルを REPEATABLE READ に変更したらファジーリードが起こらないかを見る。

-- トランザクションの分離レベルの変更(txA, txB で実行)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+

-- txA
BEGIN;
SELECT * FROM data WHERE id = 1;
-- 100

				-- txB
				BEGIN;
				SELECT * FROM data WHERE id = 1;
				-- 100
				UPDATE data SET num = 150 WHERE id = 1;
				-- 150

-- txA
SELECT * FROM data WHERE id = 1;
-- 100
-- txB のコミット前なら更新前のデータが見れる

				-- txB
				COMMIT;

-- txA
SELECT * FROM data WHERE id = 1;
-- 100
-- txB のコミット後でも更新前のデータが見れる

-- txA
COMMIT;
SELECT * FROM data WHERE id = 1;
-- 150
-- txA がコミットしたので txB の反映が見れるようになる


-- txA
-- 更新したデータを元に戻しておく
UPDATE data SET num = 100 WHERE id = 1;
COMMIT;

ファントムリード

あるトランザクション(txA)で条件を絞ってデータを取得したのち、別のトランザクション(txB)でその条件にあうレコードの挿入・削除を行ったときに生じ、コミット前の txB の変更が txA に影響を及ぼすと言うものである。

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+

-- txA
BEGIN;
SELECT count(*) FROM data WHERE num > 100;
-- 2

				-- txB
				BEGIN;
				SELECT count(*) FROM data WHERE num > 100;
				-- 2
				INSERT INTO data (id, num) VALUE (100, 1234);
				-- 150
				SELECT count(*) FROM data WHERE num > 100;
				-- 3

-- txA
SELECT count(*) FROM data WHERE num > 100;
-- 2
-- 想定では txB のデータ更新が反映されて 3 が返ってくるはず...

上記で見た通りなぜが想定どおりにいかない。それは MySQL では、分離レベルが REPEATABLE READ であると、基本的にファントムリードが起きないようになっているからである。

ロストアップデート

最後にロストアップデートを見てみる。これは別々のトランザクションで更新をかけたときに後勝ちになるものである。

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+

-- txA
BEGIN;
SELECT * FROM data WHERE id = 1;
-- 100

				-- txB
				BEGIN;
				SELECT * FROM data WHERE id = 1;
				-- 100
				UPDATE data SET num = 250 WHERE id = 1;
				-- 250
				
SELECT * FROM data WHERE id = 1;
-- 100

				-- txB
				COMMIT;

-- txA
UPDATE data SET num = 334 WHERE id = 1;
SELECT * FROM data WHERE id = 1;
-- 334
-- txA の更新で上書きされた

この現象は SERIALIZABLE なら起きないのかをみる。

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| SERIALIZABLE                   | SERIALIZABLE            |
+--------------------------------+-------------------------+

-- txA
BEGIN;
SELECT * FROM data WHERE id = 1;
-- 100

				-- txB
				BEGIN;
				SELECT * FROM data WHERE id = 1;
				-- 100
				UPDATE data SET num = 250 WHERE id = 1;
				-- ここで txA のロック解除待ちになる

-- txA
ROLLBACK;

				-- txB
				-- txA のロールバック後に更新される
				SELECT * FROM data WHERE id = 1;
				-- 250

以上のように SERIALIZABLE の場合はロストアップデートが発生しない。

終わりに

簡単であったが、トランザクションについて試した。ただ、細かいところには全く触れていない。ロック対象となる箇所や、なぜ MySQL はファジーリードが起こらないか、など。それらについても、詳しく調べて、記事を書きたいと思う(時期未定)。

Discussion