2013年6月22日土曜日

トランザクションについて(その2)

前回の「トランザクションについて」に続き、 今回はコンソールを2つ使い、同時にトランザクションが実行されている場合の挙動を確認したいと思います。

別々のコンソールで同じMySQLサーバーに接続します。
仮に以降はコンソールA、コンソールBとします。

■コンソールAとコンソールBの下準備

手順1)コンソールAのauto_commitを無効化にします。
#自動コミットを無効化
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)

#設定の確認
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)
手順2)レコードを1つ追加します。
#トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

#登録
mysql> INSERT INTO user( mail_address ) VALUES( '1@gmail.com' );
Query OK, 1 row affected (0.00 sec)

#反映
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
登録データの確認。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
手順3)コンソールBのauto_commitを無効化にします。
#自動コミットを無効化
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)

#設定の確認
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)
先ほどコンソールAから作成したデータを確認する
#データベースtestを指定
mysql> use test;
Database changed

#
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
■コンソールAがトランザクション中に変更したデータがコンソールBからはどういう風に見えるのか確認

手順1)コンソールAから先ほど追加したレコードに対してUPDATEを行います。
#トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

#データの更新
mysql> UPDATE user SET mail_address = '2@gmail.com' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
手順2)コンソールBから手順1でUPDATEされたデータを確認してみる。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
あれ・・・まだUPDATEされていない!?o(゚Д゚ = ゚Д゚)o キョロキョロ

手順3)コンソールAのトランザクションを完了させる。
mysql> COMMIT;
Query OK, 0 rows affected (0.05 sec)
手順4)コンソールBから再度データを確認してみる。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
データが反映されていることを確認(゚∀゚)キタコレ!!

以上のことからコンソールAのトランザクション中に、
コンソールBからデータを確認してみても、データはまだ反映されていない状態で取得されることになります。

これはINSERTでも同じ結果になりました。

■コンソールAがトランザクション中に追加したデータがコンソールBからはどういう風に見えるのか確認

手順1)コンソールAからINSERTを行います。
#トランザクションの開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

#データの追加
mysql> INSERT INTO user( mail_address ) VALUES( '3@gmail.com' );
Query OK, 1 row affected (0.02 sec)
手順2)コンソールBから手順1でUPDATEされたデータを確認してみる。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
手順3)コンソールAのトランザクションを完了させる。
mysql> COMMIT;
Query OK, 0 rows affected (0.05 sec)
手順4)コンソールBから再度データを確認してみる。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
■コンソールAのトランザクション中に追加したデータと、コンソールBのトランザクション中に追加したデータが双方からどういう風に見えるのか確認

手順1)コンソールAのトランザクションを開始します。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
手順2)コンソールBのトランザクションを開始します。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)

手順3)コンソールAからレコードを追加する。
mysql> INSERT INTO user( mail_address ) VALUES( '4@gmail.com' );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
手順4)コンソールBからもINSERTを行いCOMMITまで行います。

追加前にデータの確認(コンソールAのレコードは反映されていない)
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO user( mail_address ) VALUES( '5@gmail.com' );
Query OK, 1 row affected (0.00 sec)
登録データの確認。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
データを反映させる。
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
データの確認
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
手順5)コンソールAから再度、データを確認してみる。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
コンソールBのINSERTは反映されていない。
コンソールAのトランザクションを終わらせてデータを確認してみる。
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
データの確認。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
データを確認してみると、コンソールBで実行されたINSERTも正しく反映されている(゚д゚)!

しかも登録した順番も、先にINSERTとしたコンソールAのデータが古くなっていることから、
INSERTした順番通りになっています。

ということは、
今回のようにINSERT自体はコンソールAの方が先に行ったけど、
コンソールBの方がトランザクションが早く終わった場合でも、
トランザクションが終わった順番は関係なく、INSERTが発生した順番になるっぽい!

次はDELETEの場合を確認してみます。

■トランザクション中にDELETEされたレコードに対して別トランザクションからの見え方について。

手順1)コンソールAでトランザクションの開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
手順2)コンソールBでトランザクションの開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
手順3)コンソールAでDELETEを行う。
mysql> DELETE FROM user WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

#レコードが消えたことを確認。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
手順4)コンソールBで確認してみる コンソールAで削除したレコードがまだある。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
手順5)コンソールAでDELETEを反映させる
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

#レコードが消えたことを確認。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
手順6)コンソールBで確認してみる。 コンソールBはまだトランザクション中のため、 コンソールAで削除したレコードの反映はまだされない。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 2@gmail.com  |
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
手順7)コンソールBでトランザクションを終わらせてから確認してみる。 コンソールBのトランザクションを終わらせたことで、 コンソールAのDELETEが反映された状態で取得が行えるようになる。
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

#コンソールAのDELETEが反映された状態で取得できる。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
■トランザクション中にDELETEされたレコードに対して別トランザクションからの操作について。 手順1)コンソールAでトランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
手順2)コンソールBでトランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
手順3)コンソールAでDELETEを実行する
mysql> DELETE FROM user WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
手順4)コンソールBでコンソールAで消したレコードと同じものをDELETEを実行する

