第 2 章 ロックアーキテクチャの理解～ SQL Server 2000 における Web アプリケーション開発 ～

  • [アーティクル]

NRI ラーニング ネットワーク株式会社

技術研修部

沖 要知

2001年12月3日

11 月 22 日、第 1 回目の PASSJ Conference 2001 が開催されました。セッションを聴講いただいた皆様、Ask the speaker コーナーにおいでいただいた皆様、ありがとうございました。さて、前回は、ロック機構の働きで、データ操作の一貫性や整合性が維持されることを説明し、そのロック機構を機能させるためのトランザクション処理の書き方も紹介してきました。

しかし、不用意に書かれたトランザクションは、ブロッキングやデッドロックといった問題を引き起こすことがあります。章の最後に、今回、トランザクション分離レベルをあつかうと書きましたが、その前に、SQL Server のロック機構について、もう少し深く掘り下げて確認しておきましょう。

2.1 ブロッキングの問題

ブロッキング問題の多くは、単一のプロセスが長時間ロックを保持し続けていることが原因です。たとえば、会社の同僚数人と、とんかつ屋に訪れテーブルについたと考えてください。そして、全員がとんかつ定食を注文したとします。しばらくして、注文した料理が運ばれてきますが、まず、あなたが、そのテーブルにひとつしかない、とんかつソースの容器を手にしたとします。そして、延々と時間をかけ、ゆっくりと、とんかつやキャベツにソースをかけ続けていたとしたらどうなるでしょう。あなたが、ソースをかけ終わらない限り、そのテーブルに同席した同僚たちが要求する、とんかつソース リソースへのアクセスはブロックされ続け、その結果、連鎖的に、そのテーブルの誰もがとんかつを食べ始めることがでない状態になります。これがとんかつソース リソースへの排他ロックによるブロッキングで、その影響が多大なことも想像できるでしょう。

しかし、実際にそのような行為におよべばあなたは店主から、とんかつにソースをつけすぎないよう注意を受けることになります。以降のセクションでは、とんかつ屋の店主のようにロックを監視する方法を紹介します。

2.2 ロックの監視

ロック要求に関する情報は master システムデータベースの syslockinfo テーブルに格納されます。これを実行中のプロセスに関する情報を含む sysprocesses テーブルとあわせて確認すると、現在、ロックを要求しているプロセスやブロックされているプロセスを確認することができます。そして、これらのシステムテーブルからロック情報に関するレポートを作成してくれるのが sp_lock ストアドプロシージャです。それでは、sp_lock を使用し、ブロックの状態を少し観察してみましょう。

  1. 実験用テーブルの作成

    まず、実験を行うためにテーブルを作成します。名前は何でも良いのですが、仮に PASSJ という名前でデータベースをひとつ作成し、以下の SQL ステートメントを実行してください。

    --商品区分テーブルの作成

CREATE TABLE 商品区分 (     商品区分ID int not null,     商品区分名 varchar(30) not null ) GO

ALTER TABLE 商品区分 ADD CONSTRAINT pkey_商品区分      PRIMARY KEY(商品区分ID) GO

--商品テーブルの作成 CREATE TABLE 商品 (     商品ID int IDENTITY(1,1) not null,     商品区分ID int not null,     商品名 varchar(40) not null,     価格 int not null,     欠品フラグ int not null, ) GO

ALTER TABLE 商品 ADD CONSTRAINT pkey_商品     PRIMARY KEY(商品ID)   GO

ALTER TABLE 商品 ADD CONSTRAINT FK_商品_商品区分     FOREIGN KEY (商品区分ID) REFERENCES 商品区分(商品区分ID) GO

--在庫テーブルの作成 CREATE TABLE 在庫 (     商品ID int not null,     入庫数量 int null,     在庫数量 int not null ) GO

ALTER TABLE 在庫 ADD CONSTRAINT pkey_在庫     PRIMARY KEY(商品ID)   GO

ALTER TABLE 在庫 ADD CONSTRAINT FK_在庫_商品     FOREIGN KEY (商品ID) REFERENCES 商品(商品ID) GO

--バスケットテーブルの作成 CREATE TABLE バスケット (     セッションID int not null,     商品ID int not null,     数量 int not null ) GO

ALTER TABLE バスケット ADD CONSTRAINT pkey_バスケット      PRIMARY KEY(セッションID,商品ID) GO

