本項では、前回の「【SQL入門】トランザクションとは」で出てきた「COMMIT」と「ROLLBACK」について説明していきます。
コミット（COMMIT）とは
トランザクション中の更新などのデータ処理の結果は、仮のものとして扱われ確定していません。
コミットが実行されると、この仮だったものが確定しデータベースに結果が反映されトランザクションが終了します。
当然、一度コミットすると、もう処理を取り消してデータをもとに戻すことができないので十分な注意が必要です。
また、コミットされる前の仮の状態のデータは、外部（別のトランザクション）からは見ることができません。
では、実際に、以下のようなテストの点数を記録するようなテーブルを新たに作成後、データを挿入しコミットして動きを確認してみましょう。
CREATE TABLE score ( id INT(11) AUTO_INCREMENT, name VARCHAR(50), subject VARCHAR(50), score INT(3), PRIMARY KEY (id) );
テーブルを作成したら、まずは、トランザクションの開始を宣言し、データの挿入処理まで実行し、SELECT文を実行してみます。
START TRANSACTION;
INSERT INTO score (name, subject, score)
VALUES ("タロウ", "数学", 80);
INSERT INTO score (name, subject, score)
VALUES ("サクナ", "数学", 90);
SELECT *
FROM score;
上記図のようにタロウとサクナの数学のテストの点数のデータが挿入されていることが確認できると思います。
では、今度は、今MySQLに接続しているのとは別に、もう一つ「MySQL 8.0 Command Line Client」を立ち上げて
同じようにscoreテーブルに対しSELECT文を実行してみましょう。
SELECT * FROM score;
すると上記画像のように新しく立ち上げたほうではscoreテーブルは作成直後の空の状態のままとなっていることが確認できます。
次は、1つ目の接続でコミットを実行してから、2つ目のほうでもう一度SELECT文を実行してみましょう
COMMIT;
SELECT * FROM score;
今度はコミットされ処理が確定したため、2つ目のほうでもタロウとサクナのデータが挿入されてたことが確認できました。
ロールバック（ROLLBACK）とは
ロールバックは実行されるとトランザクション中の処理が取り消され、トランザクション実行前の状態に戻りトランザクションが終了します。
また、明示的に実行する以外にも、データベースとの接続が切断される等、何らかの原因でトランザクション中の処理が中断された場合にもロールバックが実行されます。
例として、先ほどの挿入したscoreテーブルのデータを削除してからロールバックしてみましょう。
START TRANSACTION; DELETE FROM score; SELECT * FROM score; ROLLBACK; SELECT * FROM score;
ロールバックを実行すると、トランザクション中のDELETE文の処理が取り消されて、元の状態に戻っていることが確認できました。
自動コミットモード
MySQLでは、「START TRANSACTION」（もしくは「BEGIN」）でトランザクションの開始を宣言しない場合、
例えばINSERT文を実行するとコミットを実行しなくても結果が確定し反映されています。ロールバックも行うことができません。
これはなぜかというと、デフォルトで自動コミットモードがONに設定されているからです。
自動コミットモードとは、文字通り自動的にコミットする機能となり、ONに設定されていると１つのSQL文を実行するたびに自動でコミットが実行されます。
この状態で「START TRANSACTION」でトランザクションの開始を宣言すると、自動コミットモードがOFFな状態で新しいトランザクションが開始され複数のSQL文を一つのトランザクションとして扱うことができるようになります。
そして、コミットまたはロールバックでトランザクションを終了すると自動コミットモードがONの状態へと戻ります。
また、明示的に自動コミットモードをOFFにしたい場合は、下記の構文で変更できます。
SET autocommit=0; -- ONにする場合は1を設定
自動コミットモードがOFFのときは、トランザクションが開始していない状態でSQL文を実行すると暗黙的にトランザクションが開始され、
自身でコミットまたはロールバックを実行する必要があります。
また、この構文での自動コミットモードの変更は、変更を実行した接続でのみで有効です。
新たに接続しなおす等、別の接続では自動コミットモードはONの状態に戻ります。
では、実際に自動コミットモードをOFFにした状態で、先ほど実行したロールバック処理を「START TRANSACTION;」なしで実行してみましょう。
SET autocommit=0; DELETE FROM score; SELECT * FROM score; ROLLBACK; SELECT * FROM score;
まず初めに、自動コミットモードをOFFにしてからDELETE文を実行しています。
暗黙的にトランザクションが開始され、ロールバックできたことが確認できました。
今回の学習ポイント
・トランザクション中のデータ処理を確定するにはコミットを実行する
・トランザクション中のデータ処理を取り消すにはロールバックを実行する
・トランザクション中のデータ内容は、コミットもしくはロールバックするまで外部からは確認できない。
・MySQLでは、デフォルトでは自動コミットモードがONとなっており、１つのSQL文ごとに暗黙的にトランザクションが実行されコミットされている。
練習問題
以下からダウンロード可能です。
また、ダウンロードしたら以下のようにしてworldcup2014データベースを作成し、インポートしてください。
※worldcup2014.zip解凍してできるworldcup2014.sqlファイルをCドライブ直下に配置した場合。 [sql] CREATE DATABASE worldcup2014;
use worldcup2014
source C:\worldcup2014.sql
[/sql]
１．現在接続しているMySQLの自動コミットモードを明示的にONに設定してください。
[/sql]
このようにautocommitを1に設定すると、明示的に自動コミットモードをONに設定できます。
また、現在の自動コミットモードがONかOFFかどちらか確認するには以下の構文をしようすると確認できます。
[/sql]
２．クロアチア(countries.id=4)がAグループからBグループに、スペイン(countries.id=5)がBグループからAグループに変更になりました。
また、ブラジルの二人の身長が間違っており、正しくはジュリオセザール選手(players.id=1)が183cm、ジェフェルソン選手(players.id=2)の身長が185cmでした。
それぞれ1つのトランザクションにまとめて計2つのトランザクションでデータを修正してください。
UPDATE countries SET
group_name = ‘B’ WHERE id=4;
UPDATE countries SET
group_name = ‘A’ WHERE id=5;
COMMIT;
START TRANSACTION;
UPDATE players
SET height = 183 WHERE id=1;
UPDATE players
SET height = 185 WHERE id=2;
COMMIT;
[/sql]
グループと身長に対するUPDATE文それぞれを「START TRANSACTION」～「COMMIT」で囲んで別々のトランザクションで実施しています。
「START TRANSACTION」は、「BEGIN」でもかまいません。
また、以下のように「SET autocommit=0;」で自動コミットモードをOFFにしてもかまいません。
UPDATE countries
SET group_name = ‘B’ WHERE id=4;
UPDATE countries
SET group_name = ‘A’ WHERE id=5;
COMMIT;
UPDATE players
SET height = 183 WHERE id=1;
UPDATE players
SET height = 185 WHERE id=2;
COMMIT;
[/sql]
※確認が終わったら以下SQL文で、データを戻しておいてください。
[sql] SET autocommit=1;UPDATE countries
SET group_name = ‘A’ WHERE id=4;
UPDATE countries
SET group_name = ‘B’ WHERE id=5;
UPDATE players
SET height = 186 WHERE id=1;
UPDATE players
SET height = 188 WHERE id=2;
[/sql]
もしくは、以下でデータベース事作り直してください。（worldcup2014.sqlファイルがCドライブ直下に配置されている前提）
[sql] DROP DATABASE worldcup2014;CREATE DATABASE worldcup2014;
use worldcup2014
source C:\worldcup2014.sql
[/sql]
３．自動コミットモードをOFFにして作業をしています。
players_tmpテーブルのデータを一旦全削除するつもりでしたが、間違えてpairings_tmpテーブルを削除したのでデータをロールバックしました。
どのようなSQLになりますか？
最初に自動コミットモードをOFFにしてデータ削除からロールバックまでを実施てください。
DELETE FROM pairings_tmp;
ROLLBACK;
[/sql]
間違えてpairings_tmpテーブルを削除したので上記のSQLが答えとなります。
きちんとロールバックできているかSELECT文で確認してみましょう。
自動コミットモードをOFFすると、明示的にCOMMITかROLLBACKするまでトランザクションが終了しない為、ロールバックすることができています。
※もし、ロールバックに失敗したら以下でデータベース事作り直してください。（worldcup2014.sqlファイルがCドライブ直下に配置されている前提）
[sql] DROP DATABASE worldcup2014;CREATE DATABASE worldcup2014;
use worldcup2014
source C:\worldcup2014.sql
[/sql]
