第 3 章 トランザクション分離レベルの選択とデッドロックの問題～ SQL Server 2000 における Web アプリケーション開発 ～

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

2001年12月21日

早いもので、連載も今回が最終回です。前回は、不用意にトランザクションを書くと、処理が長時間に渡る場合もあり、パフォーマンスに悪影響を与えたり、タイムアウトエラーが発生するといったブロッキングの問題を指摘しました。これは、トランザクション処理がデータ更新の整合性を維持するため、ロック機構を使用しているからですが...。それでは、トランザクション処理を対話処理の中から取り除くなどしてスリム化し、短時間で実行させるように書けば、それでよいでしょうか?

トランザクション処理を同時実行させた場合、トランザクションの分離レベルによって、いくつかの問題が発生します。

リソースに対するロックが不十分なトランザクション処理を同時実行させた場合、実行のタイミングなどにより、以下の問題が発生する可能性があります。

• 更新データの損失

• ダーティ リード

• 反復不可能読み取り

• ファントム値

「更新データの損失」とは、どのような状況でしょう。前回、作成したテーブルを使用して書籍の受注システムと入出庫管理システムの同時実行で考えてみます。

書籍購入用の E コマース サイトに、「独習 C#」の在庫が 20 冊あったとします。そして例えば、C# プログラミングの勉強会を開こうとしているとします。あなたは、「独習 C#」を Web サイトから 10 冊購入します。同時に、そのサイトの入出庫管理システム側では、「独習 C#」が 10 冊、追加で入庫される場合を考えます。

このトランザクション処理中に、更新データに対する排他ロックが機能していない場合、先に受注システム側で更新されたデータは、後から実行される入出庫管理システム側の更新により失われることになります。

図 3-1. 更新データの損失

「ダーティ リード」とは、別のトランザクションにより更新されたが、まだコミットされていないデータを読んでしまえることです。

「独習 C#」を Web サイトから 10 冊購入している最中、プロジェクトのメンバーから、先に VB.NET の勉強をしようと提案され、このトランザクションを確定 (COMMIT TRAN) する前に購入しようとした 10 冊をキャンセル (ROLLBACK TRAN) してしまったとします。この時、同時に入出庫管理システム側で、受注システム側の未確定の更新データを参照していたとしたら、これは「ダーティーリード」です。また、「ダーティーリード」した値に基づきデータを更新すると「更新データの損失」が発生する可能性もあります。

図 3-2. ダーティ   リード

しかし、ご安心ください。デフォルトの SQL Server のトランザクション分離レベルは、Read Commited ですから、「ダーティ リード」や「更新データの損失」といった問題はロックマネージャにより回避されます。つまり、未コミットなデータの読み取りを行おうとしても、排他ロックが機能し、受注システム側で、確定 (COMMIT TRAN) か、キャンセル (ROLLBACK TRAN) が実行されるまで待たされるわけです。また、UPDATE ステートメントの SET 句は、SET 在庫数量 = 在庫数量 + 10 と書けば最新の在庫数量を参照し更新するので安全です。

気をつけなければならないのが、以降の「反復不可能読み取り」や「ファントム値の読み取り」です。これらは、デフォルトの SQL Server のトランザクション分離レベルで発生するため注意が必要です。

まず、「反復不可能読み取り」ですが、トランザクション中に、繰り返し同じデータを参照する時、その都度、値が変わってしまう状態です。

図 3-3 では、Web サイトの受注システム側で、「独習 C#」を 10 冊購入している最中に、入出庫管理システム側のトランザクション内で、まだ、排他ロックがかけられる前の「独習 C#」の在庫数量を参照しているが、2 度目に、入出庫管理システム側で「独習 C#」の在庫数量を参照した時、別の値になってしまっている様子を示しています。もし、最初に参照した在庫数量に基づき、在庫の入庫処理などが行われるとデータの一貫性が損なわれるでしょう。つまり、「反復不可能読み取り」は、トランザクション内で参照後に更新を行う場合、問題が生じる可能性があるのです。

図 3-3. 反復不可能読み取り

「ファントム値」は文字通り、トランザクション中に、突然、現れたり消えてしまう、お化け (Phantoms) のようなデータのことで、トランザクションによって読み取られる一連のデータに対し、別のトランザクションから挿入や削除が行われたときに発生します。

図 3-4 では、Web サイトの受注システムでユーザー A とユーザー B の 2 人が、ほぼ同時に受注処理を行い、互いに受注テーブルの最新の受注 ID (200) を参照し、それに1を加算した数値 (201) を新しい受注 ID として挿入しています。

図 3-4. ファントム値