暫く実行中になってから下記のエラーが発生します。
mysql> DELETE FROM user WHERE id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
実行中のプロセスの状態をコンソールAから見ていたら次のようになっていました。
mysql> show processlist;
+----------+------+--------------------+------+-------------+---------+-----------------------------------------------------------------------+-------------------------------+
| Id       | User | Host               | db   | Command     | Time    | State                                                                 | Info                          |
+----------+------+--------------------+------+-------------+---------+-----------------------------------------------------------------------+-------------------------------+
|      597 | mha  | 192.168.0.27:52514 | NULL | Sleep       |       2 |                                                                       | NULL                          |
|   337530 | repl | 192.168.0.22:59029 | NULL | Binlog Dump | 7507359 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                          |
| 27927550 | root | localhost          | test | Query       |       0 | NULL                                                                  | show processlist              |
| 27949582 | root | localhost          | test | Query       |      16 | updating                                                              | DELETE FROM user WHERE id = 2 |
+----------+------+--------------------+------+-------------+---------+-----------------------------------------------------------------------+-------------------------------+
4 rows in set (0.00 sec)
手順5)コンソールBでコンソールAで消したレコードに対してUPDATEを実行する

暫く実行中になってから下記のエラーが発生します。
mysql> UPDATE user SET mail_address = '2@gmail.com' WHERE id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
実行中のプロセスの状態をコンソールAから見ていたら次のようになっていました。
mysql> show processlist;
+----------+------+--------------------+------+-------------+---------+-----------------------------------------------------------------------+-----------------------------------------------------------+
| Id       | User | Host               | db   | Command     | Time    | State                                                                 | Info                                                      |
+----------+------+--------------------+------+-------------+---------+-----------------------------------------------------------------------+-----------------------------------------------------------+
|      597 | mha  | 192.168.0.27:52514 | NULL | Sleep       |       2 |                                                                       | NULL                                                      |
|   337530 | repl | 192.168.0.22:59029 | NULL | Binlog Dump | 7507503 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                                      |
| 27927550 | root | localhost          | test | Query       |       0 | NULL                                                                  | show processlist                                          |
| 27949582 | root | localhost          | test | Query       |      10 | Updating                                                              | UPDATE user SET mail_address = '2@gmail.com' WHERE id = 2 |
+----------+------+--------------------+------+-------------+---------+-----------------------------------------------------------------------+-----------------------------------------------------------+
4 rows in set (0.00 sec)
手順6)コンソールBでコンソールAで消したレコードに対してSELECTを実行する コンソールAでDELETEで消したレコードのDELETE、UPDATEは行えなかったがSELECTは可能。
mysql> SELECT * FROM user WHERE id = 2;
+----+--------------+
| id | mail_address |
+----+--------------+
|  2 | 3@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
正常に動作する(゚д゚)!

手順7)コンソールBでトランザクションを終了する。
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
まだ、コンソールAのDELETEが反映されていない。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  2 | 3@gmail.com  |
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
手順8)コンソールAでトランザクションを終了する。
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
手順9)コンソールBでデータの確認をする。

コンソールAのトランザクションが終わったことで、 コンソールA側にもDELETEが反映される。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
■トランザクション中にUPDATEされたレコードに対して別トランザクションからのUPDATEについて。 手順1)コンソールAでトランザクションを開始する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
手順2)コンソールBでトランザクションを開始する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
手順3)コンソールAでid=3のレコードを更新する
mysql> UPDATE user SET mail_address = '6@gmail.com' WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 6@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
手順3)コンソールBでコンソールAで更新したレコード同じレコードを更新する
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)
エラーになる
mysql> UPDATE user SET mail_address = '7@gmail.com' WHERE id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
トランザクションを一度終わらせて再開してからやってみても同じエラーになる。
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 4@gmail.com  |
|  4 | 5@gmail.com  |
+----+--------------+
2 rows in set (0.00 sec)

#更新はエラー
mysql> UPDATE user SET mail_address = '7@gmail.com' WHERE id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

#参照は行える
mysql> SELECT * FROM user WHERE id = 3;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 4@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
別トランザクションで更新・削除されたデータを別トランザクションで更新することは出来ない(´;ω;`)ウッ…

上記の結果からトランザクション中の追加・更新データは 別のトランザクションから操作、または変更された値の確認ができないみたいですね(゚д゚)(。_。)(゚д゚)(。_。) ウンウン

これは、MySQLのストレージエンジンInnoDBのデフォルトのトランザクションの分離レベルが、
REPEATABLE-READのためにそうなるそうです。

REPEATABLE-READでは、SELECT時にスナップショットを保持しておき、
再度読み取る場合はスナップショットを読み込むため、別トランザクションで実行された結果は反映されない。

簡単にまとめると、トランザクションを開始した時点で、
そのタイミングに反映されているデータしかそのトランザクション中には見えないってこと!(`・ω・´)シャキーン
(別のトランザクション中で追加・更新されているものは参照できない。トランザクション内で独立している。)

以上です(`・ω・´)ゞビシッ!!
参考URL

0 件のコメント:

コメントを投稿