はじめに

数多くのユーザーやバッチ・プロセスが同時にデータ更新を試みるマルチユーザー・データベースには、データの保護と整合性保持のための何らかのメカニズムが不可欠です。トランザクション・ログに加えてロック機能は、マルチユーザー環境におけるデータ整合性保護の鍵となるものです。

ロックはしばしば競合や待機状態を引き起こしますが、これらはいずれもデータベース管理者が日常的に経験する問題です。適切なスクリプトなしでロックの問題を分析するのは複雑な作業であり、誤りも生じやすくなります。

この記事では、IDS のロック・メカニズムについて説明し、ロック競合やロック待機状態の分析に役立つ情報を提供します。
例は stores_demo データベース (dbaccessdemo stores_demo -log コマンドで作成可能) に基づいています。

上に戻る

動的ロック割り当て

メカニズム

onconfig の LOCKS パラメーター (最大値 8,000,000) は、ロック (インスタンス全体に使用できるロック) の初期数を指定します。ただし、IDS には動的ロック割り当て機能が備わっています。ロックが初期数に達すると、IDS は内部ロック・テーブルのキャパシティーを自動的に倍増します。この手順は最大 15 回まで繰り返されます。1 回のキャパシティー増で新たに割り当てられるロック数は最大 100,000 なので、IDS で使用できるロック数は最大 9,500,000 となります。

初期数 8,000,000 + 追加 (15 × 100,000) = 合計 9,500,000

構成済みロックが初期数に達した後でも、動的ロック割り当て機能によって、アプリケーションは処理を継続できます。

最適化されていないアプリケーション・コード

ロック動的割り当て機能の存在が「アプリケーション・コードが最適ではない」という事実を覆い隠してしまう場合もあります。IDS の機能として望まれるのは、1 つのセッションにおける使用可能ロックの最大数をコントロールできる能力です。こうした機能があれば、データベース管理者は、記述に不備のあるアプリケーションを、他のデータベース・セッションに影響を与えることなく特定してブロックすることができます。

IDS 10 では現在、ロックが上限の 9,500,000 に達した場合、実行中のアプリケーションがロックを要求すると、そのすべてに lock table overflow エラーが返されます。つまり、記述に不備のあるアプリケーションが 1 つあれば、全アプリケーションに被害が及ぶということです。これは理想的なシナリオとは言えません。

上に戻る

ロック待機時間

データベース・セッションごとの設定

各データベース・セッションでは、オーバーフローがアプリケーション全部に影響するのを回避するために、個別にロック待機時間を設定できます。IDS のデフォルトでは、ロック・モードは「not wait (待機なし)」に設定されています。すなわち、ロックの競合が検出されると、IDS は直ちにエラー・コードをアプリケーションに送ります。

• -244: Could not do a physical-order read to fetch next row
• 107: ISAM error: record is locked

Informix では、ANSI SQL に次のような拡張を加えることによって、ロック待機の動作をセッションごとに指定できるようになっています。

• set lock mode to not wait
• set lock mode to wait
• set lock mode to wait <#sec>

分散トランザクション

分散トランザクションの場合は、リモート・インスタンスに対するロックを IDS が取得するまでの最大待機時間 (アプリケーションにエラー・コードを返すまでの待機時間) を、onconfig の DEADLOCK_TIMEOUT パラメーターを使って指定します。

推奨

ロック待機を無制限に指定 (ロック・モードを wait に設定) するのは、最適な解決策ではありません。なぜなら、ロック競合やデッドロックの可能性が高まるだけでなく、最適化されていないアプリケーション・コードを見落としやすくなるからです。

OLTP 環境では、ロック待機時間を 5 ～ 10 秒に設定すると実用的です。指定時間内に IDS がロックを取得できなかった場合は、適切な SQL エラーがアプリケーションに送られます。それによりアプリケーションは、処理を再試行するか、現在のトランザクションをロールバックするかを判断できます。

セッション設定のチェック

データベース・セッションの現在の分離レベルとロック・モードは、onstat -g sql コマンドで判別できます。

				
Output from the onstat -g sql command:
--------------------------
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain
18    -              stores_demo        CR
				            Not Wait   0    0    9.03  Off
16    -              stores_demo        RR
				            Wait 10    0    0    9.03  Off
	

この例は、分離レベルの異なる 2 つのデータベース・セッションを示しています。

• セッション 18、CR (コミット済み読み取り)、Not Wait (待機なし)
• セッション 16、RR (反復可能読み取り)、Wait 10 (10 秒待機)

