第 1 章 データベース トランザクション処理～ SQL Server 2000 における Web アプリケーション開発 ～

NRI ラーニング   ネットワーク株式会社
技術研修部

沖要知

2001 年 11 月 22 日

はじめに

データベースシステムほど厳密に扱われないかもしれませんが、実社会はトランザクション処理にあふれています。 

私は本が好きなので、ブックストアにはよく足を運びますが、例えば、待望の   「独習 C# 」   という書籍が出荷されたとしましょう。早速、店に行き書棚から   「独習 C# 」   を見つけ出し、そそくさとレジまで持って行って店員に、「これください」   と伝えます。そうです、ここからがトランザクションの始まりなのです。店員から、「 3 , 800 円です」   と伝えられ、財布にあった 5 , 000 円札を取り出し手渡します。そして、袋に収めてもらった   「独習 C# 」   と領収書および 1 , 200 円の釣銭を受け取りつつ、突然頭の中では、今月号の VB Magazine の発売期間が今日までなのに、実はまだ購入していなかったことを思い出してしまいます。そこで私は店員に、「あと、 VB Magazine もください」   と伝えるのですが、残念ながら財布の中身は受け取った釣銭の 1 , 200 円しかありません。店員からは、「 VB Magazine は、 1 , 380 円ですから、合計で 5 , 180 円ですね」   と伝えられた瞬間、私は少し硬直し、「ごめんなさい。お金が足りないので、後でまた来ます」   と口にしてしまいます。その後、店員に手渡した 3 , 800 円は返却され、代わりに受け取った領収書と   「独習 C# 」   は再び店員の手に戻され書棚に置かれるという、つまりロールバック処理が実行されたことは言うまでもありません。

このように身近によく行われているトランザクション処理ですが、この記事では、 SQL Server を使用した Web アプリケーション開発を行う場合、どのように実装していくのかをメインテーマに考えています。特に Visual Basic や VBScript 言語などで、データアクセスのためのコードを記述したが、トランザクションの実装方法は本当の所、よく分からなかったという開発者の方々に、実際に試していただけるコードの紹介なども含めて情報提供したいと考えていますのでご期待ください。

1.1 トランザクション管理の重要性

業務システムに使用されるデータベースでリレーショナル型が主流となったのは、現実社会で行われている商取引の多くが帳票ベースの処理であったためです。現実の帳票は表形式に表現され、リレーショナルデータベース内のテーブルとして格納されます。しかし実際には、テーブルの処理効率を上げつつ、参照や更新時の整合性を維持するために正規化・非正規化という手続きが施されます。その結果、テーブルだけを眺めていても帳票イメージがわきにくいですが ...

では、実際のビジネスにおいて、それらの帳票類を使用した契約や商取引などの業務行為は、システム上でどのように扱われるのでしょう。ご存知の通りリレーショナルデータベースのデータ処理は SQL 言語を使用して行われます。その SQL のデータ操作言語 (DML) には、次の 4 種類のステートメントがあります。

  • SELECT

  • UPDATE

  • DELETE

  • INSERT

これらは、テーブル上のデータの参照 (SELECT) し、更新 (UPDATE) 、削除 (DELETE) 、追加 (INSERT) するためのステートメントです。これらのデータ操作言語で書かれた一連のデータ操作をトランザクションとして管理することで、一貫した整合性のある処理が保証されます。具体的には、一連のデータ操作を SQL トランザクションとして開始し、終了させるように記述するだけ で、複数のデータ操作をひとつの処理単位として扱うことが保証されるということです。

ここで強調したいのは、契約や商取引などの業務処理は、一連のデータ操作のセットとして扱われなければならないということです。データベース関連の書籍やセミナーなどで、よく使われるトランザクション処理の例としては、銀行口座間のお金の移動や座席予約、入出庫処理などがあるのですが、データ操作のトランザクション管理が不十分ですと、「出金されたのに入金されていない」とか、「私の予約した席に別の誰かが .. 」とか、「欠品しているのに受注した」などといった問題に直面します。

また近頃、 B to C 、 B to E 、といった Web サイト上を窓口 ( フロント ) とする商取引システムや、 EAI 、 B to B といった企業内・企業間連携のシステムなど、 Web コンピューティングをベースとしたシステム構築が盛んに行われています。これらのシステムの中心にはリレーショナルデータベースが置かれ、トランザクション処理が繰り返し実行されていることを考えても、正しくて効率のよいトランザクション処理を記述することが重要なのは明らかでしょう。

1.2 SQL Server によるトランザクション管理

