Hatena::ブログ(Diary)

FAT47の底辺インフラ議事録

2014-02-12

MySQLでトランザクションの4つの分離レベルを試す

f:id:fat47:20140212110420j:image

トランザクションとは

1つの作業単位として扱われるSQLクエリの集まりです。

複数のUPDATEやINSERTをひとつの集まりとして、

それらのクエリがすべて適用できた場合のみデータベースに反映します。

ひとつでも適用に失敗したクエリがあった場合は、そのまとまりすべてのクエリの結果は反映しません。

ACID特性

トランザクション処理に求められる4つの特性です。

原子性 (Atomicity)

トランザクションに含まれる手順が「すべて実行されるか」「すべてされないか」のどちらかになる性質。

一貫性 (Consistency)

どんな状況でもトランザクション前後でデータの整合性が矛盾なく保たれる性質。

分離性 (Isolation)

トランザクション実行中は、処理途中のデータは外部から隠蔽されて他の処理に影響を与えない性質。

永続性 (Durability)

トランザクションが完了したら、システムがクラッシュしてもデータが消失することがない性質。

分離レベル

MySQLに限らずSQL規格に則したデータベースでは4つの分離レベルを定義していて、

トランザクションの内外で見える変更と見えない変更のレベルを設定できます。

一般的に分離レベルが低いほど処理の並行性が高くなり処理速度向上が期待できますが、

その分データの不整合などのリスクが発生します。

READ UNCOMMITTED

一番低い分離レベルです。

コミットされていないトランザクションの結果が他のトランザクションから見える設定です。

特にパフォーマンスが優れているわけではないので、使用されることは滅多にないそうです。

コミット前のデータを読み取ることはダーティリードと呼ばれています。

f:id:fat47:20140212110608p:image

上図では、トランザクション1が商品の売上をINSERT処理している途中で、トランザクション2で

商品価格を800円に変更をしています。このトランザクション2の値はROLLBACKされたら、

値が戻る可能性もある非常に不安定な値となっています。

READ COMMITTED

多くのデータベースシステム(OraclePostgreSQLSQL Server)でデフォルトの分離レベルになっています。

この分離レベルで見える値は、このトランザクションが開始されたときに既にコミットされている値だけで、

このトランザクション内の値もコミットするまで他からは見えない状態になっています。

f:id:fat47:20140212110827p:image

分離レベルREAD UNCOMMITTEDで発生していたダーティリードは発生しません。

トランザクション2の値がコミットされるまでは、変更前の値を参照しつづけます。

しかしREAD COMMITTEDでは、次のような順番でSQLが発行された場合この状況が発生する可能性があります。

f:id:fat47:20140212110850p:image

トランザクション1のSQL:1で取得される商品番号111の価格は400円ですが、

SQL:3で取得される商品の価格は800円になります。

このようにトランザクション1を実行中にトランザクション2の処理が完了してしまい、

トランザクション内で同じ値を参照しているのに数値が変わる現象をファジーリード(またはノンリピータブルリード)と呼びます。

同じ問い合わせを2回繰り返しても、同じ結果になるとは保証できないということです。

REPEATABLE READ

MySQLのデフォルトの分離レベルです。

なぜMySQLだけがREPETABLE READをデフォルトにしているのかわからないので、どなたか詳しい人教えて下さい。

この分離レベルはトランザクション内が読み取る値が、そのトランザクション内では同じに見える事が保障されます。

f:id:fat47:20140212110919p:image

分離レベルREAD COMMITTEDで発生していたファジーリードは発生しません。

トランザクション1で参照される価格の値は、トランザクション1が開始された時点の価格を参照し続けるので

何度問い合わせをしても同じ値となります。

しかしREPEATABLE READもある条件によっては問題が発生する可能性があります。

f:id:fat47:20140212110942p:image

トランザクション1は今までと同じように売上げた商品をテーブルにINSERTしています。

その途中で、今日1日の売上価格を計算しているトランザクション2の処理が開始されます。

トランザクション2のSQL:Aでは対象の売上件数は10件で価格の合計計算を行います。

その後にトランザクション1の処理が完了し変更がコミットされ、トランザクション2のSQL:BでCOUNTをすると

レコード数は11件となり、ひとつのトランザクション内で対象レコード数が異なってしまいます。

トランザクション中に幻影のように新しい行が現れる事から、ファントムリードと呼ばれています。

※MySQLのストレージエンジンInnoDBでは、MVCC(MultiVersion Concurrency Control)という仕組みで

ファントムリードを防いでいます。

ここはまだいまいち理解できてないので、後ほど調べます。

SERIALIZABLE

最も高い分離レベルです。

複数トランザクションのSQLが入り混じらないように、強制的にトランザクションを順序付けて処理します。

今まで出てきたすべての問題は発生しませんが、この分離レベルは読み取るすべての行にロックをかけます。

ロック競合が多発することになるのであまりこのレベルが使用されることはありません。


