SAP SQL Anywhere におけるスレッドデッドロックとはどういうことか? (過去のブログ記事より)

[Sawa_Ito]

このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。

 

 

この記事のオリジナルは、Glenn Paulley が sybase.com に 2009 年 5 月に掲載したものです。Glenn はこの中で、SQL Anywhere におけるスレッドデッドロックについて解説するとともに、アプリケーション設計に起因する問題について解説しています。

 

SQL Anywhere の最新のバージョン（16 以降）では、マルチプログラミングレベルを動的に調整するため、ほとんどの場合 -gn サーバーオプションを設定する必要はありません。しかしながら、これによって Glenn が以下で語っている好ましくない設計を隠すことができたとしても、根本的な解決にはならないことに注意してください。

 

 

===

 

 

スレッドデッドロックとは、 SQL Anywhere サーバーがある特定のリクエストに対して返す特定のエラー (SQLCODE -307, SQLSTATE '40W06') のことです。このブログ記事では、スレッドデッドロックがなぜ、どのように発生するのか、さらには問題の診断に利用できるメカニズムについてまとめたいと思います。

 

 

SQL Anywhere のスレッディングアーキテクチャー

 

他のデータベース管理システムと同様、SQL Anywhere では基本となるオペレーティングシステムのスレッディングモデルだけに依存するのではなく、独自のスレッディングアーキテクチャーを実装しています。SQL Anywhere は、幅広い OS とハードウェアプラットフォームをサポートしているため -- いくつかを挙げると、Windows、Linux、Windows CE、Sun OS、AIX、HP-UX、Mac OS/X など -- SQL Anywhere では、オペレーティングシステム上の（例：Windows、Linux）それらをサポートする「lightweight」スレッド (fibersとも呼ばれる) と、これらの OS プラットフォーム上の通常の OS スレッドを利用します。

 

また、SQL Anywhere ではサーバーは特定の一つのスレッド（fiber）を一つの接続に占有させるのではなく、サイズが固定されたサーバースレッドのプールが、サーバーに入ると複数のタスクに動的にアサインされて実行されます。タスクは、アプリケーションまたはストアドプロシージャーからのSQL 文であることが多いものの、一つのスレッドがサービスできるタスクには、様々なタイプがあります。一旦スレッド（fiber）上でタスクがスケジュールされると、 そのスレッドは、そのタスクが完了するまで、またはキャンセルされるまでそのタスクを処理するようアサインされます。

 

SQL Anywhere は、デフォルトではサーバーがスタートするとスレッドを 20 作成します (Windows CE では 3 です)。このデフォルト設定は、-gn コマンドラインスイッチを使用することで変更することができます。実際はスレッド数がサーバーのマルチプログラミングレベル -- 1 回にアクティブにできるタスクの最大数 -- を決定することになります。サーバースレッドは、そのサーバー上のどのデータベースへの接続数からも独立しています。そのため、ある任意のスレッド (fiber) は、まず データベースのためのタスクをサービスしてから、別のデータベースへの接続のタスクをサービスすることができます。

 

 

スレッドデッドロック - 条件

 

SQL Anywhere サーバーのスレッドは、PREPARE、DESCRIBE、OPEN、FETCH のようなもともとはデータベースリクエストであるタスクをサービスします。これらのタスクはとても高速でサービスされることが多いものの、大きな結果セットを INSENSITIVE カーソルでオープンするような場合などでは、かなり遅くなることがあります。どこかのポイントで、そのタスクをサービスしているスレッドが、クエリーアクセスプラン演算子を実行している、結果式をアウトプットバッファーにマーシャリングしている、I/O演算子が完了するのを待機している、あるいはシェアードサービス上でブロックされている可能性があります。例えば、スキーマロックや行ロックなどです。

 

マルチプログラミングレベルが n だとすると、スレッドデッドロックとは、 n-1 スレッド (fibers) がアクティブタスクをサーブしているもののブロックされており、アクティブタスクをサーブしている nth スレッド (fiber) もまさにブロックされそうな状態のことです。サーバーは、全てのスレッド (fibers) がブロックされるのを防がなければなりません。なぜならば、これが結果的にはエンジンが「ハング」してしまうことになるからです -- つまり、スレッドは全てブロックされているため実行不可能で、新たな接続もハンドリング不可能、そして全ての新しいタスクが待機状態になります。

 

この状況は、「本当の」 デッドロックとは、以下の意味において異なります。「本当の」デッドロックとは、二つまたはそれ以上のスレッドにおいて、継続できるスレッド（fibers）がないというような依存のサイクルができてしまうことです。

