SQL Server の明示的なトランザクション制御方法

明示的にトランザクションを制御する

前回 「 トランザクションで処理をコントロールする 」 で、トランザクションとは何かを説明しました。

今回は実際に、シンプルなローカルトランザクション制御を実際に行ってみましょう。

トランザクションの制御を行うには、基本的に次の 3 つのステートメントで行います。

BEGIN TRANSACTION - トランザクションを開始します。
COMMIT TRANSACTION - トランザクションを終了し、データの変更をコミットし、リソースを解放します。
ROLLBACK TRANSACTION - トランザクションを開始位置からの変更をロールバックし、リソースを解放します。

トランザクションをネストさせたり、セーブポイントを作りそこまでロールバックさせたりすることもできますが、ここではこの 3 つを使ったシンプルなトランザクション制御にフォーカスします。

トランザクションで制御せずにエラーが起こるとどうなる？

例えば、次のような Student、Test、TestResult　テーブルがあります。

※ テーブルとデータは こちら のスクリプトを実行して生成可能です。

次のスクリプトを実行して、新しい学生 (Student) のデータとテスト結果 (TestResult) を 1 行ずつ生成しますが、TestID = 10 が Test テーブルに存在しない ID なので、TestResult にデータをインサートしようとした時点でエラーが起きます。

INSERT INTO Student
  (FirstName, LastName, Birthday, Gender)
 VALUES
  ('Kai', 'Sanada', '2000-06-01', 'M');

DECLARE @StudentID INT = SCOPE_IDENTITY();

INSERT INTO TestResult
    ( TestID, StudentID, Score )
VALUES
    ( 10, @StudentID, 100);

このスクリプトをトランザクションで制御せずに実行すると、Student データだけが生成され、TestResult のデータが生成されない状態になってしまいます。

[実行結果]

このような状態が起こるのを防ぐ為に、上記のスクリプトをトランザクションとして処理し、エラーが起こったらロールバックするようにしてみましょう。

データは元の状態に戻しておきます。

シンプルなトランザクション制御 1 - エラーが起こった時に、ただロールバックされれば良いケース

エラーが起こった時に、ただトランザクションの開始位置までロールバックされれば良い場合は、次のように制御を行うことができます。

SET XACT_ABORT ON;
BEGIN TRANSACTION;

	[一連の処理]

COMMIT TRANSACTION;

XACT_ABORT を ON にしておくと、エラーが起こるとトランザクションの開始位置までロールバックしてくれるので、ROLLBACK TRANSACTION を使う必要はありません。

先ほどの Student と TestResult にデータを挿入するスクリプトに、このトランザクション制御を加えたスクリプトは次の通りです。

SET XACT_ABORT ON;
BEGIN TRANSACTION;

INSERT INTO Student
  (FirstName, LastName, Birthday, Gender)
 VALUES
  ('Kai', 'Sanada', '2000-06-01', 'M');

DECLARE @StudentID INT = SCOPE_IDENTITY();

INSERT INTO TestResult
    ( TestID, StudentID, Score )
VALUES
    ( 10, @StudentID, 100);

COMMIT TRANSACTION;

[実行結果]

TestResult の挿入時にエラーが出ましたが、Student テーブルにもデータが挿入されていませんね。

念のために、TestID を 1 にして実行すると、データの挿入が成功しました。

[実行結果]

余談ですが、identiry はロールバックされないので、ID は飛んでしまいます。

シンプルなトランザクション制御 2 - エラーが起こった時に、エラーの内容を記録してロールバックしたいケース

もう一度テストデータをリセットして、次はエラーが起こった時に、エラーの内容を記録してロールバックする方法です。

エラーの内容を取得して保存したいような時は、トランザクションを TRY CATCH と一緒に使うと取得することができます。

次のような ProcessLog テーブルを用意して、エラーが起こった場合に日時と取得したエラーメッセージを保存するようにしてみましょう。

CREATE TABLE ProcessLog (
	ProcessLogID	INT NOT NULL IDENTITY PRIMARY KEY,
	ProcessDateTime DATETIME,
	ErrorMessage	NVARCHAR(MAX) NULL
);

先ほどの Student と TestResult にデータを挿入するスクリプトに、このトランザクション制御を加えたスクリプトは次の通りです。

エラーが起こらないで処理が成功して TRY の最後にたどり着いた場合には、COMMIT TRANSACTIONが、エラーが起こった場合には CATCH に飛んで、エラー情報を取得した後で、ROLLBACK TRANSACTION が実行されるようになっています。

BEGIN TRY

	BEGIN TRANSACTION;
	
	INSERT INTO Student
	  (FirstName, LastName, Birthday, Gender)
	 VALUES
	  ('Kai', 'Sanada', '2000-06-01', 'M');

	DECLARE @StudentID INT = SCOPE_IDENTITY();

	INSERT INTO TestResult
		( TestID, StudentID, Score )
	VALUES
		( 10, @StudentID, 100);			
		
	COMMIT TRANSACTION;
				
END TRY
BEGIN CATCH
		
	DECLARE @ErrorMessage NVARCHAR(MAX)
						= 'ErrorNumber:' + ISNULL(CAST(ERROR_NUMBER() AS NVARCHAR), '')
						+ ' ,ErrorSeverity:' + ISNULL(CAST(ERROR_SEVERITY() AS NVARCHAR), '')
						+ ' ,ErrorState:' +  ISNULL(CAST(ERROR_STATE() AS NVARCHAR), '')
						+ ' ,ErrorProcedure:' + ISNULL(ERROR_PROCEDURE(), '')
						+ ' ,ErrorLine:' + ISNULL(CAST(ERROR_LINE() AS NVARCHAR), '')
						+ ' ,ErrorMessage:' + ISNULL(ERROR_MESSAGE(), '') + ';';

	ROLLBACK TRANSACTION;
					
	INSERT INTO ProcessLog 
		( ProcessDateTime, ErrorMessage )
	VALUES	
		( GETDATE(), @ErrorMessage );
			
END CATCH

[実行結果]

エラーを CATCH して処理しているため、スクリプトの実行ではエラーが起こらず、エラーの内容が ProcessLog テーブルに保存され、Student テーブルも TestResult テーブルも更新されませんでしたね。

念のため、TestID を 1 に変更して実行したところ、データが成功し、ProcessLog テーブルのレコードは増えませんでした。

[実行結果]