Go to Qiita Advent Calendar 2024 Top
LoginSignup
5
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

ミライトデザインAdvent Calendar 2024

Day 2
@tkek321(kakiuchi)in株式会社ミライトデザイン

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

Last updated at Posted at 2024-12-02

ミライトデザイン Advent Calendar 2024 2日目の記事になります。

1日目には ほげさん の記事でした。

コードを書く際の重要な考え方がわかりやすく言語化されて大変参考になりました。
まだ読まれていない方は是非読んでみてください。

この記事ではトランザクション分離レベルについて調べたことを記事にしました。

はじめに

トランザクション分離レベルについて調べたので、まとめる。

トランザクション分離レベルの話は、共通しているところが多いが、
RDBMS によって挙動が変わるため、今回は MySQL の挙動に絞る。

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

ACID 原則 の I に関する概念。

## ACID 原則の I = Isolation（独立性）
トランザクションの実行中は他からの操作の影響を受けないことを保証する。

トランザクション分離レベルは、どの程度他のトランザクションへの影響を許容するかを定義したもの。

独立性を確実に担保するためには、処置を１件ずつ順番(Serial)に処理をしていけばいい。

ただし、その場合後に実行された処理は、前の処理を待つ時間が発生することになってしまうため、 RDBMS では処理を並列に実行可能にしている。

並列に実行することを許容した際には、処理の速度が上がる代わりに、互いの処理の影響を受けやすくなってしまい、独立性のレベルが低くなってしまう。

そのため、どの程度の処理の独立性のレベルを下げることを許容するのかを、トランザクション分離レベルの設定で変更することができる。

例えば、金融系のシステムであれば、性能よりも正確性を優先し、高い分離レベルを設定する。ようなことができる。

主な分離レベル

分離レベルは次の４つ

  • read uncommitted
  • read committed
  • repeatable read
  • serializable

下に行くほど安全になるが、速度は遅くなる。

トランザクション分離レベルの違いによって禁止される主な現象（アノマリー）

アノマリーは実際にはもっと多くの現象がある。
今回は代表的そうなものに絞る。

以下の図の例では、トランザクション1 ( T1 ) と別のトランザクション ( T2 ) が、口座テーブルのデータを扱うイメージ。

Dirty Read

トランザクション実行中に、他のトランザクションが更新した、まだコミットされていない状態のデータを読み取れる。

MySQL...ダーティー読み取り

ダーティーリード.png

実際には変更（ commit ）されていない結果が見えてしまうため、T1 で口座の集計処理などが実施されていた場合は、集計の結果が間違ったものになってしまう。

Fuzzy Read / Non-Repeatable Read

トランザクション実行中に、他のトランザクションが更新し、コミットしたデータが読み取れる。
トランザクション実行中に、複数回 SELECT した場合に、結果が変わってしまう。

MySQL...反復不可能読み取り

イメージ - ページ 1 (1).png

COMMIT されたデータが見えることは一見問題がないように見える。

もちろんケースにはよるが、WEB システムであれば、COMMIT されて確定したデータはトランザクション中であってもすぐに確認したい場合も多そう。

ただし、ACID 原則の I = Isolation（独立性）ではトランザクションの実行中は他の操作からの影響を受けないことが保証されている必要があるため、Isolation は守られていないことになる。

## ACID 原則の I = Isolation（独立性）
トランザクションの実行中は他からの操作の影響を受けないことを保証する。

Fuzzy Read が発生しない場合は、T1 の SELECT の結果がどちらも 1,000 になることが正しい。
（トランザクションの実行中は何回 SELECT をしても結果は変わらない）

Phantom Read

トランクザクション実行中に、別トランザクションで INSERT または DELETE した場合に、 COUNT した行数の結果が変わってしまう。

MySQL...ファントム

image.png

Lost Update

複数のトランザクションで処理が実行されてた場合、後に実行されて更新で処理が上書きされる。

LOST UPDATE - ページ 1 (1).png

分離レベルと上記の現象を許容する組み合わせ

次のような組み合わせになるはず。

分離レベル.png

分離レベルの挙動を確認してみる

Docker で MySQL 8 環境を用意する

docker container run \
    --name db \
    --rm \
    --detach \
    --publish 33306:3306 \
    --env MYSQL_ROOT_PASSWORD=root \
    --env MYSQL_USER=user \
    --env MYSQL_PASSWORD=password \
    --env MYSQL_DATABASE=study_isolation_level \
    mysql:8

# コンテナにアクセス
docker exec -it db bash


# mysql へアクセス
mysql -u root -proot study_isolation_level

テーブルとデータを作成

> CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(16) NOT NULL,
    balance INT NOT NULL
);


> INSERT INTO accounts (name, balance) VALUES
('A-san', 1000);


> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |   1000 |
+----+-------+---------+
1 row in set (0.01 sec)

現在のトランザクション分離レベルを確認

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

MySQL のデフォルトのトランザクション分離レベルは repeatable read なので、global のトランザクションが repeatable read、 現在セッションでのトランザクション分離レベルも repeatable read になっている。

確認方法

トランザクションを分けるために、別タブで開き、それぞれのタブで次の手順の Docker の db コンテナへアクセスし、MySQL へアクセスして動作を確認する。

# コンテナにアクセス
docker exec -it db bash


# MySQL へアクセス
mysql -u root -proot study_isolation_level

同じテキストに記載するが、T1 と T2 は別タブで実行した結果を記載する。

-- T1
-- ターミナルのタブ１で実行した SQL を記載

