2013年6月22日土曜日

トランザクションについて

いまさらですが・・・今回は基本的なトランザクション動作の復習を兼ねてφ(`д´)メモメモ...

まずは、通常のINSERT時の挙動の確認。

データベースにはMySQLインストール時に勝手に作られている
「test」というデータベースを利用します。
テーブル名は「user」を利用します。

■通常のデータ登録の挙動確認(autocommitがONの場合)

・データベースtestを指定する。
mysql> use test;
Database changed
・テーブルが無い事を確認する
mysql> show tables;
Empty set (0.00 sec)
・userテーブルを作成する。
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mail_address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;
・テーブルが作成されたことを確認する。
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)
・データの登録を行う。
INSERT INTO user( mail_address ) VALUES( 'test@gmail.com' );
・登録されたデータの確認を行う。
mysql> SELECT * FROM user;
MySQLの設定には、autocommitと呼ばれる設定値があり、
この設定がONの場合には、INSERTやUPDATEなど各SQLステートメントの直後に、
自動でトランザクションのCOMMITが実行されるようになっています。

autocommitが無効でも自動的にコミットされてしまうSQLがあるそうです。
どこかのサイトからメモったものを載せておきます!(; ・`д・´)
AUTO-COMMITを無効にした場合でも暗黙的にコミットされてしまうSQL文

DDL文全般(CREATE,ALTER,DROPコマンド)
権限管理コマンド(GRANT,REVOKE,CREATE USER,DROP USER,SET PASSWORDなど)
テーブルロック関係(LOCK TABLES, UNLOCK TABLES)
トランザクションの操作(SET autocommit=1,BEGIN,START TRANSACTION)
その他の管理系のコマンド(ANALYZE TABLE, CHECK TABLE, REPAIR TABLE, OPTIMIZE TABLEなど)
InnoDBに加えてMyISAMも併用している場合に、テーブルロックが必要な場面がある時に、
トランザクション中はテーブルロックを利用しないように注意すること。

今行ったINSERTがautocommitの設定がONの状態だったことを確認します。
・現在のautocommit設定の確認
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
autocommitが1なので自動commitがされるような設定になっています。
そのため、INSERTしたデータがすぐに反映されるようになっています。

■通常のデータ登録の挙動確認(autocommitがOFFの場合)

・autocommitをOFFにする。
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)
autocommitがOFFの場合には、
明示的にトランザクションを利用しない限りはデータが登録されません。

たとえば、先ほどの手順で登録を行ってみてもデータが反映されないです。

新しいデータを登録する
mysql> INSERT INTO user( mail_address ) VALUES( 'autocommit_off@gmail.com' );
Query OK, 1 row affected (0.00 sec)
確認すると、データは正常に登録されていますが、
AUTOCOMMITがOFFの状態なので、「Ctrl-C」などでMySQLから抜けたりするとデータが反映されずに、終了します。
(ROLLBACKが実行された扱いになる?)
mysql> SELECT * FROM user;
+----+--------------------------+
| id | mail_address             |
+----+--------------------------+
|  1 | test@gmail.com           |
|  2 | autocommit_off@gmail.com |
+----+--------------------------+
2 rows in set (0.00 sec)
MySQL終了
mysql> Ctrl-C -- exit!
Aborted
MySQLに再接続
mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27894244
Server version: 5.5.30-log MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
データベースtestを指定する。
mysql> use test;
Database changed
userテーブルの中身を確認する。
mysql> SELECT * FROM user;
+----+----------------+
| id | mail_address   |
+----+----------------+
|  1 | test@gmail.com |
+----+----------------+
1 row in set (0.00 sec)
これは、autocommitがOFFのために、明示的にCOMMITを実行しない限り、
データがテーブルに反映されないためです。