トランザクション管理には、ロックが前提となるため、トランザクションを理解するためには、ロック機能を理解する必要がります。 SQL Server では、ロックマネージャによってロックが管理されていますが、基本的には、次の 2 種類のロックがあります。

  • 共有ロック

  • 排他ロック

1. 共有ロック

SELECT ステートメントなどで、テーブルへの読み取り操作中にかけられます。他の接続から共有ロックがかけられている間は、同じリソースに対してデータを変更できません。しかし、このロックは、既定では、トランザクションの中においてもデータの読み取り操作が終わるとすぐに解除されます。

2. 排他ロック

UPDATE 、 INSERT 、 DELETE ステートメントといったデータ変更の処理中にかけられます。他の接続から排他ロックがかけられている間は、同じリソースに対してデータを変更することも、参照することもできません。このロックは、既定では、トランザクションの最後まで維持除されます。

前のセクションでトランザクションは、「開始し終了させるように記述するだけで」   機能するように書きましたが、実は少し注意が必要です。 SQL Server の場合、トランザクションの開始と終了のさせ方には以下の 3 つのモードがあるからです。

  • 自動コミット トランザクション モード (SQL Server 既定)

  • 明示的なトランザクション モード

  • 暗黙のトランザクション モード

    これらのモードの違いは正しく理解しておきましょう。

1. 自動コミット   トランザクション   モード

ステートメント実行時に一文ずつ確定 ( コミット ) されます。そのため、完了した操作の取り消し ( ロールバック ) は行えません。

例えば ... 

INSERT ････ <- 確定
UPDATE ････ <- 確定 
DELETE ････ <- 確定

というように DML ステートメントごとに確定します。そして、 SQL Server はこのトランザクションモードがデフォルトの動作なのです。

2. 明示的なトランザクション   モード

COMMIT TRANSACTION ステートメントで、確定するタイミングを明示的に指定できるモードです。 

BEGIN TRANSACTION     <- トランザクション処理の開始
・・・・              <- 一連のデータ操作処理
COMMIT TRANSACTION    <- データ操作処理をまとめて確定 (コミット)

BEGIN から COMMIT までに行った一連の操作をひとつの単位として一貫性を保証します。操作の取り消し ( ロールバック ) をしたい場合は、 COMMIT TRANSACTION ステートメントの替わりに ROLLBACK TRANSACTION ステートメントを実行します。

3. 暗黙のトランザクション   モード

SET IMPLICIT_TRANSACTIONS ON を実行すると、このモードに切り替わります。このモードは、 ANSI-92 標準や Oracle の既定のモードと互換性があります。 SET ステートメントを実行すると新しいトランザクションは自動的に開始され、そのトランザクションが完了   （ COMMIT ステートメント）   または、取り消し (ROLLBACK ステートメント ) すると、その次の SQL ステートメントから、新しいトランザクションが自動的に開始されます。

図

図 1-1 トランザクションモード

ここまででまとめると、

  • SQL Server の既定のトランザクション モードは、自動コミットモード

  • BEGIN TRANSACTION ステートメントを記述することで、明示的なトランザクションモードになる

  • トランザクション実行中は論理的な整合性を維持するため共有や排他といったロックが機能している

ということを説明してきました。

1.3 T-SQL によるトランザクションの実装

  • トランザクションの基本構文

このセクションは、 Transact-SQL によるトランザクションの実装方法を解説します。

まず、トランザクションの基本構文ですが、 

BEGIN TRANSACTION [トランザクション名]

で、開始し 

COMMIT TRANSACTION [トランザクション名]

で、終了します。

いずれも TRANSACTION は、短縮形で、 TRAN と記述することができます。また、複雑になるため推奨されませんが、明示的なトランザクションモードの SQL Server はトランザクションのネストをサポートしているため BEGIN TRAN と COMMIT TRAN の組み合わせを明確化するためにトランザクション名を記述することができます。

また、トランザクションの取り消しは、 

ROLLBACK TRANSACTION [トランザクション名|セーブポイント名]

を記述します。

これで、すべてのデータ操作をトランザクションの開始以前か、または指定されたセーブポイントまで戻すことができます。

いきなり、セーブポイントという表現が出てきましたが、これは、トランザクションを   

SAVE TRANSACTION [セーブポイント名]

と記述することで、トランザクション内にセーブポイントというマーカーを設定することができます。セーブポイントでは、トランザクションの一部だけを取り消したい場合に、トランザクションが戻ることのできる位置を定義するものです。

セーブポイントを試そう！

では少し実際に試して見ましょう。

1. 次のスクリプトを実行し C1 列がキー列で、 C2 列は NULL 値を許可しない tbl1 テーブルを作成します。 