IDS の機能としてもう 1 つ望まれるのは、デフォルトのロック待機方法を設定できる能力です。アプリケーションが明示的な set lock mode to wait... ステートメントをデータベース・レベルまたはインスタンス・レベルで実行しない場合、このデフォルトの方法が使用されます。

上に戻る

デッドロック

デッドロックが発生するのは、2 人のユーザーがロックを保有していて、双方が相手の持つロックの取得を要求している場合です。デッドロックを防ぐため、IDS は、新しいロックを許可する前に内部ロック・テーブルをスキャンし、影響が及ぶデータベース・セッションに ISAM エラー・コード -143 (deadlock detected) を出します。

検出されたデッドロックの数

onstat -p コマンドは、IDS の起動後または onstat -z (ゼロ統計) コマンドの実行後に検出されたデッドロックの総数 (deadlks 列) を表示します。IDS 統計の最終リセット時刻を判別するには、以下の SQL ステートメントを使用します。

• select dbinfo("utc_to_datetime", sh_pfclrtime) from sysmaster:sysshmvals

Sysmaster テーブル

残念ながら、検出されたデッドロックや関連するアプリケーションについて IDS が多くの詳細情報を提供することはありません。したがって、デッドロックの解決後にその原因を分析するのは困難です。ただし sysmaster データベースには、追加情報を提供するテーブルがいくつか含まれています。

• sysprofile:検出されたデッドロックの総数が記録されています。これは theonstat -p コマンドの出力に含まれる deadlks 列に該当します。
• syssesprof:検出されたデッドロックの数がデータベース・セッション別に記録されています。
• sysptprof:検出されたデッドロックの数がテーブル別に記録されています。

デッドロックのトレース

IDS のエラー・トラップ機能もデッドロックの分析に有効です。既に述べたように、IDS は、デッドロックの可能性を検出すると、アプリケーションに ISAM エラー・コード -143 を送ります。デッドロックに対するエラー・トラップ機能は、onmode -I 143 コマンドで有効化できます。IDS は、デッドロックを検出すると直ちにアサーション障害ファイルを作成し、onstat -a の出力を格納します。このファイルの内容を分析すれば、デッドロック・セッションが実行していた SQL ステートメント、およびそのデッドロック・セッションと他の並行セッションが保有していたロックを確認することができます。

デッドロックのタイムアウト

onconfig の DEADLOCK_TIMEOUT パラメーターは、ローカル・デッドロックにおけるデッドロック検出時間やアプリケーション待機時間には影響を与えません。IDS では、ローカル・デッドロックは常に即時解決されます。

IDS のリモート・インスタンスのデータを選択/更新している間にロック待機が発生する可能性もあります。これに関しては、要求されたロックを IDS のローカル・インスタンスが取得するまでの最大待機秒数 (デッドロックが発生したと判断するまでの最大秒数) を、DEADLOCK_TIMEOUT で指定します。この秒数が経過すると、ISAM エラー・コード -154 (deadlock timeout expired - possible deadlock) がアプリケーションに送られます。

既に述べたように、IDS は、内部ロック・テーブルによるインスタンス間のクロスチェックは行わず、分散環境でのロック・タイムアウトである DEADLOCK_TIMEOUT パラメーターを使用します。DEADLOCK_TIMEOUT パラメーターは、分散トランザクションにおいて、アプリケーション固有の set lock mode to wait 設定を上書きします。

上に戻る

ロック競合の分析

何百ものデータベース・セッションが並行して実行されている OLTP 環境では、複数のトランザクションが 1 つの行に同時にアクセスを試みることが少なくありません。そのため、トランザクションをできる限り短くすることは、ロック競合を避けるための有効な手段となります。

アプリケーション内で set lock mode to wait ステートメントを実行していない場合、別のセッションが既にロックを設定している行に対してそのロックと互換性のないロックを要求すると、直ちに IDS から SQL エラーが返されます。直接アボートは好ましくないことが多いため、アプリケーションは通常、ロックが許可可能になるまで、あるいは所定のロック待機時間が経過するまでデータベース・セッション (sqlexec スレッド) を保留するように IDS に指示する、set lock mode to wait ステートメントを実行します。しかし、これによってロック待機状態が発生し、IDS インスタンスのスループットが低下する可能性もあります。そうなれば、「応答時間が遅い」や「バッチ・プロセスに時間がかかる」など、インタラクティブ処理に関する不満が出かねません。通常、ロック待機モードを「infinite」に設定すべきではありません (「推奨」を参照)。

継続的にトランザクション処理が行われる動的な環境において、ロック待機状態の分析は非常に難しい作業です。最もよい方法は、ロック待機状態をリアルタイムに分析できるように、何らかのスクリプトを準備することです。次のセクションでは、便利な onstat コマンドをいくつか紹介します。これらをベースとして使用すれば、作業を自動化する高度なスクリプトを記述できます。あるいは、lockwt ユーティリティーを活用してもかまいません。

