第47回では、
検証環境
第10回 yum, rpmインストールにおけるMySQL 5.
また今回は、
mysql> CREATE DATABASE test;
mysql> use test
mysql> CREATE TABLE user(name varchar(20), point int );
mysql> INSERT INTO user(name, point) VALUE('sato', 0);
トランザクション分離レベルの種類
MySQLでは前回説明したとおり、SERIALIZABLE, REPEATABLE READ, READ COMMITTED, READ UNCOMMITTEDの4種類のトランザクション分離レベルが設定できます。これらの種類によって挙動がどの様に変わるかを紹介していきたいと思います。
SERIALIZABLE
このトランザクション分離レベルは、
SERIALIZABLEの挙動
SERIALIZABLEに設定した時のトランザクションの挙動を確認してみましょう。
2つのトランザクションを実行して確認をしていくのですが、
mysql> prompt txA> PROMPT set to 'txA> ' txA>
別のコンソールから
mysql> prompt txB> PROMPT set to 'txB> ' txB>
また、promptコマンドを引数を付けずに実行しましょう。
txB> prompt Returning to default PROMPT of mysql> mysql>
それでは、
txA> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; txB> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
まずはじめに両方のコンソールで設定を行います。続けてtxAでトランザクションを開始します。
txA> BEGIN; Query OK, 0 rows affected (0.00 sec) txA> SELECT * from user; +------+-------+ | name | point | +------+-------+ | sato | 0 | +------+-------+ 1 row in set (0.00 sec)
一件だけ入っていることがわかります。この状態で、
SERIALIZABLEではロック指定なしのSELECTステートメントも共有ロックを取ります。具体的には、SERIALIZABLEのSELECTはその他のトランザクション分離レベルでSELECT .. LOCK IN SHARE MODEを指定した場合と同じロックを取ります。そのことを確認してみましょう。
txB> BEGIN;
Query OK, 0 rows affected (0.00 sec)
txB> INSERT INTO user (name, point) VALUE('suzuki', 0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
結果はtxAのトランザクションのロックが解放されるのを待ってしまうため帰ってきません。ロックの解放待ちの時間はデフォルトでは50秒ですので、innodb_の時間を設定することで変更できます。
では、
txB> BEGIN;
txB> INSERT INTO user (name, point) VALUE('suzuki', 0);
# ロック待ちが発生
ロック待ちになったtxBを確認したら、
txA> COMMIT;
この時にtxBを確認してみると、
txB> INSERT INTO user (name, point) VALUE('suzuki', 0); # ロック待ちをしたコマンド
Query OK, 1 row affected (4.26 sec) # この行が増えている
txB> COMMIT;
コミットした後にSELECTを行い確認してみると、
txB> SELECT * from user; +--------+-------+ | name | point | +--------+-------+ | sato | 0 | | suzuki | 0 | +--------+-------+ 2 rows in set (0.00 sec)
このように、SERIALIZABLEを設定した場合はロック待ちが発生してしまうため、
REPEATABLE READ
このトランザクション分離レベルは、SELECTを発行したテーブルに関しては、
また、
しかし、
REPEATABLE READの挙動
ここではファントムリードが起こらないことを確認してみましょう。トランザクション分離レベルをREPEATABLE READに変更します。
txA> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; txB> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
続けてtxAでトランザクションを開始して、
txA> BEGIN; txA> SELECT * FROM user; +--------+-------+ | name | point | +--------+-------+ | sato | 0 | | suzuki | 0 | +--------+-------+ 2 rows in set (0.00 sec)
この状態でtxBでユーザを追加してみましょう。
txB> BEGIN;
txB> INSERT INTO user (name, point) VALUE('tanaka', 0);
Query OK, 1 row affected (0.00 sec)
txB> COMMIT;
txB> SELECT * from user;
+--------+-------+
| name | point |
+--------+-------+
| sato | 0 |
| suzuki | 0 |
| tanaka | 0 |
+--------+-------+
3 rows in set (0.00 sec)
ユーザが3件に増えていることがわかります。この状態で、
txA> SELECT * FROM user; +--------+-------+ | name | point | +--------+-------+ | sato | 0 | | suzuki | 0 | +--------+-------+ 2 rows in set (0.00 sec) txA> COMMIT;
2件のままになっていることがわかります。最後にコミットをしてトランザクションを終了しましょう。
txA> SELECT * from user; +--------+-------+ | name | point | +--------+-------+ | sato | 0 | | suzuki | 0 | | tanaka | 0 | +--------+-------+ 3 rows in set (0.00 sec)
トランザクションを終了すると件数が増えていることがわかります。このようにファントムリードが発生しないことがわかります。
READ COMMITTED
このトランザクションは、REPEATABLE READで説明したファントムリードになります。
ファジーリードはファントムリードとよく似ています。たとえば、
READ COMMITTEDの挙動
ここではファントムリードとファジーリードが起こることを確認してみましょう。以下のようにトランザクション分離レベルを変更します。
txA> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; txB> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
ファントムリードから確認してみましょう。txAでトランザクションを開始します。
txA> BEGIN; Query OK, 0 rows affected (0.00 sec) txA> SELECT * FROM user; +--------+-------+ | name | point | +--------+-------+ | sato | 0 | | suzuki | 0 | | tanaka | 0 | +--------+-------+ 3 rows in set (0.00 sec)
userテーブルに3件のデータが入ってることがわかります。ここでtrBでデータの挿入を行います。
txB> INSERT INTO user (name, point) VALUE('takahashi', 0);
Query OK, 1 row affected (0.00 sec)
この時にtrAに戻り、
txA> SELECT COUNT(*) FROM user; +-----------+-------+ | name | point | +-----------+-------+ | sato | 0 | | suzuki | 0 | | tanaka | 0 | | takahashi | 0 | +-----------+-------+ 4 rows in set (0.00 sec)
こちらではユーザが4件となっていて、
続いてファジーリードを試していきます。txAで一旦コミットをしてもう一度トランザクションを作成し、
txA> COMMIT; txA> BEGIN; txA> SELECT point FROM user WHERE name = 'sato'; +-------+ | point | +-------+ | 0 | +-------+ 1 row in set (0.00 sec)
現在satoさんのpointは0であることがわかりました。ここで、
txB> UPDATE user SET point=100 WHERE name='sato'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
ここでtxAに戻り確認してみましょう。
txA> SELECT point FROM user WHERE name = 'sato'; +-------+ | point | +-------+ | 100 | +-------+ 1 row in set (0.00 sec) txA> COMMIT;
txAのトランザクションの中でも更新された値が入っていることから、
最後に、REPEATABLE READで同じことを行った場合にどうなるかを簡単に確認してみましょう。
txA> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; txB> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; txA> BEGIN; txA> SELECT point FROM user WHERE name = 'sato'; +-------+ | point | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) txB> BEGIN; txB> UPDATE user SET point=100 WHERE name='sato'; txB> COMMIT; txA> SELECT point FROM user WHERE name = 'sato'; +-------+ | point | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) txA> COMMIT; txA> SELECT point FROM user WHERE name = 'sato'; +-------+ | point | +-------+ | 100 | +-------+ 1 row in set (0.00 sec)
上記のように、REPEATABLE READの場合はトランザクション中ではSELECTステートメントが同じ値を返していることがわかります。
READ UNCOMMITTED
このトランザクション分離レベルは、
ただ、
また、
READ UNCOMMITTEDの挙動
ここではダーティリードについて確認してみましょう。以下のようにトランザクション分離レベルを設定します。
txA> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; txB> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
続いて、
txA> BEGIN; txA> SELECT point FROM user WHERE name = 'suzuki'; +-------+ | point | +-------+ | 0 | +-------+ 1 row in set (0.00 sec)
現在0ポイントであることがわかりました。そこでtxBでトランザクションを作成し、
txB> BEGIN; txB> UPDATE user SET point=2000 WHERE name='suzuki'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
まだコミットをしていないため更新は確定していませんが、
txA> SELECT point FROM user WHERE name = 'suzuki'; +-------+ | point | +-------+ | 2000 | +-------+ 1 row in set (0.00 sec)
上記のようにtxAの中で確定されていない変更も読めてしまうため、
各トランザクション分離レベルのまとめ
最後にMySQLのInnoDBで、
| ダーティリード | ファジーリード | ファントムリード | |
|---|---|---|---|
| SERIALIZABLE | 発生しない | 発生しない | 発生しない |
| REPEATABLE READ | 発生しない | 発生しない | 発生しない |
| READ COMMITTED | 発生しない | 発生する | 発生する |
| READ UNCOMMITTED | 発生する | 発生する | 発生する |
まとめ
今回はトランザクション分離レベルの挙動について紹介しました。トランザクション分離レベルでは、