データベースというミスの許されないものに手を加える際にはトランザクションが非常に重要となってきます。
これはSQLだけでなく、どのデータベースでも言えることです。
SQLを学習中の方にも早い段階でこのトランザクションについての理解を高めてもらいたいと思いますので、今回はこのトランザクションについて解説を進めたいと思います。
トランザクション制御が出来るようになれば人的ミスも大幅に減らすことが出来るため、優先的に学習するようにしましょう。
トランザクションとは
今回の学習を始めるにはまずこのトランザクションという言葉を理解しておく必要があります。
トランザクションというのは、簡単に言ってしまうと一連の処理単位を表します。
プログラミングには開始～完了までいくつかのフローが存在します。
具体的に例を挙げると、「商品を入荷する」「入荷した商品を計上する」「計上されたデータを基に在庫を再計算する」という流れのプログラムがあった場合、この全てがトランザクションといえます。
しかしこのたった3つの処理と言えど、どこかでエラーが起きてしまっている場合、商品がきちんと計上されなかったり、あるはずの在庫が登録されていないということが起こり得るわけです。
これをしっかりと監視し、エラーが起きた場所の特定とエラーが起きた場合にどこまでロールバックさせるかなど制御する事を「トランザクション処理」と呼びます。
トランザクション処理
トランザクションを使った制御は、次の3つのステートメントで行います。
- BEGIN TRANSACTION：トランザクションを開始する。
- COMMIT TRANSACTION：トランザクションを終了させ変更をコミットすると同時に、リソースを開放する。
- ROLLBACK TRANSACTION：BEGIN TRANSACTIONから変更された箇所をロールバックし、リソースを開放する。
使い方
それでは実際に使いながら見ていきましょう。
まず、トランザクション処理を行わなかった場合にどうなるかを検証します。
例えば次のようなテーブルがあったとします。
// ItemTable
ItemID Name Code
----------- ------------------- ------------
1 Milk J0115
2 Beef M0201
3 Pork M0101
4 Carrot B0133
5 Cabbage B0315
6 Mineral Water J0128
// GroupTable
GroupID GroupName
----------- -------------------
1 Drink
2 Meat
3 Vegetables
4 Fresh Fish
5 Fruit
// ResultTable
ResultID ItemID GroupID Stock
----------- ----------- ----------- -----------
1 1 1 70
2 2 2 121
3 3 2 48
4 4 3 84
5 5 3 30
6 6 1 242
この３つのテーブルはそれぞれ個別商品データを格納する「ItemTable」、個別商品のカテゴリを格納する「GroupTable」、在庫を格納する「ResultTable」となっています。
ここに新しく以下のスクリプトを実行して個別商品データを格納してみましょう。
INSERT INTO ItemTable
(Name, Code)
VALUES
('Rice', 'C1545');
DECLARE @ItemID INT = SCOPE_IDENTITY();
INSERT INTO ResultTable
(ItemID, GroupID, Stock)
VALUES
(6, @ItemID, 18);
GO
(1 行処理されました)
メッセージ 547、レベル 16、状態 1、サーバー DESKTOP-T3J7G2K\SQLEXPRESS、行 8
INSERT ステートメントは FOREIGN KEY 制約 "FK__ResultTab__Group__412EB0B6" と競合しています。競合が発生したのは、データベース "sample_db"、テーブル "dbo.GroupTable", column 'GroupID' です。
ステートメントは終了されました。
実行結果を見てみると、ResultTableにINSERTしようとした時点でエラーが起きていることがわかります。
これは、GroupTableのGroupIDにない「6」というGroupIDで登録しようとしたことが原因です。
ResultTableのGroupIDはREFERENCESを使って外部参照にしてあるため、GroupIDに該当するものが無い場合エラーが返されます。
この結果でテーブル内がどのように変化したかを確認するために、SELECTで検索をかけてみましょう。
// ItemTableを確認
SELECT * FROM ItemTable;
GO
ItemID Name Code
----------- ------------------- -------------
1 Milk J0115
2 Beef M0201
3 Pork M0101
4 Carrot B0133
5 Cabbage B0315
6 Mineral Water J0128
7 Rice C1545
(7 行処理されました)
// ResultTableを確認
SELECT * FROM ResultTable
GO
ResultID ItemID GroupID Stock
----------- ----------- ----------- -----------
1 1 1 70
2 2 2 121
3 3 2 48
4 4 3 84
5 5 3 30
6 6 1 242
(6 行処理されました)
ItemTableにINSERTをかけた時点ではエラーが発生していない為きちんと登録されています。
一方、ResultTableにINSERTをかけた時点でエラーが発生したため、ResultTableにはデータが登録できていないことが確認できました。
このようにトランザクション処理を行っていない場合には、データの整合性が保てなくなってしまいます。
ロールバックさせる方法
それではここからはトランザクション処理について解説していきます。
まずは先ほどのようにエラーが発生した場合にロールバックさせる方法についてです。
ロールバックを行えば、エラーが発生する直前まで安全に戻ることが出来ます。
ロールバックトランザクションの基本的な記述については次の通りです。
SET XACT_ABORT ON; BEGIN TRANSACTION; [一連の処理] COMMIT TRANSACTION;
1行目で「XACT_ABORT」を”ON”にしている点に注目してください。
これをONにすることで、エラーが発生した場合に自動でトランザクションの開始位置までロールバックを行ってくれるため、手動でROLLBACK TRANSACTIONをする必要がなくなります。
「BEGIN TRANSACTION」でトランザクションの開始位置を設定し、「COMMIT TRANSACTION」でトランザクションを終了させるので、この二つを使い先ほどのスクリプトを挟みます。
SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO ItemTable
(Name, Code)
VALUES
('Rice', 'C1545');
DECLARE @ItemID INT = SCOPE_IDENTITY();
INSERT INTO ResultTable
(ItemID, GroupID, Stock)
VALUES
(6, @ItemID, 18);
COMMIT TRANSACTION;
GO
(1 行処理されました)
メッセージ 547、レベル 16、状態 1、サーバー DESKTOP-T3J7G2K\SQLEXPRESS、行 1
INSERT ステートメントは FOREIGN KEY 制約 "FK__ResultTab__Group__412EB0B6" と競合しています。競合が発生したのは、データベース "sample_db"、テーブル "dbo.GroupTable", column 'GroupID' です。
// ItemTableを確認
SELECT * FROM ItemTable;
GO
ItemID Name Code
----------- ------------------- -------------
1 Milk J0115
2 Beef M0201
3 Pork M0101
4 Carrot B0133
5 Cabbage B0315
6 Mineral Water J0128
(6 行処理されました)
// ResultTableを確認
SELECT * FROM ResultTable
GO
ResultID ItemID GroupID Stock
----------- ----------- ----------- -----------
1 1 1 70
2 2 2 121
3 3 2 48
4 4 3 84
5 5 3 30
6 6 1 242
(6 行処理されました)
確認のためにSELECTでItemTableを検索していますが、登録されていないことが確認できました。
まとめ
今回はトランザクション処理について解説してみましたが、いかがでしたか？
トランザクションを利用すれば、簡単な記述でお手軽にロールバックできるため、エラーが排出されても焦ることはなくなります。
初心者の方になればなるほど有効な手段と成り得る為、学習中の方は早い段階でこの技術を身に着けておいた方が良いでしょう。