便利な onstat コマンド

onstat -u コマンドは、ロック競合に対処するためのベースとなるものです。これには注目すべき値が 2 つあります。

• 各セッションによって現在保有されているロックの数
  • locks 列は、このセッションが保有しているロックの数を示しています。この値が大きいセッションはロック競合を引き起こす可能性があります。この値の大きさは、(必ずとは言えないものの) アプリケーションの記述に問題があるということを示唆している可能性があります。
• 現在ロックを待っているセッション
  • ロックを待っているデータベース・セッションには、flags 列の先頭に「L」が付いています。

				
Output from the onstat -u command:
---------------------
address  flags   sessid   user     tty      wait     tout locks nreads   nwrites
4506b44c L-BPR-- 20       informix 11       440cfac4 -1   17    19       0
4506b978 Y--P--D 16       informix -        4407d138  0    0     0        0
...
...
	

onstat -k | grep 'L-' コマンドを実行すると、現在ロックを保有しているすべてのセッションを識別できます。onstat -g ses <sessid> では、このデータベース・セッションが現在どの SQL ステートメントを実行しているかを監視できます。また、開いているデータベース (Current Database)、現在の分離レベル (Iso Lvl)、および所定のロック待機時間 (Lock Mode) も表示されます。status 列には、IDS がアプリケーションにロック・タイムアウト・エラーを返すまでの残り秒数が表示されます。

					
Output from the onstat -g ses 20 command:
-----------------------------
...
...
42       sqlexec  4506b44c L-BPR--  7168     sleeping(secs: 9)
...
...
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain
20    DELETE (all)   stores_demo
					CR
					Wait 60        0    0  9.03  Off
...
...
Current SQL statement:
   delete from customer
	

次に、ロックを保有してロック待機を引き起こしているセッションを識別します。onstat -k コマンドを使用すると、この IDS インスタンスに現在割り当てられているすべてのロックを把握できます。

				
Output from the onstat -k command:
--------------------------
Locks
address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
440cf9bc 0        4506b44c 0        HDR+S    100002   207         0
440cfa14 0        4506b44c 440cf9bc HDR+IX   10015f   0           0
440cfa6c 0        4506b44c 440cfa14 HDR+X    10015f   101         0
440cfac4 4506b44c 0        440cfa6c HDR+     10015f   106         0
440cfb1c 0        4506b44c 440cfa6c HDR+X    100160   101      K- 1
440cfb74 0        4506b44c 440cfb1c HDR+X    100175   101      K- 1
440cfbcc 0        4506b44c 440cfb74 HDR+X    10015f   102         0
...
...
	

この出力で注目すべきなのは、wtlist という 2 番目の列です。ここには、ロックを待っているユーザー・スレッドの 16 進数アドレスが表示されます。ただし、IDS インスタンスで現在アクティブになっているロックの数によっては、onstat -k の出力は非常に大きなものになります。以下のいずれかのコマンドを使用すると、4506b44c という共有メモリー・アドレスを持つデータベース・セッションが現在どのロックを待っているのかを突き止めることができます。

• AWK : onstat -k | awk '$2 ~/4506b44c/ { print }'
• PERL: onstat -k | perl -ane 'print if $F[1] eq "4506b44c"'

owner 列には、そのロックを保有しているユーザー・スレッドの共有メモリー・アドレスが表示されます。このセッションは、onstat -u コマンドの出力を grep ユーティリティーにパイプ接続すれば簡単に識別できます。ロック待機の原因となっているものが現在何を行っているのかは、onstat -g <sessid> コマンドで分析できます。原因となっているもの自体がロックを待っている (onstat -u の flags 列の先頭に「L」が付いている) 場合は、上記のステップを繰り返して、2 つ目のロック待機状態の原因を探らなければなりません。

上に戻る

Lockwt ユーティリティー

Esql/C の lockwt ユーティリティーを使用すると、ロック待機状態を手軽に分析することができます。このユーティリティーを使用するには、Informix Client SDK と、コンパイル用の C コンパイラーをインストールする必要があります。lockwt ユーティリティーは、一対の sysmaster テーブルを検索して、ロック待機状態を探します。

このプログラムは、ロックを保有している各ユーザー・セッションと、そのロックが解放されるのを待っているセッションとをレポートします。指定された時間間隔でロック検索を繰り返すには、lockwt -r <#sec> コマンドを実行します (onstat -r コマンドと同様です)。