-- T2
-- ターミナル２のタブ２で実行した SQL を記載

read uncommitted

read uncommitted.png

トランザクション分離レベルを read uncommitted に変更する。

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

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

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

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

どちらのセッションでもトランザクション分離レベルが、read uncommitted になっている。

Dirty Read を起こしてみる

-- T1
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1000 |
+----+-------+---------+
1 row in set (0.00 sec)


-- T2
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

> UPDATE accounts SET balance = 1500 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
+----+-------+---------+
1 row in set (0.00 sec)

-- T1 
-- COMMIT されていないデータが取得できてしまう
SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
+----+-------+---------+
1 row in set (0.01 sec)

-- T2
> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1000 |
+----+-------+---------+
1 row in set (0.00 sec)

-- T1
-- ROLLBACK されたので結果は当然 1,000 に戻る
> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1000 |
+----+-------+---------+
1 row in set (0.01 sec)

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

read commited

readCommited

トランザクション分離レベルを read committed に変更する。

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

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


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

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

Dirty Read と Fuzzy Read を試す

Dirty Read は発生しないはず。
Fuzzy Read は発生するはず。

-- T1
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1000 |
+----+-------+---------+
1 row in set (0.01 sec)

-- T2
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

> UPDATE accounts SET balance = 1500 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
+----+-------+---------+
1 row in set (0.00 sec)

-- T1
-- COMMIT されていないデータは見れない（Dirty Read は発生していない）
> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1000 |
+----+-------+---------+
1 row in set (0.00 sec)

-- T2
> COMMIT;
Query OK, 0 rows affected (0.00 sec)


-- T1
-- COMMIT されたデータは確認できる（Fuzzy Read が発生している）
> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
+----+-------+---------+
1 row in set (0.00 sec)

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

Phantom Read を試す

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

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

-- T1
> SELECT count(*) FROM accounts;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    3000 |
+----+-------+---------+
1 row in set (0.00 sec)


-- T2
> INSERT INTO accounts VALUES (2, "B-san", 2000);
Query OK, 1 row affected (0.00 sec)

 SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    3000 |
|  2 | B-san |    2000 |
+----+-------+---------+
2 rows in set (0.00 sec)

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


-- T1
-- 追加された結果が読み取れている ( Phantom Read が発生 )
> SELECT count(*) FROM accounts;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    3000 |
|  2 | B-san |    2000 |
+----+-------+---------+
2 rows in set (0.00 sec)

repeatable read

repeatable read.png

-- T1
> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

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

-- T2
> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.01 sec)

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

Lost Update と Fuzzy Read を試す

Lost Update は発生する。
Fuzzy Read は発生しない。

-- T1
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
+----+-------+---------+
1 row in set (0.00 sec)

-- T2
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

> UPDATE accounts SET balance = 2000 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    2000 |
+----+-------+---------+
1 row in set (0.00 sec)

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

-- T1
-- Fuzzy Read は発生していない

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
+----+-------+---------+
1 row in set (0.00 sec)

> UPDATE accounts SET balance = 3000 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    3000 |
+----+-------+---------+
1 row in set (0.00 sec)

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

-- 結果が上書きされてしまう（ Lost Update が発生している ）

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    3000 |
+----+-------+---------+
1 row in set (0.00 sec)

UPDATE の場合は MySQL がうまく更新してくれているっぽい。
おそらく、SELECT はスナップショットを作成してそこから読み取るけど、 UPDATE は元データを参照するとかそんな感じ ??

あまりわかっていないので気が向いたら調べる。

-- T1
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
+----+-------+---------+
1 row in set (0.00 sec)

-- T2
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

> UPDATE accounts SET balance = balance + 500 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    2000 |
+----+-------+---------+
1 row in set (0.00 sec)

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

-- T1

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
+----+-------+---------+
1 row in set (0.00 sec)

> UPDATE accounts SET balance = balance + 1000 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    3000 |
+----+-------+---------+
1 row in set (0.00 sec)

Phantom Read を試す

Phantom Read は発生しない。

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

-- T2
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

> INSERT INTO accounts VALUES (2, "B-san", 2000);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
|  2 | B-san |    2000 |
+----+-------+---------+
2 rows in set (0.00 sec)

-- T1

> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    1500 |
+----+-------+---------+
1 row in set (0.00 sec)

> SELECT count(*) FROM accounts;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

serializable

serializable.png

-- T1
> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.01 sec)

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

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

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

Lost Update が発生しない

-- T1
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- T2
> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- T1
mysql> SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    3000 |
+----+-------+---------+
1 row in set (0.00 sec)

-- T2
-- ロックがかかっているため更新ができない (Lost Update が発生しない)
> UPDATE accounts SET balance = 4000 WHERE id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

この時のロックの状態は次のようになっている。

> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+-----------+-----------+-------------+------------------------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-------------+-----------+-----------+-------------+------------------------+
| accounts    | TABLE     | IS        | GRANTED     | NULL                   |
| accounts    | RECORD    | S         | GRANTED     | supremum pseudo-record |
| accounts    | RECORD    | S         | GRANTED     | 1                      |
+-------------+-----------+-----------+-------------+------------------------+
3 rows in set (0.00 sec)

id=1 に共有ロックがかかっているため、更新処理ができない。

ロックの話はこちら。

まとめ

トランザクション分離レベルは結局どうロックをとるかという話っぽい。

終わりに

明日は、 bucchi さんの今年使った AI についての記事らしいです。

個人的に便利な AI があれば触ってみたいと思っていたところだったので、どんな内容なのか楽しみです！

参考

5
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?