MySQL の 4 つのトランザクション分離レベルの分析例

データベース操作では、同時に読み取られるデータの正確性を効果的に保証するために、トランザクション分離レベルが提案されています。データベース トランザクションには 4 つの分離レベルがあります。SQL 標準では、トランザクションの内部および外部で表示される変更と非表示になる変更を制限するための特定のルールを含む 4 つの分離レベルが定義されています。次の記事では、MySQL の 4 つのトランザクション分離レベルに関する関連情報を例を通して詳細に分析しています。必要な方は参照してください。

まえがき

以下では多くを語る必要はありません。詳細な紹介を見てみましょう。

データベース トランザクションには 4 つの分離レベルがあります:

• コミットされていない読み取り (コミットされていない読み取り): ダーティ リードが許可されます。これは、他のセッションのコミットされていないトランザクションによって変更されたデータが読み取られる可能性があることを意味します。

• 読み取りコミット: Oracle などのほとんどのデータベースは、デフォルトでこのレベルに設定されています。

• 繰り返し読み取り: 繰り返し読み取り。同じトランザクション内のクエリは、トランザクションの開始時 (InnoDB のデフォルト レベル) で一貫性があります。 SQL 標準では、この分離レベルにより反復不可能な読み取りは排除されますが、ファントム読み取りは依然として存在します。

• シリアル化可能: 完全にシリアル化された読み取りごとにテーブルレベルの共有ロックが必要で、読み取りと書き込みは相互にブロックされます。

トランザクション分離の概念に初めて触れた友人は、上記の教科書の定義を読んだ後、混乱するかもしれません。具体的な例を通して 4 つの分離レベルを説明しましょう。

まずユーザーテーブルを作成します:

CREATE TABLE user (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE `uniq_name` USING BTREE (name)
) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

コミットされていない分離レベルを読み取ります

まず、トランザクションの分離レベルをコミット済みを読み取るように設定します:

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED  |
+------------------------+
1 row in set (0.00 sec)

以下では、トランザクション 1 とトランザクション 2 をそれぞれシミュレートするために 2 つの端末を開きます。操作 1 と 2 は時系列に実行されることを意図しています。

トランザクション 1

mysql> start transaction; # 操作1
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name) values('ziwenxie'); # 操作3
Query OK, 1 row affected (0.05 sec)

トランザクション 2

mysql> start transaction; # 操作2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作4
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

上記の実行結果から、コミットされていない読み取りレベルでは、トランザクション 2 でコミットされていないデータをトランザクション 1 (ダーティ読み取り) で読み取る可能性があることが明確にわかります。

コミットされた分離レベルの読み取り

上記のダーティ リードの問題は、分離レベルをコミットに設定することで解決できます。

mysql> set session transaction isolation level read committed;

トランザクション 1

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作三
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作五，操作四的修改并没有影响到事务一
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.00 sec)

トランザクション 2

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='lisi' where id=10; # 操作四
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作六
Query OK, 0 rows affected (0.08 sec)

ダーティ リードの問題は解決されましたが、トランザクション 1 のオペレーション 7 では、トランザクション 2 により、オペレーション 6 のコミット後にトランザクション 1 が同じトランザクション内で 2 回読み取られることに注意してください。これは非反復読み取りの問題であり、3 番目のトランザクション分離レベルの反復読み取りを使用すると、この問題を解決できます。

反復読み取り分離レベル

MySQL の Innodb ストレージ エンジンのデフォルトのトランザクション分離レベルは反復読み取り分離レベルであるため、追加の設定を行う必要はありません。

トランザクション 1

mysql> start tansactoin; # 操作一
mysql> select * from user; # 操作五
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> commit; # 操作六
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

トランザクション 2

mysql> start tansactoin; # 操作二
mysql> update user set name='lisi' where id=10; # 操作三
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作四

トランザクション 1 の操作 5 では、操作 3 でのトランザクション 2 の更新を読み取りませんでした。更新されたデータはコミット後にのみ読み取ることができます。

Innodb はファントム読み取りを解決しますか? 実際、RR レベルはファントム読み取りを生成する可能性があります。InnoDB エンジンはこの問題を解決するために MVCC マルチバージョン同時実行制御を使用すると公式に主張しています。

表示の都合上、上記のユーザー テーブルを変更しました:

mysql> alter table user add salary int(11);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> delete from user;
Query OK, 1 rows affected (0.07 sec)
mysql> insert into user(name, salary) value('ziwenxie', 88888888);
Query OK, 1 row affected (0.07 sec)
mysql> select * from user;
+----+----------+----------+
| id | name  | salary |
+----+----------+----------+
| 10 | ziwenxie | 88888888 |
+----+----------+----------+
1 row in set (0.00 sec)

トランザクション 1

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> update user set salary='4444'; # 操作六，竟然影响了两行，不是说解决了幻读么？
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from user; # 操作七， Innodb并没有完全解决幻读
+----+----------+--------+
| id | name  | salary |
+----+----------+--------+
| 10 | ziwenxie | 4444 |
| 11 | zhangsan | 4444 |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.04 sec)

トランザクション 2

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name, salary) value('zhangsan', '666666'); # 操作四
Query OK, 1 row affected (0.00 sec)
mysql> commit; # 操作五
Query OK, 0 rows affected (0.04 sec)

上記の例からわかるように、Innodb は公式に述べられているようにファントム リードを解決しませんが、上記のように現場ではあまり一般的ではないので、あまり心配する必要はありません。

シリアル化された分離レベル

最も高い分離レベルでは、ファントム読み取りは発生せず、実際の開発ではほとんど使用されません。

関連する推奨事項:

MySQL トランザクション分離レベル インスタンスのチュートリアル

MySQL の 4 つのトランザクション分離レベルの詳細な説明と比較

MySQL トランザクション分離レベルがパフォーマンスに与える影響の簡単な分析

以上がMySQL の 4 つのトランザクション分離レベルの分析例の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。