次は、INSERT後にCOMMITを実行して確認してみます。
そのまえに、MySQLを一度抜けたため、先ほどOFFにしたautocommitがデフォルトのONに戻ってしまっているのを戻す必要があります。 (先ほど設定した方法ではセッション単位で有効な設定のため)
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)
データの登録を行う。
mysql> INSERT INTO user( mail_address ) VALUES( 'autocommit_off@gmail.com' );
Query OK, 1 row affected (0.00 sec)
INSERTの確認
mysql> SELECT * FROM user;
+----+--------------------------+
| id | mail_address             |
+----+--------------------------+
|  1 | test@gmail.com           |
|  3 | autocommit_off@gmail.com |
+----+--------------------------+
2 rows in set (0.00 sec)
COMMITを実行する
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
MySQLを終わらせる。
mysql> Ctrl-C -- exit!
Aborted
MySQLに接続
$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27922176
Server version: 5.5.30-log MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
データベースtestを指定する。
mysql> use test;
Database changed
データの確認
mysql> SELECT * FROM user;
+----+--------------------------+
| id | mail_address             |
+----+--------------------------+
|  1 | test@gmail.com           |
|  3 | autocommit_off@gmail.com |
+----+--------------------------+
2 rows in set (0.00 sec)
今度はデータが消えずに登録が完了しました( ´∀`)bグッ!

ここで確認するべき項目として、
auto_increment属性がついているidカラムの値になります。

auto_incrementはデータのINSERT時に連番を自動で挿入してくれるものですが、
データ的には2個しか無いのにidが3になってしまっています。

これは、1つ前の手順でCOMMITを実行せずにMySQLを終わらせてしまった場合でも、
auto_increment自体は1つ進んでしまう事を示します。

たとえば、トランザクションを実行したときに、
ROLLBACKを実行した場合でも同様の動きになります。

■トランザクションの手順を踏んだデータの登録方法

先ほどの手順でautocommitが0の場合には、
明示的にCOMMITを実行しなければデータが反映されない点について確認しましたが、
この手順ではトランザクションの一部の手続きが省略されています。
(MySQLはBEGINは明示的に実行せずともトランザクションは開始されるようになっている)

そこで次は正しいトランザクション手続きの方法を見ていきたいと思います。

今まででの手順では、トランザクション処理を操作するコマンドとして、
COMMITのみを利用しましたが、実際は他にもBEGIN(START TRANSACTION)、ROLLBACKがあります。

各コマンドの意味は下記の通りになります。

BEGINは、トランザクションを開始する場合に実行するコマンド。
COMMITは、トランザクション中に行った作業を全て反映させる場合に実行するコマンド。
ROLLBACKは、トランザクション中に行った作業を全て戻す場合に実行するコマンド。

では、トランザクションの各コマンドを明示しながらINSERTを行っていきたいと思います。

まず、トランザクション開始を行います。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
データの登録。
mysql> INSERT INTO user( mail_address ) VALUES( 'transaction@gmail.com' );
Query OK, 1 row affected (0.00 sec)
データの確認
mysql> SELECT * FROM user;
+----+--------------------------+
| id | mail_address             |
+----+--------------------------+
|  1 | test@gmail.com           |
|  3 | autocommit_off@gmail.com |
|  4 | transaction@gmail.com    |
+----+--------------------------+
3 rows in set (0.00 sec)
トランザクション完了
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
トランザクションを明示した場合には、
上記のような手順になります。

次はROLLBACKを試してみたいと思います。

トランザクション開始を行います。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
データの登録
mysql> INSERT INTO user( mail_address ) VALUES( 'rollback1@gmail.com' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO user( mail_address ) VALUES( 'rollback2@gmail.com' );
Query OK, 1 row affected (0.00 sec)
データの確認
mysql> SELECT * FROM user;
+----+--------------------------+
| id | mail_address             |
+----+--------------------------+
|  1 | test@gmail.com           |
|  3 | autocommit_off@gmail.com |
|  4 | transaction@gmail.com    |
|  5 | rollback1@gmail.com      |
|  6 | rollback2@gmail.com      |
+----+--------------------------+
5 rows in set (0.00 sec)
トランザクションをキャンセル。
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
データの確認してみると、トランザクションの開始前に戻っています!(`・ω・´)シャキーン
mysql> SELECT * FROM user;
+----+--------------------------+
| id | mail_address             |
+----+--------------------------+
|  1 | test@gmail.com           |
|  3 | autocommit_off@gmail.com |
|  4 | transaction@gmail.com    |
+----+--------------------------+
3 rows in set (0.00 sec)
※ROLLBACKを行った場合でも、auto_increment値は進むので、
次のINSERT時には7からになる。

次に使われるauto_increment値は次のコマンドで確認できます。
mysql> SHOW TABLE STATUS LIKE 'user'\G;
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 7
    Create_time: 2013-06-21 14:20:02
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified
MySQLはBEGINは明示的に実行せずともトランザクションは開始されるようになっているので、
次のようにいきなりコマンドでデータの登録をした場合でも、ROLLBACKが適用される。 (autocommitがOFFの場合のみ)
mysql> INSERT INTO user( mail_address ) VALUES( 'rollback3@gmail.com' );
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM user;
+----+--------------------------+
| id | mail_address             |
+----+--------------------------+
|  1 | test@gmail.com           |
|  3 | autocommit_off@gmail.com |
|  4 | transaction@gmail.com    |
|  7 | rollback3@gmail.com      |
+----+--------------------------+

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

mysql> SELECT * FROM user;
+----+--------------------------+
| id | mail_address             |
+----+--------------------------+
|  1 | test@gmail.com           |
|  3 | autocommit_off@gmail.com |
|  4 | transaction@gmail.com    |
+----+--------------------------+
4 rows in set (0.00 sec)
ROLLBACKが正常に動作する。(`・ω・´)シャキーン

今回は基本的なトランザクションの流れについて確認したので、
次回は、同時にトランザクションが実行されたときの挙動の確認をしたいと思います。

以上(`・ω・´)ゞビシッ!!

参考URL

0 件のコメント:

コメントを投稿