分離レベルと問題の関係

分離レベルダーティリードファジーリードファントムリード読み取りロック
READ UNCOMMITTEDYesYesYesNo
READ COMMITTEDNoYesYesNo
REPEATABLE READNoNoYesNo
SERIALIZABLENoNoNoYes

実際にMySQLで分離レベルの問題を発生させてみる

テーブルの作成

mysql> use test;
mysql> create table level_test(id INT(11) NOT NULL PRIMARY KEY,user_name varchar(32) not null );
Query OK, 0 rows affected (0.14 sec)

mysql> desc level_test;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | NULL    |       |
| user_name | varchar(32) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

テストデータ挿入

mysql> insert into level_test values (1, 'hoge');
mysql> select * from level_test;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | hoge      |
+----+-----------+
1 row in set (0.00 sec)

ストレージエンジン確認

トランザクションを利用できるInnoDBになっているか。

mysql> select table_name, engine from information_schema.tables where table_schema = "test";
+------------+--------+
| table_name | engine |
+------------+--------+
| level_test | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

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

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

※MySQLのデフォルトのREPETABLE READになっています。


ダーティリード

READ UNCOMMITTEDで確認します。

※端末を2つ立ち上げます。

mysql1> SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;
mysql2> SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;

mysql1> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

mysql2> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

myqsl1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql1> UPDATE level_test SET user_name = 'fuga' WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

myqsl2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql2> SELECT * FROM level_test;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | fuga      |
+----+-----------+
1 row in set (0.00 sec)

コミット前の変更(hoge→fuga)が別トランザクションから参照できている!


ファジーリード(アンリピータブルリード)

READ COMMITTEDで確認します。

※うまく分離レベルを変更できない事があったのでmysql端末を立ち上げなおして下さい。

mysql1> SET SESSION TRANSACTION ISOLATION LEVEL read committed;
Query OK, 0 rows affected (0.00 sec)

mysql2> SET SESSION TRANSACTION ISOLATION LEVEL read committed;
Query OK, 0 rows affected (0.00 sec)

mysql1> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql2> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql1> UPDATE level_test SET user_name = 'fuga' WHERE id = 1;

mysql2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql2> SELECT * FROM level_test WHERE id = 1; 
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | hoge      |
+----+-----------+
1 row in set (0.00 sec)

mysql1> COMMIT;

mysql2> SELECT * FROM level_test WHERE id = 1;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | fuga      |
+----+-----------+
1 row in set (0.00 sec)

トランザクション2内での値が変わっている!


ファントムリード

前述のとおりInnoDBはREPEATABLE READでファントムリードが起きないため、

READ COMMITTEDで確認します。

mysql1> SET SESSION TRANSACTION ISOLATION LEVEL read committed;
Query OK, 0 rows affected (0.00 sec)

mysql2> SET SESSION TRANSACTION ISOLATION LEVEL read committed;
Query OK, 0 rows affected (0.00 sec)

mysql1> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql2> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql2> SELECT COUNT(*) FROM level_test;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

mysql1> INSERT INTO level_test VALUES(2,"boo");

mysql1> COMMIT;

mysql2> SELECT COUNT(*) FROM level_test;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

トランザクション内2のCOUNTの結果が異なっている!


本当にREPEATABLE READでファントムリードが起きないか確認

端末を開き直す。

MySQLのデフォルトなのでREPEATABLE READになります。

mysql1> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql2> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql2> SELECT COUNT(*) FROM level_test;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

mysql1> INSERT INTO level_test VALUES(2,"boo");

mysql1> COMMIT;

mysql2> SELECT COUNT(*) FROM level_test;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

件数かわってない!!


今まであまり意識せずに利用してしまっていましたが、

分離レベルってものすごく重要ですね。

今回の内容は下記の書籍で解説されています。

PEASEPEASE 2015/10/28 16:01 >なぜMySQLだけがREPETABLE READをデフォルトにしているのかわからないので、どなたか詳しい人教えて下さい。
ふと気になったので調べてみました。
MySQLの5.1まではステートメントベースのレプリケーションのみを使っていて、
READ COMMITTEDがNGだったという歴史の名残のようです。

■InnoDB : Why not use the same isolation level as ORACLE ?
http://serge.frezefond.com/2010/01/innodb-why-not-use-the-same-isolation-level-as-oracle/

>The reason why InnoDB use REPEATABLE READ as its default is historical. This is a related to the way MySQL replication was developed . MySQL replication until 5.1 functioned with a statement based replication mechanism. This means that statements that occurs on the master server are replayed on the slave server. The statement base replication mode does not permit to use the READ COMMITTED isolation level. In that case replication will not guaranty consistency between the slave and the master.

■MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels?
http://dba.stackexchange.com/questions/101122/mysql-why-statement-based-binlog-format-cannot-work-with-innodb-read-uncommitte

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/fat47/20140212/1392171784