どうして SQL Server のデフォルトのトランザクション分離レベル (Read Commited) では、「反復不可能読み取り」や「ファントム値」といった問題が発生するのでしょうか?

ロックマネージャの動作には、ロックの取得を取得するための成長フェーズと、ロックを解除する収縮フェーズがありますが、Read Commited では、排他ロック (X) はトランザクション終了まで保持されますが、共有ロック(S)はデータスキャンを終えると収縮フェーズに入りロックが解除されてしまうからです。これらの問題を完全に無くそうとするならば、共有ロック (S) もトランザクションが完了するまで保持される Serializable トランザクション分離レベルに設定する必要があります。表 3-1 は、トランザクション分離レベルとトランザクション処理を同時実行させた場合に起こる問題の相関関係を示しています。

表 3-1

それでは、図 3-4 の処理を Serializable トランザクション分離レベルに設定し実行すれば、問題がなくなるのでしょうか? 確かにそれで、お化け (Phantoms) は、現れなくなるでしょう。しかし、今度は、デッドロックの問題が発生します。では、前回作成したテーブルを使用して実験してみましょう。準備として、下記ステートメントを実行しユーザーを 2 人、顧客テーブルに追加しておきます。

INSERT INTO 顧客 VALUES(1,'ユーザーA','東京都千代田区','03-3282-1234','','')
INSERT INTO 顧客 VALUES(2,'ユーザーB','東京都千代田区','03-3282-5678','','')

• クエリ   アナライザから 2 つの接続を作成
  クエリ アナライザを起動し、接続を 2 つ作り一方がユーザー A で、もう一方をユーザー B とします。

• Serializable トランザクション分離レベルに設定
  両接続で、下記 SET ステートメントを実行し、トランザクション分離レベルを Serializableにする。

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  GO

• ユーザー A からトランザクションを開始
  まず、ユーザー A から下記ステートメントを実行し、トランザクション内で、最新の受注 ID を取得し、受注テーブルにデータを挿入します。

  BEGIN TRAN
  DECLARE @受注ID INT
  SELECT @受注ID = MAX(受注ID) FROM dbo.受注
  WAITFOR DELAY '00:00:10'
  IF (@受注ID IS NULL) SELECT @受注ID = 0
  INSERT INTO 受注 VALUES(@受注ID + 1,1,GETDate())

• ユーザー B からトランザクションを開始
  次に、かんばつを入れずにユーザー B からも同じステートメントを開始します。

  BEGIN TRAN
  DECLARE @受注ID INT
  SELECT @受注ID = MAX(受注ID) FROM dbo.受注
  WAITFOR DELAY '00:00:10'
  IF (@受注ID IS NULL) SELECT @受注ID = 0
  INSERT INTO 受注 VALUES(@受注ID + 1,2,GETDate())

しばらくすると、ユーザー A のトランザクションは正常に終了し、後から実行したユーザー B で下記メッセージが表示されるはずです。

サーバー : メッセージ 1205、レベル 13、状態 8、行 5

この実験の後、ユーザー A で COMMIT TRANかROLLBACK TRAN を実行しトランザクションを終わらせておいてください。

さて、1205 エラーですが、これは SQL Server がデッドロックを検出し、一方のプロセスを強制終了したことを示しています。デッドロックは、2 つ以上のプロセスが、お互いに別のプロセスがロックしているリソースにアクセスしようとして、介入なしに先へ進めない状況が検出された場合に発生します。アプリケーションは、1205 エラーをトラップした時、キャンセルされた処理を再実行するエラーハンドラを実装している必要があります。

ここからは、デッドロックの起こり方について解説していきます。SQL Server のデッドロックには、変換デッドロックとサイクルデッドロックの 2 種類があります。

図 3-5 では、変換デッドロックの状態を示しています。ユーザー A とユーザー B の両方が、トランザクション内で最新の受注 ID を取得するために、受注テーブルの同じ行を参照し共有ロックをかけています。(内部的には、受注 ID 列にインデックスが付けられているため、キー範囲に対する共有ロックが保持されます。) トランザクション分離レベルが、Serializable の場合、共有ロックもトランザクションが終了するまで継続されます。その後、ユーザー A とユーザー B の両方が、新規受注データを書き込むため INSERT ステートメントを実行しても、双方で共有ロックをかけ合っているため、共有ロックを排他ロックに変換できなくなります。その結果、コストがかかっていないプロセスで 1205 エラーが発生します。

図 3-5. 変換デッドロック

次にサイクルデッドロックが発生する手順をクエリアナライザから実行してみます。

• クエリ   アナライザから 2 つの接続を作成
  クエリ アナライザを起動し、接続を 2 つ作り一方がユーザー A で、もう一方をユーザー B とします。

