mysqlトランザクション独立性レベル
7241 ワード
SQL規格では、4つの独立性レベルが定義されています.これには、トランザクションの内外でどのような変更が表示されるか、表示されないかを定義するための特定のルールが含まれています.低レベルの独立性レベルは、一般的に、より高い同時処理をサポートし、システムオーバーヘッドを低減します.この4つの独立性レベルについて、具体的な例で説明します.
Read Uncommitted(コミットされていないコンテンツの読み取り)
この独立性レベルでは、すべてのトランザクションに対して、コミットされていない他のトランザクションの実行結果が表示されます.この独立性レベルは、他のレベルよりもパフォーマンスが優れていないため、実際のアプリケーションではあまり使用されません.コミットされていないデータを読み取ることをダーティリード(Dirty Read)ともいう.
例:
表の作成:create table test(id int,num int)ENGINE=innodb;
A、Bの2つのセッションを開いて、以下の設定をします.
1.独立性レベルの表示:
2.独立性レベルの変更:
3.トランザクションの自動コミットを閉じる:set autocommit=off;または:set@@autocommit=0;
4.Aセッションでトランザクションを開始し、データを表示します.
5.Bセッションでトランザクションを開始し、データを挿入してコミットしません.
上記の例から分かるように、トランザクションBはレコードを更新したが、コミットされていない場合、トランザクションAはコミットされていないレコードをクエリーすることができるが、トランザクションBがロールバックされると、トランザクションAの2回のクエリーのデータが異なり、ダーティリード現象を引き起こす.コミットされていない読み取りは、最も低い独立性レベルです.
Read Committed(コミット内容の読み込み)
これは、ほとんどのデータベース・システムのデフォルトの独立性レベルです(ただし、MySQLのデフォルトではありません).独立性の簡単な定義を満たしています.1つのトランザクションでは、トランザクションにコミットされた変更しか表示されません.この独立性レベルは、同じトランザクションの他のインスタンスがインスタンス処理の間に新しいcommitを返す可能性があるため、同じselectが異なる結果を返す可能性があるため、いわゆる重複不可能読み取り(Nonrepeatable Read)もサポートします.
例:
1.2つのセッションで独立性レベルを変更:setセッションtx_isolation='read-committed';
2.A側が取引を開始し、データを表示する:
3.B側はデータの更新を開始する:
上記の例では、コミットされたリード独立性レベルがダーティリードの問題を解決したが、トランザクションAが2つのクエリの間でトランザクションBが1つのデータを更新したため、2つのクエリのデータが一致しないという再読み取り不可能な問題が発生したと結論した.コミットされた読み取りレベルでは、コミットされたレコードの読み取りのみが許可されますが、繰り返し読み取りは必要ありません.
Repeatable Read(再読み取り可能)
これはMySQLのデフォルトのトランザクション独立性レベルで、同じトランザクションの複数のインスタンスがデータを同時に読み込むときに同じデータ行が表示されることを保証します.しかし、理論的には、幻読(Phantom Read)というもう一つの厄介な問題を引き起こす.簡単に言えば、幻読みとは、ユーザがある範囲のデータ行を読み取ると、別のトランザクションがその範囲内に新しい行を挿入し、ユーザがその範囲のデータ行を読み取ると、新しい「幻」行が発見されることを意味する.InnoDBとFalconストレージエンジンは、この問題をマルチバージョン同時制御(MVCC,Multiversion Concurrency Control)メカニズムによって解決した.
例:
1.両端で独立性レベルを変更する:set session tx_isolation='repeatable-read';
2.取引A照会データ:
3.取引B更新データ:
上記の例から、繰り返し可能読み出し独立性レベルでは、コミットされたレコードの読み取りのみが許可され、1つのトランザクションが2回にわたって1つのレコードを読み込む間はデータは一貫していますが、他のトランザクションとシリアル化する必要はありません.たとえば、トランザクションがコミットされたトランザクションによって更新されたレコードを見つけることができますが、データベースが独立性レベルの実装に異なるため、幻読みの問題が発生する可能性があります.以上の例のように,データの幻読みの問題は発生しなかった.
Serializable(シリアル化可能)
これは、トランザクションのソートを強制することで、互いに衝突しないようにする最も高い独立性レベルです.これにより、ファンタジーの問題を解決します.簡単に言えば、各読み出しデータ行に共有ロックを追加します.このレベルでは、大量のタイムアウト現象とロック競合を引き起こす可能性があります.
例:
1.独立性レベルの変更:set session tx_isolation='Serializable';
2.取引Aがデータの照会を開始する:
3.取引B更新データ:
インスタンスからserializableがフィールドを完全にロックしていることがわかります.前のトランザクションが完了し、ロックが解除されるまで、同じデータをクエリーするトランザクションが待機する必要があります.完全な独立性レベルであり、対応するデータテーブルがロックされるため、効率的な問題があります.
この4つの独立性レベルは異なるロックタイプで実現され,同じデータを読み取ると問題が発生しやすい.例:
ダーティリード(Drity Read):あるトランザクションが1つのデータを更新し、もう1つのトランザクションが同じデータを読み込んだ場合、いくつかの理由で前のRollBackが操作した場合、後のトランザクションが読み込んだデータは正しくありません.
再読み取り不可(Non-repeatable read):1つのトランザクションの2つのクエリでデータが一致しません.これは、2つのクエリ・プロシージャの間に1つのトランザクションが更新された元のデータが挿入されている可能性があります.
幻読(Phantom Read):1つのトランザクションの2つのクエリでデータペン数が一致しません.たとえば、1つのトランザクションがいくつかのカラム(Row)データをクエリしたのに、別のトランザクションがこの時点で新しい数列のデータを挿入した場合、前のトランザクションは次のクエリで、いくつかのカラムのデータが以前になかったことがわかります.
MySQLでは、次の4つの独立性レベルが実装されています.
Read Uncommitted(コミットされていないコンテンツの読み取り)
この独立性レベルでは、すべてのトランザクションに対して、コミットされていない他のトランザクションの実行結果が表示されます.この独立性レベルは、他のレベルよりもパフォーマンスが優れていないため、実際のアプリケーションではあまり使用されません.コミットされていないデータを読み取ることをダーティリード(Dirty Read)ともいう.
例:
表の作成:create table test(id int,num int)ENGINE=innodb;
A、Bの2つのセッションを開いて、以下の設定をします.
1.独立性レベルの表示:
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)2.独立性レベルの変更:
mysql> set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)
mysql> set global tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+------------------+
| READ-UNCOMMITTED | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)3.トランザクションの自動コミットを閉じる:set autocommit=off;または:set@@autocommit=0;
4.Aセッションでトランザクションを開始し、データを表示します.
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
Empty set (0.01 sec)
/* B */
mysql> select * from test;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
/* B */
mysql> select * from test;
Empty set (0.00 sec)5.Bセッションでトランザクションを開始し、データを挿入してコミットしません.
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> insert into test values(1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.01 sec)上記の例から分かるように、トランザクションBはレコードを更新したが、コミットされていない場合、トランザクションAはコミットされていないレコードをクエリーすることができるが、トランザクションBがロールバックされると、トランザクションAの2回のクエリーのデータが異なり、ダーティリード現象を引き起こす.コミットされていない読み取りは、最も低い独立性レベルです.
Read Committed(コミット内容の読み込み)
これは、ほとんどのデータベース・システムのデフォルトの独立性レベルです(ただし、MySQLのデフォルトではありません).独立性の簡単な定義を満たしています.1つのトランザクションでは、トランザクションにコミットされた変更しか表示されません.この独立性レベルは、同じトランザクションの他のインスタンスがインスタンス処理の間に新しいcommitを返す可能性があるため、同じselectが異なる結果を返す可能性があるため、いわゆる重複不可能読み取り(Nonrepeatable Read)もサポートします.
例:
1.2つのセッションで独立性レベルを変更:setセッションtx_isolation='read-committed';
2.A側が取引を開始し、データを表示する:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
/* B */
mysql> select * from test;
Empty set (0.00 sec)
/* B */
mysql> select * from test;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)3.B側はデータの更新を開始する:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> insert into test values(1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)上記の例では、コミットされたリード独立性レベルがダーティリードの問題を解決したが、トランザクションAが2つのクエリの間でトランザクションBが1つのデータを更新したため、2つのクエリのデータが一致しないという再読み取り不可能な問題が発生したと結論した.コミットされた読み取りレベルでは、コミットされたレコードの読み取りのみが許可されますが、繰り返し読み取りは必要ありません.
Repeatable Read(再読み取り可能)
これはMySQLのデフォルトのトランザクション独立性レベルで、同じトランザクションの複数のインスタンスがデータを同時に読み込むときに同じデータ行が表示されることを保証します.しかし、理論的には、幻読(Phantom Read)というもう一つの厄介な問題を引き起こす.簡単に言えば、幻読みとは、ユーザがある範囲のデータ行を読み取ると、別のトランザクションがその範囲内に新しい行を挿入し、ユーザがその範囲のデータ行を読み取ると、新しい「幻」行が発見されることを意味する.InnoDBとFalconストレージエンジンは、この問題をマルチバージョン同時制御(MVCC,Multiversion Concurrency Control)メカニズムによって解決した.
例:
1.両端で独立性レベルを変更する:set session tx_isolation='repeatable-read';
2.取引A照会データ:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
/* B */
mysql> select * from test;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
/* B */
mysql> select * from test;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 rows in set (0.00 sec)3.取引B更新データ:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(3,3);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)上記の例から、繰り返し可能読み出し独立性レベルでは、コミットされたレコードの読み取りのみが許可され、1つのトランザクションが2回にわたって1つのレコードを読み込む間はデータは一貫していますが、他のトランザクションとシリアル化する必要はありません.たとえば、トランザクションがコミットされたトランザクションによって更新されたレコードを見つけることができますが、データベースが独立性レベルの実装に異なるため、幻読みの問題が発生する可能性があります.以上の例のように,データの幻読みの問題は発生しなかった.
Serializable(シリアル化可能)
これは、トランザクションのソートを強制することで、互いに衝突しないようにする最も高い独立性レベルです.これにより、ファンタジーの問題を解決します.簡単に言えば、各読み出しデータ行に共有ロックを追加します.このレベルでは、大量のタイムアウト現象とロック競合を引き起こす可能性があります.
例:
1.独立性レベルの変更:set session tx_isolation='Serializable';
2.取引Aがデータの照会を開始する:
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 rows in set (0.00 sec)3.取引B更新データ:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(4,4);
/* A ， B */
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionインスタンスからserializableがフィールドを完全にロックしていることがわかります.前のトランザクションが完了し、ロックが解除されるまで、同じデータをクエリーするトランザクションが待機する必要があります.完全な独立性レベルであり、対応するデータテーブルがロックされるため、効率的な問題があります.
この4つの独立性レベルは異なるロックタイプで実現され,同じデータを読み取ると問題が発生しやすい.例:
ダーティリード(Drity Read):あるトランザクションが1つのデータを更新し、もう1つのトランザクションが同じデータを読み込んだ場合、いくつかの理由で前のRollBackが操作した場合、後のトランザクションが読み込んだデータは正しくありません.
再読み取り不可(Non-repeatable read):1つのトランザクションの2つのクエリでデータが一致しません.これは、2つのクエリ・プロシージャの間に1つのトランザクションが更新された元のデータが挿入されている可能性があります.
幻読(Phantom Read):1つのトランザクションの2つのクエリでデータペン数が一致しません.たとえば、1つのトランザクションがいくつかのカラム(Row)データをクエリしたのに、別のトランザクションがこの時点で新しい数列のデータを挿入した場合、前のトランザクションは次のクエリで、いくつかのカラムのデータが以前になかったことがわかります.
MySQLでは、次の4つの独立性レベルが実装されています.