ALTER TABLE バスケット ADD CONSTRAINT FK_バスケット_商品     FOREIGN KEY (商品ID) REFERENCES 商品(商品ID) GO

--顧客テーブルの作成 CREATE TABLE 顧客 (     顧客ID int not null,     顧客名 varchar(30) not null,     住所 varchar(255) not null,     Tel varchar(20) not null,     EMail varchar(30) not null,     パスワード varchar(10) not null ) GO

ALTER TABLE 顧客 ADD CONSTRAINT pkey_顧客     PRIMARY KEY(顧客ID) GO

--受注テーブルの作成 CREATE TABLE 受注 (     受注ID int not null,     顧客ID int not null,     受注日 datetime not null ) GO

ALTER TABLE 受注 ADD CONSTRAINT pkey_受注     PRIMARY KEY(受注ID) GO

ALTER TABLE 受注 ADD CONSTRAINT FK_受注_顧客     FOREIGN KEY (顧客ID) REFERENCES 顧客(顧客ID) GO

--受注明細テーブルの作成 CREATE TABLE 受注明細 (     受注ID int not null,     商品ID int not null,     受注数量 int not null )

GO ALTER TABLE 受注明細 ADD CONSTRAINT pkey_受注明細     PRIMARY KEY(受注ID,商品ID) GO

ALTER TABLE 受注明細 ADD CONSTRAINT FK_受注明細_受注     FOREIGN KEY (受注ID) REFERENCES 受注 (受注ID) GO

ALTER TABLE 受注明細 ADD CONSTRAINT FK_受注明細_商品     FOREIGN KEY (受注ID) REFERENCES 商品(商品ID) GO 

これで図 2-1 に示されるテーブル構造が作られます。実を言うと、これらのテーブルは、PASSJ Conference 2001 で、私のセッションで行った Web サイトで商品を購入するデモで使用したものと同等です。

これらのテーブルを使用し ASP (Active Server Pages) 環境で実行されるスクリプトを利用しストアフロントを作成し、そこから ADO でテーブルにアクセスさせれば、例えば、商品テーブルから商品情報を取り出して商品カタログとして表示させたり、Web サイトの顧客が好きな商品を選択し、バスケットに移し、顧客情報を書き込んで、商品を注文するといった簡単な受注処理も実行することもできます。

![図](images/Cc707373.webtech2-1(ja-jp,MSDN.10).gif)

**図** **2-1**

  1. 実験用データの挿入

    次に実験に必要なデータを挿入するため以下の INSERT ステートメントを実行します。

    /*商品区分データの挿入*/

INSERT INTO 商品区分 VALUES(1,'MCSE教科書') INSERT INTO 商品区分 VALUES(2,'プログラミング学習シリーズ') INSERT INTO 商品区分 VALUES(3,'独習シリーズ') INSERT INTO 商品区分 VALUES(4,'雑誌:VB Magazine') INSERT INTO 商品区分 VALUES(5,'雑誌:SQL Magazine')

/商品データの挿入/ SET IDENTITY_INSERT 商品 ON GO

--MCSE教科書 INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(1,1,'Windows 2000 Network Design',4200,0) INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(2,1,'Windows 2000 Server',3800,0) INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(3,1,'Windows 2000 Directory Service',3800,0) INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(4,1,'Windows 2000 Network',3800,0) INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(5,1,'Administering SQL Server7.0',3800,0) INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(6,1,'Database Design on SQL Server7.0',3800,0)

--プログラミング学習シリーズ INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(7,2,'Visual C++ はじめてのMFCプログラミング',2800,0) --独習シリーズ

INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(8,3,'独習C++',3600,0) INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(9,3,'独習VB6',3600,0) INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(10,3,'独習C#',3800,0)

--雑誌:VB Magazine DECLARE @i INT DECLARE @no INT DECLARE @title VARCHAR(40) SET @i = 10 SET @no = @i - 10

WHILE @no < 1000 BEGIN      SET @i = @i + 1   SET @no = @no + 1   SET @title = 'Visual Basic Magazine'   SET @title = @title + CAST(@no AS VARCHAR) + '号'   INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(@i,4,@title,1500,0) END GO

--雑誌:SQL Magazine DECLARE @i INT DECLARE @no INT DECLARE @title VARCHAR(40) SET @i = 1010 SET @no = @i - 1010