• Serializable トランザクション分離レベルに設定
  両接続で、下記SETステートメントを実行し、トランザクション分離レベルを Serializable にする。

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  GO

• 受注システム側で受注処理を行うトランザクションを開始
  一方の接続から、下記ステートメントを実行します。この処理は、受注側で商品が発注され、在庫テーブルの在庫数量が 0 になったため、商品テーブルの欠品フラグを ON にセットするという操作を想定しています。

  BEGIN TRAN
  UPDATE 在庫 SET 在庫数量 = 在庫数量 - 20 WHERE 商品ID = 10
  WAITFOR DELAY '00:00:10'
  IF (SELECT 在庫数量 FROM 在庫 WHERE 商品ID = 10) < 1
  BEGIN
     UPDATE 商品 SET 欠品フラグ = '1' WHERE 商品ID = 10
  END

• 入出庫管理システム側で、入庫処理を行うトランザクションを開始
  次に別の接続から、下記ステートメントを実行し倉庫側での入庫処理を行います。これは、入庫された商品の欠品フラグをOFFに戻して、在庫テーブルの在庫数量を加算するという操作を想定しています。

  BEGIN TRAN
  UPDATE 商品 SET 欠品フラグ = '0' WHERE 商品ID = 10
  WAITFOR DELAY '00:00:10'
  UPDATE 在庫 SET 在庫数量 = 在庫数量 + 20 WHERE 商品ID = 10 

  3 と 4 の SQL バッチを続けて実行すると、受注側のトランザクションは正常に終了しますが、後から実行した入庫側で 1205 エラーが表示されるはずです。図 3-6 は、サイクルデッドロックが発生する様子を示しています。たがいに必要とするリソースに排他ロックを掛け合ってしまい、「すくみ」の状態になるため、第 3 者の介入なしには進めません。

  

  図 3-6. サイクルデッドロック

SQL Server でデッドロックの監視を行うには、SQL Server プロファイラで、ロックイベントクラスをモニタする方法もありますが、今回は、トレースフラグ 1204 を使用する方法を紹介します。トレース フラグ 1204 を設定すると、デッドロック時に待機中のスレッド間の従属性やスレッドが保持しているリソースなどがレポートされます。では、前のセクションで試した、デッドロックが発生するトランザクションを利用し、トレース フラグ 1204 のレポートを生成してみましょう。

1. トレース   フラグ 1204 をセットし SQL Server を起動
   まず、SQL Server サービスマネージャを使用し SQL Server を停止します。次にコマンドプロンプトから下記コマンドで、SQL Server の実行モジュールが保存されたパスに移動します。

   CD "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"

   下記コマンドを実行し、SQL Server をコマンドプロンプトから起動します。

   sqlservr -d"C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf" -T1204

2. 変換デッドロックを起こす
   3.2 で紹介したスクリプトを実行し、変換デッドロックを起こします。

