8. CREATE EVENT ... TYPE DEADLOCK
循環デッドロックを検出し、報告する新しいEVENTタイプが導入されています。デッドロックとは、使用率の高いシステムで時々発生する現象であり、2つの異なる接続がそれぞれ目的のローをロックした後、直ちに相手がロックしているローのロックを要求することです。デッドロックが発生すると、両方の接続がブロックされ、どちらの接続も必要なものを取得できず、第三者がデッドロックを解消するアクションを起こさない限り、どちらの接続の動作も進展しません。
その第三者の役割を果たすのがSQL Anywhereです。これまでのSQL Anywhereの機能でも、デッドロックを検出し、デッドロックを「解決」することができました。その方法とは、どちらか一方の接続を犠牲にして、ROLLBACKを発行し、そのアプリケーションにエラーメッセージを返すというものです。
そこまでのところに問題はありません。一方のユーザーが勝ち、もう一方のユーザーは処理をやり直し、メンテナンスプログラマーはエラーを探すことになります。そして、ほとんど常に、原因はプログラミングのエラーにあります。SQL文を実行する順序に何らかの欠陥があるのです。問題は、どのテーブルまたはローがデッドロックに関係していたのか、あるいはそれぞれの接続がエラーの発生時に何をしていたのか、ということを判別する手掛かりが標準のデッドロックエラーメッセージからはまったく得られないことです。この問題を解決するのが新しいCREATE EVENT ... TYPE DEADLOCK文です。この文を使用すると、デッドロックに関連するすべての詳細情報を記録し、出力できます。記録される詳細情報には、両方の接続の接続番号とユーザーID、デッドロック発生時に実行されていた実際のSQL文が含まれます。
CREATE EVENT deadlock TYPE DEADLOCK HANDLER BEGIN -- Copy fresh entries from sa_report_deadlocks() INSERT deadlock ( snapshotId, snapshotAt, waiter, who, what, object_id, record_id, owner, is_victim, rollback_operation_count ) SELECT * FROM sa_report_deadlocks() WHERE NOT EXISTS ( SELECT * FROM deadlock WHERE deadlock.snapshotId = sa_report_deadlocks.snapshotId AND deadlock.snapshotAt = sa_report_deadlocks.snapshotAt ); COMMIT; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' deadlock' ) TO CONSOLE; END;
ここで使用した組み込みのsa_report_deadlocks()プロシージャは新しいものではありませんが、SQL Anywhere 11で大幅に改良されました。このプロシージャは、サーバの起動後に発生した個々のデッドロックについて、犠牲になった接続ともう一方の接続に対応する少なくとも2つのローが含まれた結果セットを返します。INSERTはそのデータを「deadlock」という名前の常設ユーザー定義テーブルにコピーします。WHERE NOT EXISTSは、新たなデッドロックが発生したときに古いローが再びコピーされることを防止するために必要です。
「deadlock」テーブルは次のようになります。人工的なプライマリキーとして機能する「row_number」という余分なカラムが1つあり、さらにsa_report_deadlocks()が返したすべてのものに対応するカラムがあります。
CREATE TABLE deadlock ( row_number BIGINT NOT NULL DEFAULT AUTOINCREMENT, snapshotId BIGINT NOT NULL, snapshotAt TIMESTAMP NOT NULL, waiter INTEGER NOT NULL, who VARCHAR ( 128 ) NOT NULL, what LONG VARCHAR NOT NULL, object_id UNSIGNED BIGINT NOT NULL, record_id BIGINT NOT NULL, owner INTEGER NOT NULL, is_victim BIT NOT NULL, rollback_operation_count UNSIGNED INTEGER NOT NULL, PRIMARY KEY ( row_number ) );
これを機能させるには、次のように指定して、サーバーにレポートデータを収集するように指示する必要があります。
SET OPTION PUBLIC.LOG_DEADLOCKS = 'ON';
'LastStatement'接続プロパティを有効にして、ブロックされた実際のSQL文がレポートデータに表示されるようにすると、作業が非常に楽になります。それを行う最も簡単な方法は、サーバを起動するときにdbsrv11 -zlオプションを指定することです。
図3は、デッドロックの後に、次のクエリによって表示された情報を示しています。waiterは接続番号、whoはユーザー名、whatはCONNECTION_PROPERTY('LastStatement')、is_victimはROLLBACKの指示を受けた接続を示しています。
SELECT waiter, who, what, is_victim FROM deadlock ORDER BY row_number;