CREATE TABLE tbl1 (
    C1 int IDENTITY (1, 1) NOT NULL ,
    C2 char(1) NOT NULL 
) 
GO
ALTER TABLE tbl1 WITH NOCHECK ADD 
    CONSTRAINT PK_tbl1 PRIMARY KEY  CLUSTERED 
    (
     C1
    )
GO

2. 次に tbl1 テーブルに 4 件のデータを挿入します。 

DELETE FROM tbl1
GO
SET IDENTITY_INSERT tbl1 ON
GO
DECLARE @i INT
SET @i = 1
WHILE @i < 5
BEGIN   
  INSERT INTO tbl1(C1,C2) VALUES(@i ,'X')   
  SET @i = @i + 1
END
GO

このスクリプトの BEGIN-END ブロックで実行されている INSERT ステートメントは、自動コミットモードのため、 WHILE ループ内でその都度、レコードの挿入が確定されます。

この後、 tbl1 テーブルを参照すると図のようなレコードがセットされていることが確認できます。

図

図 1-2 tbl1 テーブル

3. 次のトランザクションでは、 C2 列の 'X' の代わりに 'A' ～ 'D' の文字を挿入しようとしていますが、実際に実行して更新が確定するのは (1)~(4) のいずれの UPDATE ステートメントでしょうか。よく分からない方は、実際に試してみましょう。   

BEGIN TRAN TR1
 UPDATE tbl1 SET C2= 'A' WHERE C1 = 1  ---(1)
 SAVE TRAN SV1
 UPDATE tbl1 SET C2= 'B' WHERE C1 = 2  ---(2)
 SAVE TRAN SV2
 UPDATE tbl1 SET C2= 'C' WHERE C1 = 3  ---(3)
 ROLLBACK TRAN SV1
 UPDATE tbl1 SET C2= 'D' WHERE C1 = 4  ---(4)
COMMIT TRAN TR1

  • トランザクションの取り消し方法

更に、トランザクションの取り消しについて考えてみましょう。トランザクションの実行中にわざとエラーが発生する次のようなスクリプトを実行してみます。 

BEGIN TRAN
　UPDATE tbl1 SET C2= 'a' WHERE C1 = 1 
　UPDATE tbl1 SET C2= NULL WHERE C1 = 2 -- NOT NULL違反
　UPDATE tbl1 SET C2= NULL WHERE C1 = 3 -- NOT NULL違反
　UPDATE tbl1 SET C2= 'd' WHERE C1 = 4
COMMIT TRAN
GO

結果は、図のようになります。

図

図 1-3 tbl1 テーブル

実行しているトランザクションの中で NOT NULL 違反の実行時エラーが発生したのに、 1 行目と 4 行目は、更新が確定していますが、これはどうしたらよいでしょう。そうですね。エラー時に、全体の処理を取り消したい場合、 @@ERROR を使用して、トランザクション内で実行しているデータ操作の実行結果を取得し COMMIT TRAN の替わりに ROLLBACK TRAN を実行使用しなければなりません。

@@ERROR は、 T-SQL ステートメントの実行が成功すると 0 を返し、エラーが発生した場合はエラー番号を返すシステム関数で、その値は、 T-SQL ステートメントが完了するたびに変化します。  

以降が、 tbl1 テーブルに対し更新されていない行を更新するように記述したトランザクションにエラー処理を追加した例になります。 

BEGIN TRAN TR1
 UPDATE tbl1 SET C2= 'ｂ' WHERE C1 = 2 
  IF @@ERROR <> 0
   BEGIN
    ROLLBACK TRAN TR1
    GOTO ON_ERROR
   END
 UPDATE tbl1 SET C2= 'c' WHERE C1 = 3 
  IF @@ERROR <> 0
   BEGIN
    ROLLBACK TRAN TR1
    GOTO ON_ERROR
   END
COMMIT TRAN TR1
ON_ERROR:

エラーが発生すると BEGIN - END ブロックの中で、 ROLLBACK TRAN を実行し ON_ERROR: ラベルに飛ばしています。これで、エラー以降のデータ操作を行わないですみます。 @@ERROR とあわせて、 @@TRANCOUNT や @@ROWCOUNT といったシステム関数もトランザクション処理のエラーハンドリングに参照されますので、あわせて覚えておきましょう。

また、 @@ERROR によるチェックを行わず、全体をロールバックさせる別の方法として、 SET 　 XACT_ABORT を設定する方法もあります。以下のスクリプトを行ってもテーブルの更新は、すべて失敗します。 