3. 変換デッドロックの分析
   1205 エラーが発生すると、コマンドプロンプトには、下記のレポートが表示されます。

   Deadlock encountered .... Printing deadlock information
   2001-12-18 22:29:30.21 spid4
   2001-12-18 22:29:30.21 spid4  Wait-for graph
   2001-12-18 22:29:30.22 spid4
   2001-12-18 22:29:30.22 spid4 Node:1
   2001-12-18 22:29:30.22 spid4 KEY: 20:773577794:1 (ffffffffffff) CleanCnt:2 Mode: Range-S-S Flags: 0x0
   2001-12-18 22:29:30.23 spid4 Grant List::
   2001-12-18 22:29:30.23 spid4 Owner:0x192f13e0 Mode: Range-S-S Flg:0x0 Ref:1 Life:02000000 SPID:52 ECID:0
   2001-12-18 22:29:30.24 spid4 SPID: 52 ECID: 0 Statement Type: INSERT Line #: 7
   2001-12-18 22:29:30.24 spid4 Input Buf: Language Event: 
   BEGIN TRAN
   DECLARE @受注ID INT
   SELECT @受注ID = MAX(受注ID) FROM dbo.受注
   WAITFOR DELAY '00:00:10'
   IF (@受注ID IS NULL) SELECT @受注ID = 0
   INSERT INTO 受注 VALUES(@受注ID + 1,1,GETDate())
   2001-12-18 22:29:30.26 spid4 Requested By:
   2001-12-18 22:29:30.26 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null 
   SPID:51 ECID:0 Ec:(0x195a9508) Value:0x192f12e0 Cost:(0/54)
   2001-12-18 22:29:30.27 spid4
   
   2001-12-18 22:29:30.28 spid4 Node:2
   2001-12-18 22:29:30.28 spid4 KEY: 20:773577794:1 (ffffffffffff) CleanCnt:2 Mode: Range-S-S Flags: 0x0
   2001-12-18 22:29:30.28 spid4 Grant List::
   2001-12-18 22:29:30.29 spid4 Owner:0x192f1280 Mode: Range-S-S Flg:0x0 Ref:1 Life:02000000 SPID:51 ECID:0
   2001-12-18 22:29:30.29 spid4 SPID: 51 ECID: 0 Statement Type: INSERT Line #: 6
   2001-12-18 22:29:30.30 spid4 Input Buf: Language Event: 
   BEGIN TRAN
   DECLARE @受注ID INT
   SELECT @受注ID = MAX(受注ID) FROM dbo.受注
   WAITFOR DELAY '00:00:10'
   IF (@受注ID IS NULL) SELECT @受注ID = 0
   INSERT INTO 受注 VALUES(@受注ID + 1,1,GETDate())
   2001-12-18 22:29:30.31 spid4 Requested By:
   2001-12-18 22:29:30.32 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null 
   SPID:52 ECID:0 Ec:(0x19595528) Value:0x192f1340 Cost:(0/54)
   
   2001-12-18 22:29:30.33 spid4 Victim Resource Owner:
   2001-12-18 22:29:30.33 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null 
   SPID:52 ECID:0 Ec:(0x19595528) Value:0x192f1340 Cost:(0/54)

   このレポートでは、まず、SPID：51 と SPID：52 で同じインデックスキー (KEY: 20:773577794:1) に対して共有モードのキー範囲ロック (Range-S-S) をかけていることが分かります。また、Requested By を確認すると各プロセスが、お互いのリソースを要求しあい変換デッドロックが発生していることが分かります。Victim Resource Owner は、デッドロックチェーンを解消するために SQL Server がキャンセルの対象として選択したプロセスを示していて、この例では SPID：52 がキャンセルされています。デッドロックの種類を理解するには、Input Buf に表示されている実行中のバッチ内のステートメントを確認して判断します。Cost は、そのプロセスの実行にかかったコスト値です。

4. サイクルデッドロックを起こす
   3.3 で紹介したスクリプトを実行し、サイクルデッドロックを起こします。

5. サイクルデッドロックの分析
   1205 エラーが発生すると、コマンドプロンプトには、下記のレポートが表示されます。

   Deadlock encountered .... Printing deadlock information
   2001-12-18 22:55:55.16 spid4
   2001-12-18 22:55:55.16 spid4 Wait-for graph
   2001-12-18 22:55:55.17 spid4
   2001-12-18 22:55:55.17 spid4 Node:1
   2001-12-18 22:55:55.17 spid4 KEY: 20:645577338:1 (0a0087c006b1) CleanCnt:1 Mode: X Flags: 0x0
   2001-12-18 22:55:55.18 spid4 Grant List::
   2001-12-18 22:55:55.18 spid4 Owner:0x192f12c0 Mode: X   Flg:0x0 Ref:2 Life:02000000 SPID:52 ECID:0
   2001-12-18 22:55:55.19 spid4 SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1
   2001-12-18 22:55:55.19 spid4 Input Buf: Language Event: 
   BEGIN TRAN
   UPDATE 在庫 SET 在庫数量 = 在庫数量 - 20 WHERE 商品ID = 10
   WAITFOR DELAY '00:00:10'
   IF (SELECT 在庫数量 FROM 在庫 WHERE 商品ID = 10) < 1
   BEGIN
      UPDATE 商品 SET 欠品フラグ = '1' WHERE 商品ID = 10
   END
   2001-12-18 22:55:55.22 spid4 Requested By:
   2001-12-18 22:55:55.22 spid4 ResType:LockOwner Stype:'OR' Mode: X 
   SPID:51 ECID:0 Ec:(0x196ef528) Value:0x192f1260 Cost:(0/54)
   2001-12-18 22:55:55.24 spid4
   
   2001-12-18 22:55:55.24 spid4 Node:2
   2001-12-18 22:55:55.24 spid4 KEY: 20:597577167:1 (0a0087c006b1) CleanCnt:1 Mode: X Flags: 0x0
   2001-12-18 22:55:55.25 spid4 Grant List::
   2001-12-18 22:55:55.26 spid4 Owner:0x192f1400 Mode: X  Flg:0x0 Ref:1 Life:02000000 SPID:51 ECID:0
   2001-12-18 22:55:55.27 spid4 SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 1
   2001-12-18 22:55:55.28 spid4 Input Buf: Language Event: 
   BEGIN TRAN
   UPDATE 商品 SET 欠品フラグ = '0' WHERE 商品ID = 10
   WAITFOR DELAY '00:00:10'
   UPDATE 在庫 SET 在庫数量 = 在庫数量 + 20 WHERE 商品ID = 10
   2001-12-18 22:55:55.31 spid4 Requested By:
   2001-12-18 22:55:55.32 spid4 ResType:LockOwner Stype:'OR' Mode: X 
   SPID:52 ECID:0 Ec:(0x19597528) Value:0x192f13c0 Cost:(0/A0)
   
   2001-12-18 22:55:55.33 spid4 Victim Resource Owner:
   2001-12-18 22:55:55.33 spid4 ResType:LockOwner Stype:'OR' Mode: X 
   SPID:51 ECID:0 Ec:(0x196ef528) Value:0x192f1260 Cost:(0/54)

