🐥

MySQL9.0でダーティーリードやってみた

2024/08/06に公開

はじめに

ダーティーリードとは

https://dev.mysql.com/doc/refman/8.0/ja/glossary.html#glos_dirty_read
ダーティー読み取り

信頼できないデータ、つまり別のトランザクションによって更新されたけれども、まだコミットされていないデータを取得する操作。
これは、コミットされた読み取りと呼ばれる分離レベルでのみ可能です。

この種の操作は、データベース設計の ACID 原則には準拠しません。
これは非常にリスクが高いと見なされます。
データをロールバックできたり、コミットされる前にさらに更新できたりするためです。
この場合、ダーティー読み取りを行うトランザクションは、正確であると確定されていないデータを使用することになります。

MySQLのDockerコンテナをつくる

今回はMySQLバージョンは9.0を使用します｡

コマンド

docker run --name mysql9.0 -e MYSQL_ROOT_PASSWORD=root -d mysql:9.0

実行結果

% docker run --name mysql9.0 -e MYSQL_ROOT_PASSWORD=root -d mysql:9.0
Unable to find image 'mysql:9.0' locally
9.0: Pulling from library/mysql
c72f53f7235b: Pull complete
c7e4ed755af2: Pull complete
6c8c802f90bc: Pull complete
eecc55f854cd: Pull complete
cc8dabc09813: Pull complete
3a27c3f0dbd7: Pull complete
a55978eb4258: Pull complete
767d62f87325: Pull complete
afe5d39ea75c: Pull complete
72a1a98f7aad: Pull complete
Digest: sha256:76cfe72d2c51be8946bbd3085028e8989599bae0c6112748c1da35ad448fc00b
Status: Downloaded newer image for mysql:9.0
240ca2acd4396158f23d7af787987250d2cbe060045225e7fec77bab96d9c315
%

コンテナが起動しました｡

MySQLにログインする

コマンド

docker exec -it mysql9.0 mysql -uroot -p

実行結果

% docker exec -it mysql9.0 mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 9.0.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

データベースを作成する

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql>

テーブルを作成する

mysql> CREATE TABLE test.sample (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32));
Query OK, 0 rows affected (0.01 sec)
mysql> DESC test.sample;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32)  | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

データ操作するセッションでログインする

セッションA

% docker exec -it mysql9.0 mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 9.0.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

セッションB

% docker exec -it mysql9.0 mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 9.0.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

わかりやすいようにプロンプトを変更する

セッションA

mysql> prompt SESSION-A>
PROMPT set to 'SESSION-A> '
SESSION-A>

セッションB

mysql> prompt SESSION-B>
PROMPT set to 'SESSION-B> '
SESSION-B>

現在の自動コミットモードを確認する

どちらも自動コミットモードは有効となっていますので
実行したSQLは明示的にコミットをしなくても
実行した時点でコミットがされます｡
MySQLではこれがデフォルトですね｡

SESSION-A> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

SESSION-A>

SESSION-B> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

SESSION-B>

現在の分離レベルを確認する

どちらも「REPEATABLE-READ」となっています｡
MySQLのデフォルトの分離レベルですね｡

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

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

SESSION-A> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ                 |
+---------------------------------+
1 row in set (0.01 sec)

SESSION-A>

SESSION-B> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ                 |
+---------------------------------+
1 row in set (0.00 sec)

SESSION-B>

分離レベルを「READ UNCOMMITTED」に変更する

分離レベルが「REPEATABLE-READ」の場合だと
トランザクション内の最初の読み取りでスナップショットが作成され
他のトランザクションはそのスナップショットを参照するために
ダーティーリードが発生しません｡
そのため､今回は分離レベルをダーティーリードが発生する
「READ UNCOMMITTED」に変更します｡

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

SESSION-A> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED                |
+---------------------------------+
1 row in set (0.00 sec)

SESSION-A>

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

SESSION-B> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED                |
+---------------------------------+
1 row in set (0.00 sec)

サンプルデータを挿入する

SESSION-A> INSERT INTO test.sample SET name = 'ABC';
Query OK, 1 row affected (0.02 sec)

SESSION-A> select * from test.sample;
+----+------+
| id | name |
+----+------+
|  1 | ABC  |
+----+------+
1 row in set (0.00 sec)

SESSION-A>

トランザクションを開始する

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

SESSION-A>

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

SESSION-B>

セッションAでレコードの値を更新する

SESSION-A> UPDATE test.sample SET name = 'DEF' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SESSION-A> select * from test.sample;
+----+------+
| id | name |
+----+------+
|  1 | DEF  |
+----+------+
1 row in set (0.00 sec)

SESSION-A>

セッションBでレコードの値を参照する

ダーティーリードが発生しました｡

SESSION-B> select * from test.sample;
+----+------+
| id | name |
+----+------+
|  1 | DEF  |
+----+------+
1 row in set (0.00 sec)

SESSION-B>

もしここでセッションBで同じレコードを更新しようとすると

セッションAがロックしているためにエラーとなります｡

SESSION-B> UPDATE test.sample SET name = 'DEF' WHERE id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
SESSION-B>

ではセッションBでレコードを更新しタイムアウトまでにセッションBがロールバックすると

セッションBで更新クエリを実行します｡

SESSION-B> UPDATE test.sample SET name = 'GHI' WHERE id = 1;

その直後にセッションAでロールバックします｡

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

SESSION-A>

セッションAがロールバックをした直後に更新処理が完了します｡

SESSION-B> UPDATE test.sample SET name = 'GHI' WHERE id = 1;
Query OK, 1 row affected (2.86 sec)
Rows matched: 1  Changed: 1  Warnings: 0

その後､セッションBがまだトランザクションをクローズしていない状態で
セッションAでレコードを読み取りをするとまたここでもダーティーリードが発生します｡

SESSION-A> select * from test.sample;
+----+------+
| id | name |
+----+------+
|  1 | GHI  |
+----+------+
1 row in set (0.01 sec)

SESSION-A>

そして､セッションBでもロールバックをすると
もともとのレコードの値に戻ります｡

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

SESSION-B> select * from test.sample;
+----+------+
| id | name |
+----+------+
|  1 | ABC  |
+----+------+
1 row in set (0.00 sec)

SESSION-B>

最後に

約17年ぶりにダーティーリードを実験してみました｡
実際に動作させながら確認すると記憶に定着しやすいと思います｡

はじめに

ダーティーリードとは

MySQLのDockerコンテナをつくる

コマンド

実行結果

MySQLにログインする

コマンド

実行結果

データベースを作成する

テーブルを作成する

データ操作するセッションでログインする

セッションA

セッションB

わかりやすいようにプロンプトを変更する

セッションA

セッションB

現在の自動コミットモードを確認する

現在の分離レベルを確認する

分離レベルを「READ UNCOMMITTED」に変更する

サンプルデータを挿入する

トランザクションを開始する

セッションAでレコードの値を更新する

セッションBでレコードの値を参照する

もしここでセッションBで同じレコードを更新しようとすると

ではセッションBでレコードを更新しタイムアウトまでにセッションBがロールバックすると

最後に

Discussion