WHILE @no < 1500 BEGIN      SET @i = @i + 1   SET @no = @no + 1   SET @title = 'SQL Server Magazine '   SET @title = @title + CAST(@no AS VARCHAR) + '号'   INSERT INTO 商品(商品ID,商品区分ID,商品名,価格,欠品フラグ) VALUES(@i,5,@title,1500,0) END GO

SET IDENTITY_INSERT 商品 OFF GO

/在庫データの挿入/ DECLARE @i INT DECLARE @no INT SET @i = 1 SET @no = (SELECT COUNT(*) FROM 商品)

WHILE @i < @no BEGIN      INSERT INTO 在庫(商品ID,在庫数量) VALUES(@i,20)   SET @i = @i + 1 END GO 

これらスクリプトを実行することで商品テーブルには 2,510 件分のデータが挿入されます。また、在庫テーブルには、すべての商品の在庫数が、20 個で登録されます。

  1. Global.asa の作成

    更に、これらのテーブルにアクセスする簡単な Web アプリケーションを作成しましょう。まず、任意の IIS 仮想ディレクトリを作成し、以下を Global.asa として保存してください。sa パスワードは、便宜上、password とさせていただきましたが、ご自分の PC 環境に合わせて適正な内容で設定してください。

    <SCRIPT LANGUAGE=VBScript RUNAT=Server>

Sub Application_OnStart     strConnection = "Driver=SQL Server;" & _             "Server=localhost;" & _             "Database=PASSJ;" & _             "UID=sa;" & _             "PWD=password"     Application("strConnection") = strConnection End Sub

Sub Session_OnStart     Session.Timeout = 5     Session("CustomerID") = null End Sub </SCRIPT>

  1. 商品区分の表示

    そして、次の ASP スクリプトコードは、商品区分テーブルから商品区分を表示するものです。同じように Global.asa を保存した仮想ディレクトリに Category.asp というファイル名で保存すれば実行することができます。

    <%

Option Explicit Dim Cn, Rs, strSQL Response.Buffer=True %> <HTML><HEAD><TITLE>商品区分の表示</TITLE></HEAD><BODY> <H1>オンライン書籍購入サイト</H1> 購入したい商品区分を選択して下さい。<BR><BR> <% Set Cn = CreateObject("ADODB.Connection") Cn.ConnectionString = Application("strConnection") Cn.Open strSQL = "SELECT 商品区分ID, 商品区分名 FROM 商品区分 ORDER BY 商品区分ID" Set Rs = Cn.Execute(strSQL) Do While Rs.EOF = False %>     <A HREF="Product.asp?商品区分ID=<%=Rs("商品区分ID")%>&商品区分名=<%=Rs("商品区分名")%>">     <%=Rs("商品区分名")%></A><BR> <%     Rs.MoveNext Loop Rs.Close: Set Rs = Nothing Cn.Close: Set Cn = Nothing %> </BODY></HTML> <% Response.Flush %>

  1. 商品の表示

    そして、Category.asp で指定された商品データを表示する Product.asp も用意します。

    %>

<HTML><HEAD><TITLE>商品の表示</TITLE></HEAD><BODY> <% Set Cn = CreateObject("ADODB.Connection") Cn.ConnectionString = Application("strConnection") Cn.Open %> <H1>商品リスト(<%=Request("商品区分名")%>)</H1> 購入したい商品を選択して下さい。<BR> <% '商品を表示 strSQL = "SELECT 商品.商品ID" _      & ",商品.商品名"_      & ",商品.価格" _      & ",在庫.在庫数量" _      & " FROM 商品,在庫" _      & " WHERE 商品.商品ID = 在庫.商品ID AND " _      & " 商品.商品区分ID = " & Request("商品区分ID") _      & " AND 商品.欠品フラグ = 0" _      & " ORDER BY 商品.商品ID" Set Rs = Cn.Execute(strSQL) Response.Write "<A HREF= Basket.asp >買い物カゴを表示</A>" Response.Write "<TABLE CELLSPACING=""1"">" & vbCR & vbLF Response.Write "<TR BGCOLOR=AQUA>" Response.Write "<TH WIDTH=250>商品名</TH>" Response.Write "<TH WIDTH=60>単価</TH>" Response.Write "<TH WIDTH=60>残数</TH>" Response.Write "<TH WIDTH=80>操作</TH></TR>" Do While Rs.EOF = False     Response.Write "<TR><TD>" & Rs("商品名") & "</TD>" & vbCR & vbLF     Response.Write "<TD ALIGN=""RIGHT"">"     Response.Write FormatCurrency(Rs("価格")) & "</TD>" & vbCR & vbLF     Response.Write "<TD ALIGN=""CENTER"">"     Response.Write Rs("在庫数量") & "</TD>" & vbCR & vbLF     Response.Write "<TD ALIGN=""CENTER"">"     Response.Write "<A HREF=""_addItem.asp?商品ID="     Response.Write Rs("商品ID") & """>購入</A></TD></TR>" & vbCR & vbLF     Rs.MoveNext Loop