このレポートでは、SPID：51 がインデックスキー (KEY: 20:645577338:1) に対して排他ロック (X) をかけているのに対して SPID：52 はインデックスキー (KEY: 20:597577167:1) に対して排他ロック (X) をかけていることが分かります。更に、Input Buf に表示されているステートメントを確認すると、在庫テーブルと商品テーブルに対しするサイクルデッドロックであることが分かります。その結果、コストが少ない SPID：51 が犠牲になったこともレポートされています。

トランザクション分離レベルが、Serializable の時、発生する可能性のあるデッドロックをどのように回避したらよいのでしょうか。デッドロックは、トランザクション分離レベルを下げることで発生しなくなりますが、下げることができない場合、どうしたらよいのでしょうか?このセクションでは、変換デッドロックとサイクルデッドロックの回避策について説明します。

変換デッドロックは、複数の接続から Serializable で直列化されたトランザクションが実行され、共有ロックがかけられた時から始まります。Serializable であっても、共有ロックに対して別の接続から共有ロックをかけることができるので、誰も参照した値を基に更新することができなくなります。下記の表は、ロックの互換性を示しています。

表 3-2

縦軸の共有ロックと横軸の共有ロックの交点に ○ が入っているのは、共有ロックに対して別の接続から共有ロックをかけることができることを示しています。

変換デッドロックの回避には、更新ロックを利用することができます。更新ロックは、クラスタ化インデックスを持つテーブルに対して、限定更新や限定削除、または挿入を行うと対象となるキーを捜す間、自動的にかけられるロックです。実際にデータを変更するキーが見つかると、そこから排他ロックに変換されます。ポイントは、更新ロックに更新ロックを掛けることはできず、リソースに対して更新ロックを取得できるのは、ただ一つのプロセスのみとなることです。ロックヒントを使えば、この更新ロックをトランザクション内の SELECT ステートメントでも使用することができます。3-2 の受注処理にロックヒントを指定した例を下記します。

SELECT @受注ID = MAX(受注ID) FROM dbo.受注 WITH (UPDLOCK)

この修正を行い、再度、変換デッドロックが起こるトランザクションのバッチを実行してみると良いでしょう。

サイクルデッドロックの回避は、リソースのアクセス順序を制御するようにします。3-3. の例では、受注システム側も入庫処理も、いずれもが、「在庫」→「商品」というように、同じ順序でテーブルアクセスしていれば、サイクルデッドロックは発生しません。

複数のシステムからひとつのデータベースにデータアクセスする場合など、あらかじめデータ更新用のコンポーネントを用意しその中で、ストアドプロシジャを呼ぶようにしておけば、リソースのアクセス順序を共通化させやすくなるでしょう。

しかし、強調しておきたいことは、現行のコンポーネントサービスの基でトランザクションを実行した場合、Serializable しか設定できないということです。コンポーネントベースのトランザクションでは、特にデッドロック対策を意識した実装を心がけるようにしましょう。

紙面 (ページ面?) の都合から、ロックの粒度とインテントロックの機能やロックエスカレーションといった話題には触れませんでしたが、主な SQL Server のトランザクション管理の仕組みとブロッキングやデッドロックといった問題への対策についての指針については、ご理解いただけたのではないかと考えます。今後、分散アプリケーション開発のフレームワークは、.NET へと進化しますが、SQL Server のトランザクション管理とロック機構は、基本的に変わらないでしょう。今回の連載で提供した情報が、今後の皆様のWebアプリケーション開発の参考になれば幸いです。また、より詳細を知りたい方は、弊社で開催しているトレーニング (http://learningnet.nri.co.jp/) においでいただくことを期待しています。それでは、皆様、再びお会いする日まで、Have a good transactions!!

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