データベースのデッドロックを回避する方法_SQL Anywhere

[former_member186998]

 

目的
このページの目的は、リレーショナル・データベースのデッドロックを特定して対処する方法について説明します。

概要
「デッドロック」とうい用語は、「サイクル・デッドロック」と呼ばれる状況を指しています。2つ以上の競合するトランザクションが同じリソース（通常、テーブルやローのロック）上で相互にブロックしているため、どのトランザクションも処理を進行できなくなり、サーバ側でいずれかのトランザクションを強制的に終了する必要がある状況です。

データベースのデッドロックを解決する方法として、状況に応じて2つの異なるアプローチが考えられます。それぞれのアプローチは、入手可能な情報、対応する側の経験または、嗜好に基づいています。データベース・スキーマの修正を好む管理者もいれば、SQLコードやサーバ／接続オプションの変更を優先する管理者もいます。

デッドロックの難しい点は、テスト環境やQA環境ではほとんど発生せず、開発サイクルの早期に発見することが困難であるということです。通常、デバッグは困難であり、状況によっては、コードの修正もできません。

データベースのデッドロックを回避するために、いくつかのことが必要です。
・デッドロック情報をログに記録する
・関連するSQLの識別
・パフォーマンスのためにクエリが最適化されていることを確認
・トランザクションを短くする
・一般的なパフォーマンスを改訂する

デッドロックは、データベース設計及び、SQLコーディングの貧弱さまたは、システムに隠れているその他の問題の症状であることを覚えておくことが重要です。

このドキュメントの例では、SQL Anywhereをデータベース・サーバとして使用していますが、基本的には、SAP HANA、SAP ASE、SAP IQ、Microsoft SQL Server、Oracleなどのその他のリレーショナル・データベース・システムにも同様の手法を適用できます。

アプリケーションがデッドロックの影響を受けているかどうか確認する方法
デッドロックは、散発的（一度起こった後、再び繰り返される）または、反復的（一日の特定の時間に、または特定のレポート/プロシージャ・コールの処理時）に発生する可能性があります。 データベース管理者は、無視するものと注意を払うものか判断する必要があります。 デッドロックが発生する理由は、アプリケーションとデータベース設計に絞り込むことが重要なポイントです。

アプリケーションが異常な動作をしている場合は、ほとんどの場合、処理が正常に実行されているが、何も変更されていないにもかかわらずトランザクションがロールバックされたり、スクリプト処理が失敗したり、アプリケーションから直接、次のエラーが返される場合：

SQLCODE=-306, ODBC 3 State="40001"

簡単な分析を実行し、デッドロックの原因をデータベースで検証します。

デッドロック情報のロギング
SAP SQL Anywhereには、ユーザがデッドロックを解決する上で必要な情報を入手できるデッドロック・ロギング機能があります。デッドロック・ロギング機能は、デフォルトでは有効になっていません。この機能を有効にするには、管理者がSybase Central または Interactive SQL を使用して有効にする必要があります。デッドロック・ロギング機能に加えて、ユーザはデッドロック発生時に実行されているSQL文を追跡するために、データベース・オプション'RememberLastStatement'を有効にする必要があります。

必要なオプションを有効にするには、Interactive SQL から次のコマンドを実行します。

// make sure default blocking_timeout has not been modified
SET OPTION PUBLIC.blocking_timeout = 0;
// set server option to remember LastStatement
SET OPTION PUBLIC.rememberlaststatement = 'On';
// remember information about deadlocks
SET OPTION PUBLIC.log_deadlocks = 'On';

必要なオプションの設定後、すべての情報は再起動が発生するか、手動で消去されるまでサーバによって一時的にメモリに格納されます。

デッドロックの例
シンプルなデッドロック情報を取得するには、次の例を使用します。2つのdbisqlウィンドウ（'Connection 1' と 'Connection 2'）を開き、demo16 データベースに接続します。'Connection 1'から次のコマンドを実行すると、deadlock_example テーブルと、このテーブルに対するupdate と select を実行する2つのプロシージャが作成されます。