lockwt ユーティリティーを使用すると、複雑なロック待機状態をリアルタイムに監視し、収集された情報を読みやすい形式で表示できます。

				
Output from lockwt:
-------------------
(0) (1)  (2)  (3)    (4)          (5)                (6)         (7)       (8)        (9) 
  WAIT SID :PID  PROCNAME    USERNAME           LKTYPE    DATABASE:TABLENAME   LKOBJ
		
 0 -   13900:12303 workprocess3   dbuser              X        rome    :orders        row 
 1 W   53600:23613 batchp12       dbuser                           rome    :orders 
		
		
Colno Purpose
				(0)   Sequence number 
		
(1)   Waiting or not waiting, possible values are: 
      "-" - this session is the holder of the lock and is always listed first.
      "W" - this session(s) is(are) waiting for the above session. 
		
(2)   Session ID of this session in the database server 
		
(3)   Process ID  of the UNIX process, remote connections have pid -1 
		
(4)   Process name of the UNIX process. If it is a remote connection 
      (pid = -1), no process name will be available.
		
(5)   UNIX username of this session 
		
(6)   Type of lock, possible values are: 
      "X" - Exclusive Lock 
      "S" - Shared Lock 
      "U" - Update Lock 
      For additional lock types, execute the following sql-statement: 
      -> select txt from sysmaster:flags_text where tabname = "syslcktab" 
		
(7)   Database name 
		
