ApsaraDB RDS for SQL Serverインスタンスでデッドロックが発生した場合はどうすればよいですか。 - ApsaraDB RDS

問題の説明

アプリケーションがApsaraDB RDS for SQL Serverインスタンス上のテーブルまたはリソースからデータを頻繁に読み書きする場合、デッドロックが発生する可能性があります。デッドロックが発生すると、RDSインスタンスはトランザクションの1つを終了し、次のようなエラーメッセージをトランザクションを開始したクライアントに送信します。

Error Message: Msg 1205, Level 13, State 47, Line 1Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

解決策

クライアントからRDSインスタンスに接続します。詳細については、「ApsaraDB RDS For SQL Serverインスタンスへの接続」をご参照ください。

関連するビューを監視します。

次のSQL文を実行して、SYS.SYSPROCESSESビューを定期的に監視します。
1 = 1の
```
WHILE 1 = 1
BEGIN
SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
WAITFOR DELAY '[$Time]';
END;
```
説明
[$Time] はモニタリング間隔を指定します。この例では、00:00:01が使用されます。
次の図は、サンプル出力を示しています。
説明
出力では、blocked列は現在のセッションをブロックするセッションのIDを示し、waitresource列はブロックされたセッションが待機するリソースを示します。この例では、セッション53とセッション56とが互いにブロックし合い、デッドロックが発生する。 spidはセッションIDを示します。

次のSQL文を実行して、sys.dm_tran_locksおよびsys.dm_os_waiting_tasksビューを定期的に監視します。

while 1=1
    Begin
    SELECT
    db.name DBName,
    tl.request_session_id,
    wt.blocking_session_id,
    OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
    tl.resource_type,
    h1.TEXT AS RequestingText,
    h2.TEXT AS BlockingText,
    tl.request_mode
    FROM sys.dm_tran_locks AS tl
    INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
    INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
    INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
    INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
    INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
    CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
    CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
    waitfor delay '[$Time]'
    End

次の図は、サンプル出力を示しています。

下表にレスポンスパラメーターを示します。

パラメーター	説明
`DBName`	ブロックされたセッションがアクセスしようとするデータベースの名前。
`request_session_id`	現在のリクエストのセッションのID。セッションはブロックされたセッションです。
`blocking_session_id`	ブロッキングセッションのID。
`BlockedObjectName`	ブロックされたセッションによって管理されるオブジェクト。
`resource_type`	セッションが待機するリソースのタイプ。
`RequestingText`	セッションで実行されるステートメント。ステートメントはブロックされたステートメントです。
`BlockingText`	ブロッキングセッションで実行されるステートメント。
`request_mode`	セッションによって要求されるロックモード。

RDSインスタンスがSQL Server 2012を実行している場合は、SQL Server Profilerを使用してデッドロックを監視し、デッドロックグラフを生成することもできます。
デッドロックグラフを次の図に示します。

最適化の提案

最適化のために次の手順を実行できます。

ブロッキングセッションを終了して、ブロッキングの問題を解決します。
長期間コミットされていないトランザクションが存在するかどうかを確認します。トランザクションが存在する場合は、最も早い機会にトランザクションをコミットします。
共有ロックが原因でクエリがブロックされ、アプリケーションでダーティな読み取りが許可されている場合は、WITH (NOLOCK) クエリヒントを使用します。たとえば、クエリに対してSELECT * FROMテーブルWITH (NOLOCK); ステートメントを実行できます。このように、クエリは、デッドロックの問題を防ぐためにロックを適用しません。
アプリケーションロジックをチェックして、リソースに順番にアクセスします。

ApsaraDB RDS:ApsaraDB RDS for SQL Serverインスタンスでデッドロックが発生した場合はどうすればよいですか。

問題の説明

解決策

最適化の提案

関連ドキュメント