それに対して、スレッドデッドロックでは、完全に関係ない SQL リクエストの場合にブロックされる可能性があります。それぞれがサーバースレッド (fiber) を 結びつけ、スレッドが SQL リクエストをブロックしようと試みると、エラー -307 を受け取ります。サーバーのスレッド (fibers) のセットは、別々のデータベースに接続している接続に対しても、全ての SQL リクエストをサービスするため、それぞれのデータベースの結びつけられたワークロードが原因でスレッドデッドロックが発生する可能性があるということに注意してください。

 

何十、何百もの接続をサービスするビジーなサーバーの場合、データベースのサイズまたはブロッキングが原因で多くのリクエストが long-running の場合にはスレッドデッドロックになる可能性があります。この場合に適切な解決策は、-gn コマンドラインスイッチを使用して、より高い値でサーバーを再起動することにより、サーバーのマルチプログラミングレベルを増加させることです。

 

しかしながら、アプリケーションシステムでは、アプリケーションの設計に起因する過度あるいは意図しない接続で、スレッドデッドロックになることが頻繁に発生します。このような場合には、アプリケーションをより大きなデータセットまたは接続数にスケールすることで問題をさらに悪化させてしまいます。また、マルチプログラミングレベルを高い値に上げることで、問題を解決できることはめったにありません。

 

 

どのようにしてスレッドデッドロックは発生するのか

 

スレッドデッドロックのインスタンスを（簡単に）入手する方法を説明するために、それぞれのクライアントが定期的に「センサーデータ」の行を挿入するシンプルなマルチクライアントの例を使ってみます。「センサーデータ」は、以下のテーブルに格納します。

 

 

1. CREATE TABLE sensor_data (


2.   sensor_id BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,


3.   sensor_number INTEGER,


4.   sensor_data  VARCHAR(50) NULL


5. )

さらに、クライアントがセンサーデータの行を挿入するごとに、クライアントはそのセンサーの挿入レコード記録の総数を含むサマリーレコードを更新します。サマリーデータテーブルは、以下のとおりです。

 

1. CREATE TABLE summary_data (


2.   sensor_number INTEGER PRIMARY KEY,


3.   sensor_count  BIGINT


4. )

 

それぞれのクライアント接続のロジックは、以下のストアドプロシージャーにあらわされています。

 

1. CREATE OR REPLACE PROCEDURE INSERT_SENSOR_DATA()


2.   BEGIN


3.       DECLARE sensor_ident INTEGER;


4.       SET sensor_ident = MOD( 1000.0 * RAND(), 10 );


5.       INSERT INTO sensor_data VALUES( DEFAULT, sensor_ident, 'This is a test.' );


6.       IF EXISTS ( SELECT * FROM summary_data WHERE summary_data.sensor_number = sensor_ident ) THEN


7.         UPDATE summary_data SET sensor_count =


8.                 sensor_count + 1 WHERE summary_data.sensor_number = sensor_ident


9.       ELSE INSERT INTO summary_data VALUES ( sensor_ident, 1 )


10.       END IF;


11.   END

 

上記のプロシージャー内のロジックは簡単です。最初のステップ で、センサーデータの行を sensor_data テーブルに挿入します。次のステップで、サマリーテーブルを修正します。複雑なのは、問題のセンサーのサマリー行があるかどうかを決定することです。もしあれば、その行のカウントは、インクリメントされ 、なければ、新しい行が挿入されます。

 

注意: 上記のコードは簡単ですが、誤りでもあります。このプロシージャーのロジックは、文字通りrace条件を含み、使用されている分離レベルによって、頻繁にデッドロックと/または不正確な結果を引き起こします。ただし、これに関する詳細は、ここで説明しようと思っているスレッドデッドロックのケースでは、重要ではありません。

 

この例をセットアップするには、リクエストのレスポンス時間をトラッキングするために samples/trantest ディレクトリの trantabs.sql スクリプトを実行し、TRANTEST ユーティリティーが利用するテーブルもセットアップする必要があります。セットアップには、以下も必要になります。

 

1. TRUNCATE TABLE summary_data;


2. TRUNCATE TABLE sensor_data;


3. SET OPTION "DBA".LOG_DEADLOCKS = "ON";


4. CALL SA_SERVER_OPTION( 'RememberLastStatement', 'Yes' );

SQL Anywhere サンプルの TRANTEST パフォーマンス分析ユーティリティを使用して、上記のプロシージャーを同時に複数の接続から実行します。TRANTEST コマンドラインは、以下のとおりです。

 