Rs.Close: Set Rs = Nothing Cn.Close: Set Cn = Nothing Response.Write "</TABLE>" Response.Write "<BR><A HREF= Category.asp >商品区分表へ戻る</A><BR>" %> </BODY></HTML> <% Response.Flush %>

図 2-2 は、4 番目の商品区分の VB Magazine を選択して表示された商品リストです。スクロールさせると VB Magazine が 1 号から 1000 号まで登録されていることが分かりますね。

![図](images/Cc707373.webtech2-2(ja-jp,MSDN.10).gif)

**図** **2-2**

  1. ブロッキングの発生

    それでは、商品カタログを参照する ASP スクリプトを実行しながら、クエリアナライザからは、ブロックが発生するスクリプトを実行してみましょう。次のスクリプトは、商品 ID が 10 の商品の在庫数量を変更していますが、トランザクションを終了する COMMIT TRAN は記述されていません。このスクリプトを実行しながら、商品カタログを参照しようとするとブロッキングが発生します。

    BEGIN TRAN

UPDATE 在庫 SET 在庫数量 = 在庫数量 + 50 WHERE 商品ID = 10 

ブラウザから独習シリーズの商品リストを表示させるよう商品区分のリンクをクリックして、しばらく置いておくと次のエラーが表示されます。

<pre IsFakePre="true" xmlns="http://www.w3.org/1999/xhtml">Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)

[Microsoft][ODBC SQL Server Driver]時間切れになりました

  1. ブロッキングの監視

    在庫を更新するクエリは、そのままにして、再度、ブラウザから独習シリーズを表示させるよう商品区分のリンクをクリックします。そして、クエリアナライザから新しい接続を作成し、sp_lock を使用してブロッキングを引き起こしているプロセスを確認してみましょう。図 2-3 のような表示が返ってくるはずです。

    図

    図 2-3

    まず、Status 列を確認します。Status 列は、ロックが取得された場合 GRANT、ロックが別のプロセスにブロックされている場合、WAIT が表示されます。つまり、Status 列に WAITがある場合、ブロッキングが発生しています。

    次に、WAIT がある行の ObjId 列、IndId 列、Type 列、Resource 列、Mode 列を確認していきます。図 2-3 では、プロセス ID (spid) が 55 の接続からクラスタ化インデックス (IndId=1) が構成された在庫 (ObjId=2057058364) テーブルのインデックスキーを参照(Mode=S)しようとした時、待たされていることがわかります。Mode は、S が共有ロックで、X が排他ロックです。また、ObjId の調べ方は、SELECT OBJECT_NAME(2057058364) と実行すればよいでしょう。

    では、誰がブロックしているのでしょう。これは、待たされている接続と同じリソースに、互換性のないロックモードでアクセスしている接続がないかを確認します。分かりましたか。そうですね。プロセス ID (spid) が 51 の接続が、同じキーに対して排他ロック (X) を取得しています。排他ロックがかけられたリソースは、「私が更新しているので、誰も読まないで」という状態です。

    Tips!!

    ここでは、sp_lock でロック状態が確認できることを紹介していますが、このコマンドはブロッキング情報以外のすべてのロック情報が表示さるため、ユーザーの接続数が増えると分析するのが大変ですよね。そこでブロッキングに関係する情報だけを表示するコマンドをご紹介します。
    sp_blockinglocks です。えっ!! 実行しても、そんなストアドプロシージャは見つからないですって? 実は、この sp_blockinglocks は、Inside SQL Server 2000 (邦題: 「アーキテクチャ徹底解説 Microsoft SQL Server 2000」日経 BP ソフトプレス刊)の付属 CD-ROM 内 Scripts\Chapter 16 フォルダに含まれています。関心のある方は、是非、試してみましょう。