SET XACT_ABORT ON
GO
BEGIN TRAN
 UPDATE tbl1 SET C2= 'A' WHERE C1 = 1 
 UPDATE tbl1 SET C2= NULL WHERE C1 = 2 -- NOT NULL違反
 UPDATE tbl1 SET C2= NULL WHERE C1 = 3 -- NOT NULL違反
 UPDATE tbl1 SET C2= 'D' WHERE C1 = 4
COMMIT TRAN
GO

制約違反の種類などを @@ERROR でチェックする必要がない場合、 SET XACT_ABORT を設定するだけでよいためシンプルですね。

1.4 ADO によるトランザクションの実装
さて、ここまでは SQL Server のトランザクション管理の基本事項を確認してきましたが、この連載のタイトルは   ｢ SQL Server による Web アプリケーション開発｣   ですから、このセクションは、 ADO からのトランザクションの記述方法も確認しておきます。

  • トランザクションの基本構文

VB からのトランザクションの開始、確定、取り消しの構文ですが、 Connection オブジェクトの BeginTrans 、 CommitTrans 、 RollbackTrans メソッドを使用します。また、 ADO エラーは実行時エラーとしてアプリケーションに通知されるので、各プログラミング言語のエラートラップ機能を使用して処理することができます。 Visual Basic では、 On Error ステートメントが使われます。 1.3 で作成した T-SQL による @@ERROR でエラー処理を追加した Tbl1 テーブルに対するトランザクションは、 VB で記述すると以下になります。このコードは、 VB でデータプロジェクトを開き、ボタンに埋め込めば実行できます。   

Private Sub Command1_Click()
'エラーのトラップ
On Error GoTo Error_Handler
'データベースへの接続
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Provider = "SQLOLEDB"
cn.ConnectionString = "Driver=SQL Server;" & _
              "Server=localhost;" & _
                  "Database=PASSJ;" & _
               "UID=sa;" & _
               "PWD=password"
cn.Open
'トランザクションの開始
cn.BeginTrans
    bInTran = True
    cn.Execute "UPDATE tbl1 SET C2= 'b' WHERE C1 = 2", , adExecuteNoRecords
    cn.Execute "UPDATE tbl1 SET C2= 'c' WHERE C1 = 3", , adExecuteNoRecords
'トランザクションの確定
cn.CommitTrans
bInTran = False
cn.Close
Set cn = Nothing
Exit Sub
Error_Handler:
    If bInTran Then
　　　　'トランザクションの取り消し
        cn.RollbackTrans
    End If
    MsgBox "Err:" & Err.Description, vbOKOnly
    Dim i As Integer
    For i = 0 To cn.Errors.Count - 1
        MsgBox "cn.Erros:" & cn.Errors(i).Description, vbOKOnly
    Next i
End Sub

コードにデータベース接続の記述を追加され、エラー処理も VB 流に変えていますが、基本的な流れは、 T-SQL スクリプトの場合と同じです。更にこのコードを ASP 環境から実行できるよう変更してみましょう。 

<!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.6 Library" 
             UUID="{00000206-0000-0010-8000-00AA006D2EA4}" VERSION="2.6"-->
<%
On Error Resume Next
Dim strMsg
Dim iRet
strMsg = "" 
iRet = Proc1()
If iRet <> 0 Then
    Response.Write "<HTML><HEAD><TITLE>エラー</TITLE></HEAD><BODY>"
    Response.Write "<H1>トランザクション処理中にエラーが発生しました。</H1>"
    Response.Write strMsg
    Response.Write "</BODY></HTML>"
Else
        Response.Write " <HTML><HEAD><TITLE>成功</TITLE></HEAD><BODY>"
    Response.Write "<H1>トランザクション処理は成功しました。</H1>"
    Response.Write "</BODY></HTML>"
End If
Session.Abandon
'Proc1関数
Function Proc1()
    On Error Resume Next                
    Set cn = Server.CreateObject("ADODB.Connection")
    cn.ConnectionString ="Driver=SQL Server;" & _
            "Server=localhost;" & _
            "Database=PASSJ;" & _
            "UID=sa;" & _
            "PWD=password"
    cn.Open
    'トランザクションの開始
    cn.BeginTrans
    cn.Execute("UPDATE tbl1 SET C2= 'X' WHERE C1 = 2"), , adExecuteNoRecords
    If Err.Number &lt;&gt; 0 Then
        strMsg = strMsg + Err.description + "&lt;BR&gt;"       
        'トランザクションの取り消し
        cn.RollbackTrans
        cn.Close 
        Set cn = Nothing
        Proc1 = 1
        Exit Function
    End If
    cn.Execute("UPDATE tbl1 SET C2= 'X' WHERE C1 = 3"), , adExecuteNoRecords
    If Err.Number &lt;&gt; 0 Then
        strMsg = strMsg + Err.description + "&lt;BR&gt;"       
        'トランザクションの取り消し
        cn.RollbackTrans
        cn.Close 
        Set cn = Nothing
        Proc1 = 1
        Exit Function
    End If
    'トランザクションの確定
    cn.CommitTrans
    cn.Close 
    Set cn = Nothing