// SQL Anywhere 16 create sample table
CREATE TABLE IF NOT EXISTS deadlock_example
(
pk INT NOT NULL PRIMARY KEY,
s1 VARCHAR(30) NOT NULL
);
CREATE OR REPLACE PROCEDURE proc1()
BEGIN // make sure isolation level is set to 1
SET TEMPORARY OPTION isolation_level = 1;
// clean the table first
DELETE deadlock_example;
// populate sample table with two rows
INSERT INTO deadlock_example (pk, s1) VALUES (1, 'initial 1');
INSERT INTO deadlock_example (pk, s1) VALUES (2, 'initial 2');
COMMIT;
// update the table, which creates a read lock on the row
UPDATE deadlock_example SET s1 = 'deadlock 1' where pk = 1;
WAITFOR DELAY '00:00:10';
SELECT * FROM deadlock_example WHERE pk = 2;
COMMIT;
END;
CREATE OR REPLACE PROCEDURE proc2()
BEGIN //make sure you are in isolation level 1
SET TEMPORARY OPTION isolation_level = 1;
//lock row with pk = 2
UPDATE deadlock_example SET s1='deadlock 2' where pk=2;
//try to read row with pk = 1
SELECT * FROM deadlock_example where pk = 1;
COMMIT;
END;

実際の環境と同様に、この例では、デッドロックがタイミングに依存するイベントに該当することを示しています。 WAITFOR DELAYの呼び出しは、deadlock_example ローがロックされている間、データベース上で長時間実行されているトランザクションをシミュレートします。

次に、Connection 1から、以下を実行して下さい：

CALL proc1();

次に、Connection 2から次のコマンドを（10秒以内に）実行します。

CALL proc2();

proc2()の実行直後にエラーが発生します。 [Show Details] をクリックすると、デッドロックが検出されたことを示す次のメッセージが表示されます。



SQL Anywhere には、デッドロック情報を表示する sa_report_deadlocks() システム・ストアド・プロシージャが用意されています。 次に dbisql 呼び出しのサンプルを示します。

SELECT * FROM sa_report_deadlocks();

snapshotId,snapshotAt,waiter,who,what,object_id,record_id,owner,is_victim,rollback_operation_count
1,'2014-06-12 14:56:13.298',2,'DBA',,3591,37617666,1,false,1
1,'2014-06-12 14:56:13.298',1,'DBA',,3591,37617667,2,true,1

sa_report_deadlocks() の情報以外に、デッドロックのビジュアル・インタフェースをSybase Centralで確認できます。 データベース・アイコンをクリックし、[Deadlocks] タブを選択するだけです。



デッドロックの定義に従うと、デッドロックが発生する場合、少なくとも2つの異なるトランザクションが関係しています。 デッドロックが発生する可能性は、トランザクションの持続時間と関連するテーブル（ロック）の数に比例して増加します。 言い換えれば、デッドロックを回避するためには、トランザクションの持続時間を短縮し、トランザクションの処理中にアクセスされるテーブル/ロックの数を削減することが重要です。

長時間実行されるトランザクションは、クエリの記述やインデックスの問題によって発生します。 多くのケースで、トランザクションの持続時間を短縮するとパフォーマンスのボトルネックが解消されるだけでなく、デッドロックも解決されます。

デッドロックを解決する主な方法は、関連するトランザクションのロック動作を変更することです。その結果、得られる結果が同じでも、ロックに関する動作は異なります。

ロック動作を変更するには、いくつかの方法があります。
例えば：
・既存のSQLコードの変更（例えば、独立性レベルの変更、NOLOCKヒント、文の並び替え）
・既存のデータベーススキーマの変更（インデックスの変更、追加、削除）

環境に応じて、さまざまなソリューションを適用できます。コンパイルされたアプリケーション内にSQLコードが埋め込まれている場合、唯一の選択肢は、スキーマを変更することです（例えば、トランザクションを高速化するために新しいインデックスを追加すること）。SQLの変更が問題がない他のシステムでは、ストアド・プロシージャを簡単に書き直すことができます。

上記の例では、WAITFORコマンドで秒数を減らすか、独立性レベルを '0'に変更すると、次のように、デッドロックが発生する可能性が低下するか、完全に解消されます。

SELECT * FROM deadlock_example WITH (NOLOCK) WHERE pk = 2

関連コンテンツ
関連ドキュメント
sa_report_deadlocks システムプロシージャ
チュートリアル：デッドロックの診断

このページは、以下の英語ページの抄訳です。
Detecting and Eliminating Deadlocks from a SQL Anywhere Database