(8)   Table name, the lock is on. If it is an index lock and the index is detached 
      from the table (has it's own partition number), the name of that index 
      is shown here. 
		
(9)   Type of object locked, possible values are: 
      "table" - this is a table lock 
      "idx" - this is an index key lock 
      "page" - this is a page lock 
      "row" - this is a row lock 
	

				
Output from lockwt:
-------------------
WAIT   SID  :PID   PROCNAME     USERNAME LKTYPE DATABASE:TABLENAME       LKOBJ 
		
0 -    13900:12303 workprocess3 dbuser   X      rome    :orders          row 
1 W    53600:23613 batchp12     dbuser          rome    :orders 
	

この例では、セッション 13900 (プロセス「workprocess3」) が orders テーブル内の特定行に対するロックを保有しています。セッション 53600 はこのロックが解放されるのを待っています。そこで、onstat -g ses 13900 を実行して、セッション 13900 を分析する必要があります。

				
Output from lockwt:
-------------------
WAIT   SID  :PID   PROCNAME     USERNAME LKTYPE DATABASE:TABLENAME       LKOBJ 
		
0 W     3894:   -1 (remote)     eherber1 X      rome    :status          row
1 W    17048: 3140 batchp3      dbuser          rome    :status 
		
0 -    63296:   -1 (remote)     eherber1 X      rome    :customer_order  row
1 W     3894:   -1 (remote)     eherber1        rome    :customer_order 
	

この例はもう少し複雑です。セッション 17048 は、セッション 3894 が status テーブルに対するロックを解除するのを待っています。しかし、2 つ目のロックの対を見てみましょう。ここではセッション 3894 が、セッション 63296 によるロック解除を待っています。つまり、セッション 3894 の保有するロックが解除されるのを待っているセッションがある一方で、セッション 3894 自体も別のロックの解除を待っているのです。これはロック・エスカレーション状態の典型です。onstat -g ses 63296 コマンドを使用し、セッション 63296 の動作を確認しなければなりません。

lockwt ユーティリティーのソース・コードには、注目すべき sysmaster クエリーがいくつかあります。これをベースとして使用すれば、独自のクエリーを記述することも可能です。

「ダウンロード」セクションで、lockwt ユーティリティーの無料版をダウンロードしてください。

上に戻る

オープン・カーソルに関する問題

テーブルを変更しようとして奇妙な問題に遭遇したことはないでしょうか。排他モードで明示的にロックしたにもかかわらず、テーブルを変更できないという問題です。この問題は次の例で再現できます。

				
Output from dbaccess -e stores_demo <script.sql>:
--------------------------------------------------
begin;
  Started transaction.

lock table customer in exclusive mode;
  Table locked.

alter table customer add (mycol integer);
  242: Could not open database table (informix.customer).
  106: ISAM error: non-exclusive access.
			

この状況が発生するのは、別のユーザーが customer テーブルで select カーソルを開いているからです。このカーソルは個々の行に対していかなるロックも設定しません。そうでなければ、テーブルを排他的にロックすることは不可能だったはずです。しかし、このカーソルが原因で、IDS はパーティション情報を変更できません。

この問題を解決するには、どのセッションが customer テーブルでカーソルを開いているのかを識別する必要があります。

1. customer テーブルの 16 進数のパーティション番号を調べます。
   • Select hex(partnum) from systables where tabname = "customer".
2. このテーブルのパーティション番号がゼロであれば、それはフラグメント化されたテーブルです。以下に示す SQL ステートメントを実行し、個々のフラグメントのパーティション番号を探す必要があります。
   

   Select st.tabname, dbinfo("dbspace", sf.partn), hex(sf.partn) from systables st, sysfragments sf, where st.tabid = sf.tabid and sf.fragtype = "T"and st.tabname = "customer".

3. 得られた 16 進数のパーティション番号について、現在開いているすべてのテーブルで検索します。
   

   onstat -g opn | grep -i <hex_partnum>

4. 各ユーザー・スレッドの共有メモリー・アドレス (rstcb 列の値) について、onstat -u コマンドを使用して検索します。
   

   onstat -u | grep <rstcb_without_leading_0x>

各データベース・セッションを識別した後、onmode -z <sessid> コマンドでそのセッションを終了します。

IDS バージョン 7.31.xD5、9.40、または 10 を実行している場合は、環境変数 IFX_DIRTY_WAIT も利用可能です。この環境変数は、エンジンまたはクライアント環境で設定できます。IFX_DIRTY_WAIT には、変更対象テーブルへの既存の dirty read アクセスが終了するまでの DDL ステートメントの待機秒数を指定します。指定した秒数が経過すると、IDS は、IFX_DIRTY_WAIT 変数を設定しなかった場合に送られるものと同じエラーをアプリケーションに返します。

上に戻る

まとめ

何百ものトランザクションがリアルタイムに並行実行されている環境において、ロック競合の分析は容易なことではありません。しかし IDS のロック・メカニズムについて簡潔に説明したこの記事は、ロック競合のより迅速な解決に役立つことでしょう。

この記事の執筆中に、IDS (IBM のハイエンド OLTP/組み込み環境向けデータ・サーバー) の次期バージョンがベータ・フェーズに入りました。新しいバージョンでは、ロックに関する魅力的な新規オプションが提供されることでしょう。これからもご注目ください。

ダウンロード

• コードサンプル： lockwt ユーティリティーのダウンロード

参考文献

学ぶために

• IDS 10 Information Center:IDS バージョン 10 の全ドキュメントを Web ベースで手軽に利用し、IDS 10 の詳細情報を入手していただけます。
  
  
• The Informix Zone: テクノロジーとマーケティングが出会う場所です。
  
  
• developerWorks Informix ゾーン:記事/チュートリアルの参照や他のリソースとの連携を通じて、Informix のスキルを拡大していただけます。
  
  
• IDS サポート:IDS に関する有益な技術情報や各種サポート情報が用意されています。
  
  
• Informix Webcast:Informix の Webcast に関する最新情報が用意されています。
  
  
• developerWorks の Information Management ゾーン:DB2 に関する知識を深めていただけます。テクニカル文書、実用記事、教育情報、ダウンロード、製品情報などが用意されています。
  
  
• developerWorks の Technical events and webcasts: 最新の知識を入手していただけます。
  
  

製品や技術を入手するために

• IDS バージョン 10 の無料試用版をダウンロードしていただけます。
  
  
• Informix ソフトウェアのその他の評価版・試用版については、こちらでダウンロードしていただけます。
  
  
• 次回の開発プロジェクトに IBM の試用版ソフトウェアをお試しください。試用版は developerWorks から直接ダウンロードしていただけます。
  
  

議論するために

• ディスカッション・フォーラムにご参加ください。
  
  
• IDS Developer and User Forum は、IDS の開発者やユーザーの皆様にそれぞれの疑問、考え、アイデアなどを共有していただくための場です。
  
  
• International Informix User Group で、世界中の何千もの Informix ユーザーからなるネットワークをご活用ください。
  
  
• Google の Informix-Newsgroup comp.databases.informix では、Informix のデータベース管理ソフトウェアについてディスカッションしていただけます。
  
  
• developerWorks のブログや developerWorks コミュニティーにご参加ください。
  
  

著者について

Eric Herber は IBM Informix データベース製品の独立コンサルタントとして 10 年間のキャリアを積んでいます。コンサルタントとしての仕事に加えて、Informix データベース・テクノロジーに関する複数の記事を発表してきました。余暇の楽しみはマウンテン・バイクです。

この記事を評価する

コメント

上に戻る

目次

• はじめに
• 動的ロック割り当て
• ロック待機時間
• デッドロック
• analyze-lock
• Lockwt ユーティリティー
• オープン・カーソルに関する問題
• まとめ
• ダウンロード
• 参考文献
• 著者について
• コメント