新鮮

頑張らないために頑張る

MySQL は必ずしもデータの一貫性を担保しない(トランザクション分離レベル)

データベース系の資格といえば ORACLE MASTER が有名ですが、実は MySQL にも ORACLE MASTER 相当の資格があります。

  • (1Z0-882) Oracle Certified Professional, MySQL 5.6 Developer
  • (1Z0-883) Oracle Certified Professional, MySQL 5.6 Database Administrator

諸般の事情で MySQL 5.6 Developer をとらなければならなくなったので学習メモを残しておきたいと思います。資格の勉強をする方や MySQL を利用する方の参考になればと思います。

今回は「トランザクション分離レベル」です。

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

Wikipediaより引用。

あるデータに対する読み書きの処理を行う場合、わずかでも処理時間が発生する。処理が「複数同時に並行して」実行されようとした場合、感覚的にはどちらかの処理が先に行われ、残ったほうの処理が後に行われるであろう。この場合、後に行われた処理は先に行われた処理が完了するまでの間「待ち」の状態になってしまう。

データベース管理システムはこれらの「待ち」の状態を可能な限り防ぐため、複数の処理を並列で行っている間でもその他の処理を受け付けられる制御方法が確立された。このとき、1つのトランザクション処理が他の処理からどれだけ独立して行われるかが焦点になる、すなわち、「待ち時間を減らすためどれだけデータの一貫性を犠牲にして良いか」を定めたものが、トランザクション分離レベルである。

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

分離レベルと発生事象の関係

分離レベル Dirty Read Non-Repeatable Read Phantom Read
Serializable 発生しない 発生しない 発生しない
Repeatable Read 発生しない 発生しない 発生する
Read Committed 発生しない 発生する 発生する
Read Uncommitted 発生する 発生する 発生する

それぞれの事象は以下の通り。

事象 内容
Dirty Read 別トランの未コミットデータを読みとる事象
Non-Repeatable Read 別トランで INSERT したデータを読み取る事象
Phantom Read 別トランで UPDATE したデータを読み取る事象

前準備

mysql> create table t (id int, a int, primary key(id));
mysql> insert into t (id,a) values (1,10);
mysql> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   10 |
+----+------+

Dirty Read を試してみる

トランザクション分離レベル Read Uncommitted

Tx1> set session transaction isolation level read uncommitted;
Tx1> show variables like 'tx_isolation';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+

トラン Tx1 を開始する。

Tx1> start transaction;
Tx1> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   10 |
+----+------+

別のターミナルでトラン Tx2 を開始して 10 を 20 に変える。

Tx2> update t set a=20 where id=1; /* 未コミット */

トラン Tx1 で再度 select する。

Tx1> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   20 |
+----+------+

20 になる。一貫性はなく、未コミットデータが別トランでみられてしまう。

トランザクション分離レベル Repeatable Read

Tx1> set session transaction isolation level repeatable read;
Tx1> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

トラン Tx1 を開始する。

Tx1> start transaction;
Tx1> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   10 |
+----+------+

別のターミナルで Tx2 を開始して 10 を 20 にする。

Tx2> update t set a=20 where id=1;

トラン Tx1 で再度 select する。

Tx1> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   10 |
+----+------+

10のまま。一貫性があり、未コミットデータは別トランで見られない。

Phantom Read を試してみる

トランザクション分離レベル Read Committed

Read-Committed でトラン Tx1 を開始する。

Tx1> set session transaction isolation level read committed;
Tx1> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+

Tx1> start transaction;
Tx1> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   10 |
+----+------+

トラン Tx2 を開始して update 。トラン Tx1 で再度 select してみる。

Tx2> start transaction;
Tx2> update t set a=20 where id=1;
Tx1> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   10 |
+----+------+

a=10 のままで一貫性は保たれている。トラン Tx2 をコミットする。

Tx2> commit;
Tx1> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   20 |
+----+------+

a=20 で一貫性が失われた。

トランザクション分離レベル Repeatable Read

mysql> prompt Tx1>
Tx1> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

トラン Tx1 を開始する。

Tx1> start transaction;
Tx1> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   10 |
+----+------+

別のターミナルでTx2を開始して 10 を 20 に変える。

mysql> prompt Tx2>
Tx2> update t set a=20 where id=1;
Tx2> commit;

トランザクションTx1で再度 select する。

Tx1> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |   10 |  /* REPEATABLE-READ では Phantom Read が発生してない*/
+----+------+

10 のまま。トランザクション開始したあとの select 結果に一貫性がある。

試験用チェックリスト

  • MySQLトランザクション分離レベルは repeatable read (デフォルト)
  • 一般的な repeatable read とは違いファントムリードも発生しない
  • ファントムリード: 別トランザクションによる追加・削除で参照結果が変わる事象