1. TRANTEST -a ESQL -c "uid=dba;pwd=sql" -f insert_sensor_data.sql -i 2 -k 5 -l 15 -m 0 -n 25 -o results.txt -w 0

 

まとめると、TRANTEST は ESQL 接続を 25 作成し、"insert_sensor_data.sql" 内のスクリプトを、think time ゼロ、分離レベル2、総経過時間15秒で継続的にコールし、5 トラザクションごとにCOMMITを発行します。"insert_sensor_data.sql" ファイルには単一の line が含まれます。

 

1. CALL INSERT_SENSOR_DATA()

 

11.0.1 サーバーを実行しているので、デフォルトマルチプログラミングレベル20で、25クライアントを選択します。

 

 

スレッドデッドロックの問題判別

 

スレッドデッドロックが発生し、接続のセットとSQL リクエストが含まれているかどうか判断するには、２つの方法があります。１つはビルトインの診断プロシージャー sa_report_deadlocks() を使う方法で、これは上記 LOG_DEADLOCKS オプションを有効にすることで可能です。TRANTEST で上のサンプルを実行後の結果の一部が以下です。

 


Line 54-67 は、スレッドデッドロックを示す DBISQL ウィンドウです。ここでは、最初の行 (line 54) が、「victim」 (CALL 文は 、最後のブロックされていないスレッドを実行していました) です。続く行は、他の接続の状況を示します。もちろん、これらそれぞれは、INSERT_SENSOR_DATA()  プロシージャーを実行している間ブロックされています。sa_report_deadlocks() で返される行は、テーブル (object 3358、summary_data テーブル、 SYSOBJECTS カタログテーブルから) と、ブロックを発生させている summary_data 内の行の行識別子のどちらも詳細しています。

 

原因は簡単です。なぜならば、プロシージャーは、summary_data テーブルの行の読み込み (プロシージャーの line 16) と変更 (line 17) のどちらも試み、マルチプルクライアントが互いにブロックするからです。可能なスレッドよりも多くのクライアント、バッチド COMMIT、zero think time で、スレッドデッドロックは必然です。-gn をクライアントの数よりも高い値に増やすことで、スレッドデッドロックの発生を防ぐことができますが、根本的な問題、INSERT_SENSOR_DATA() プロシージャーの実行のシリアル化、の解決にはなりません。

 

スレッドデッドロックの存在を発見する２つめの方法は、SQL Anywhere の Sybase Central (現 SQL Central) から Application Profiling 機能を使用することです。Application Profiling を開始し、TRANTEST を実行することで、どの接続によっても発行される各ステートメントの実行を記述するトレーシングデータベースを産出します。このテストのサマリーページを Sybase Central で表示すると以下のようになります。

 

2009/09/thread_deadlock_summary.png



 

UPDATE summary_data ステート文のサマリー時間に注意してください。9300-odd ステートメントの呼び出し総時間は 2910 ミリ秒です。 しかし、最大時間の 213 ミリ秒は、過度のブロッキングのサインです。詳細ペイン移動すると、スレッドデッドロックの発生は明白になります。

 



 

この詳細なビューから、それぞれのスレッドデッドロックが発生した時点で同時実行ステートメントを分析することができ、サーバーの各スレッドの何がその時何を実行していたのか判断することができます。そしてそれが、繰り返しますが、うまく書かれていない INSERT_SENSOR_DATA() ストアドプロシージャー、そして特に、summary_data テーブル上のUPDATE ステートメントを指摘することになります。

 

 

 

===

 

SAP SQL Anywhere に関する詳細情報は、SAP SQL Anywhere Communityページ<英語> を参照してください。

 

上記のコミュニティーに掲載されている技術情報は、順次SQL Anywhere　日本語コミュニティ

に掲載しています。

 

SQL Anywhere に関してはまずはこちらをご参照ください。無期限でご利用いただける無償の Developers Edition もこちらからダウンロードが可能です。

 

SQL Anywhere に関して技術的な質問のある方はコミュニティに登録し、
「Ask a Question」機能をご利用ください。

Language には「Japanese」、
Primary Tag には「SAP SQL Anywhere」を選択
User Tagに「sql anywhere」「sql anywhere Japanese question」

を入力してください。

不具合につきましては、サポート契約者様専用の問い合わせ方法にてお問い合わせください。

 

======================
ご購入に関するお問い合わせ

こちらよりお問い合わせください。