2.3 ブロッキングを回避するには

既定のトランザクション分離レベルでは、排他ロックによるブロッキングをなくすことはできませんが、できるだけ少なくすることはできます。今回の実験で確認したように開始していつまでも完了しないトランザクションがあるとブロッキングの影響は大きくなります。以下にブロッキングを避けるためのアプリケーション設計上のガイドラインをまとめます。

  • クエリでは必要なリソースだけにアクセスする。

  • トランザクションはできるだけ短くする。

  • SET LOCK_TIMEOUT を設定し、タイムアウトした場合の処理を用意する。

  • トランザクション内で、ユーザーが入力できるようなアプリケーションを設計しない。

  • カタログ検索などは、フリーテキストインデックスを検討する。

また、事前に実働環境と同じユーザー数で同時接続する、アプリケーションのストレス テストを行ってブロッキングの状態をモニタすることをお勧めします。この場合、SQL Server プロファイラ、パフォーマンスモニタを利用すれば、時系列的な変化の傾向を知るのに役立ちます。

2.4 トランザクション分離レベルを変更してみる

さて、前のセクションの冒頭で、「既定のトランザクション分離レベルでは、排他ロックによるブロッキングをなくすことはできません」と書きましたが、トランザクション分離レベルを変更すると排他ロックによるブロッキングをなくせるのでようか?

ANSI 規格では、以下の 4 つのトランザクション分離レベルが規定されており、SQL Server 2000 は、そのすべてのレベルを実装できます。

  • READ UNCOMMITTED

  • READ COMMITTED

  • REPEATABLE READ

  • SERIALIZABLE

SQL Server の既定のトランザクション分離レベルは、READ COMMITTED ですが、これを READ UNCOMMITTED に変更すると、排他ロックがきかなくなり、未コミットデータを読める(これをダーティリードと呼ぶ)ようになってしまいます。ADO では、Connection オブジェクト IsolationLevelプロパティを以下に設定することでトランザクション分離レベルを変更できます。

  • adXactReadUncommitted

  • adXactReadCommitted

  • adXactRepeatableRead

  • adXactSerializable

もし、この Web アプリケーションが表示する在庫数量が重要でない数値で、とにかくWebアプリケーションのスループットをあげることが重要であると考えるのであれば、Product.asp のトランザクション分離レベルを READ UNCOMMITTED に変更することもできます。以下がその手順です。

  1. METADATA タグの追加

    Global.asa ファイルの先頭に下記 METADATA タグを字述し ADO がサポートする定数宣言を含むタイプ ライブラリを参照できるようにします。

    <!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.6 Library"

UUID="{00000206-0000-0010-8000-00AA006D2EA4}" VERSION="2.6"-->

  1. トランザクション分離レベルの指定

    Product.asp ファイルを開き接続を開く(Cn.Open) 前に下記を記述します。

    Cn.IsolationLevel = adXactReadUncommitted

  2. トランザクション開始の指定

    Connection オブジェクトの IsolationLevel プロパティは、BeginTrans メソッドの実行により有効になるので、Set Rs = Cn.Execute(strSQL) の前に、Cn.BeginTrans を書きます。

  3. トランザクション終了の指定

    Do While - Loop ブロックの次の行に Cn.CommitTrans を書きます。

これで、Product.asp は、排他ロックがかからなくなります。Product.asp を保存し実行してみましょう。ブロックされずに図 2-4の様な結果が表示されるはずです。

図

図 2-4

独習 C# の在庫数が 70 とありますが、これは、まだコミットされていない数値であることに注意してください。

2.5 設計を見直す

しかし、本当に、このアプリケーションの Product.asp ページにとって正しい在庫数量を表示する必要がないと考えるのであれば、最初からこのページで在庫数量を表示しないように設計すれば、在庫テーブルへの排他ロックが、影響して、ユーザーが商品情報を表示できなくなると言うことはなくせるはずです。不用意に整合性を損なうような READ UNCOMMITTED を使用せず、アプリケーションの設計、テーブル設計からよく考えるようにしましょう。

次回は、最終回となりますが、READ UNCOMMITTED 以外の分離レベルを説明し、その影響と対応方法について、更に突っ込んでいきます。では皆さん次回まで Have a nice Transaction!!

webtech.jpg

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