End Function
%>

Visual Basic のコードと比べるといくつかの違いがあります。まず、 ASP では、 VB で行われる参照設定の代わりとして METADATA タグを使用したタイプ   ライブラリ宣言を記述しています。タイプ   ライブラリとは、 COM コンポーネントでサポートされるオブジェクトと型に関する情報のあるファイルで、 ADO のタイプ   ライブラリを参照することで adExecuteNoRecords とった定数を認識できるようになります。また、エラー処理は、 On Error Goto の代わりに On Error Resume Next を使用します。このためトランザクション処理を関数化してエラーが起きた場合 Exit Function で抜けるように変更しています。更に、 ASP では、 New 演算子が使用できないため、 CreateObject を使用してオブジェクトを生成していることなど細かい違いなどにも注意しておきましょう。

ここまでで、 T-SQL 、 VB 、 ASP 環境での VBScript とそれぞれのトランザクション処理コードの書き方を明らかにしてきました。次のセクションでは、またコードからは離れ、トランザクションの特性について、もう一段掘り下げてまとめておきます。

1.5 トランザクションにより保証されるものと犠牲になるもの

論理的な 1 つの作業単位がトランザクションとして有効であるには、 ACID と呼ばれる 4 つのプロパティを備えている必要があります。以下に ACID プロパティとはどのようなものか、次の表を参照してください。。

Atomicity

原子性

分離できない 1 つの作業単位 (All or Nothing)

Consistency

一貫性

トランザクション完了時のデータの一貫性

Isolation

分離性

別のトランザクションからの独立性

Durability

持続性

システム障害が発生しても完了したトランザクションは保存されなければならない

表 1-1 ACID プロパティ

この表を眺めてみると、ここまでで語られてきたトランザクションの特性は、 ACID プロパティの中の Atomicity ( 原子性 ) と Consistency ( 一貫性 ) についてであったということが分かります。また、 Durability( 持続性 ) は、リレーショナルデータベース管理システムがサーバーの再起動やディスク障害などの問題に直面した際に、トランザクションの一貫性を回復させる機能のことです。データベースを復旧させるために、トランザクションログファイルを利用し、ロールフォワードとロールバックという操作を実行して回復させます。この持続性に関するお話は、システム管理系の話題となるため今回は詳しく扱いません。しかし、トランザクションを書くことでこれらのプロパティが保証されるということは覚えておいてください。

さて、ここまであまり説明してこなかった、 3 つ目のプロパティ、 Isolation ( 分離性 ) とは、どういったことでしょう。これは複数のユーザーにより開始されるトランザクション間の相互の関係性を示すプロパティです。ここで 1 つ思い出して欲しいことがあります。それは、トランザクションはロックにより同時実行性を制御するものだということです。ロックを甘くして同時実行性を優先すれば、トランザクションの分離性は下がり一貫性が脅かされますが、パフォーマンスは向上します。ロックを厳して同時実行性を犠牲にするとトランザクションの分離性が上がり整合性は維持されますが、パフォーマンスは低下します。 SQL プログラマは、トランザクションを書くときに、このトレードオフの関係にある分離性のレベルを調整することができるのです。

次回は、この分離性のプロパティ調整について、展開していきます。お楽しみに !
それでは皆さん次回まで Have a nice Transaction!!

図

沖要知 : 1998 年   野村総合研究所からグループ会社の NRI ラーニングネットワークに出向後、 Web サイト開発者、 DB 技術者向けの研修講師を担当。また、トレーニング実施の合間を縫って、技術書籍の技術監修をしたりマニアックなトレーニングカリキュラムを開発したりと多忙な日々ですが、趣味は辛いカレーを作ることとドライブで、時間があれば愛車のインプレッサを乗り回しています。ただし、少々方向音痴ぎみ。 SQL Server 関連では、インサイド SQL Server 7.0 ( 日経 BP) 、 SQL Server 2000 リソースキット ( 日経 BP) を監修。
11/22 の PASSJ セッションでは、「 SQL Server 2000 における Web アプリケーション開発」を担当。

Page view tracker