ApsaraDB RDS for SQL Server におけるデッドロックの処理方法 - ApsaraDB RDS

問題の説明

アプリケーションが同じテーブルやリソースに頻繁に読み取りや書き込みを行うと、デッドロックが発生することがあります。デッドロックが発生すると、SQL Server はトランザクションの 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.

ソリューション

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

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

次の SQL ステートメントを実行して、SYS.SYSPROCESSES を定期的に監視します。
```
WHILE 1 = 1
BEGIN
SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
WAITFOR DELAY '[$Time]';
END;
```
説明
この例では [$Time] を使用しています。ポーリング間隔は、00:00:01 のようにカスタマイズできます。

複数回のイテレーションの結果、SPID 53 は LCK_M_X タイプのロックを、SPID 56 は LCK_M_S タイプのロックを待機しています。SPID 53 の blocked 値は 56、SPID 56 の blocked 値は 53 であり、これは互いにブロックし合っていることを示します。両方のセッションの waittime は、ループごとに増加します。

説明
監視結果では、blocked 列はブロッキングセッションのセッション ID を示し、waitresource 列はブロックされているセッションが待機中のリソースを示します。この結果は、SPID 53 と SPID 56 が互いにブロックし合い、デッドロックを形成していることを示しています。

次の 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

2 つのセッションが互いにブロックし合い、デッドロックが発生しています。request_session_id 56 は、blocking_session_id 62 によってオブジェクト Tbl2 上でブロックされており、request_mode は X です。同時に、セッション 62 はセッション 56 によってオブジェクト Tbl1 上でブロックされており、request_mode は X です。両方のセッションの RequestingText は INSERT ステートメントを示しており、デッドロックにつながる循環依存を確認できます。

返される列を次の表で説明します。

パラメーター	説明
`DBName`	リクエスト元のセッションがアクセスしているデータベース。
`request_session_id`	ブロックされているセッションの ID。
`blocking_session_id`	ブロッキングセッションの ID。
`BlockedObjectName`	ブロックされているセッションがアクセス中のオブジェクト。
`resource_type`	待機対象のリソースのタイプ。
`RequestingText`	ブロックされているセッションが実行したステートメント。
`BlockingText`	ブロッキングセッションが実行しているステートメント。
`request_mode`	ブロックされているセッションがリクエストしたロックモード。

インスタンスが ApsaraDB RDS for SQL Server 2012 を実行している場合は、SQL Server Profiler を使用してデッドロックグラフを監視し、キャプチャすることもできます。

[トレースのプロパティ] ダイアログボックスの [イベントの選択] タブで、[ロック] カテゴリを展開し、[デッドロックグラフ] イベントと [Lock:Deadlock] イベントを選択します。

キャプチャされたデッドロックグラフ：

SQL Server Profiler のトレース結果には、2 つの [Lock:Deadlock Chain] イベントが表示されます。SPID 52 は test_shrink データベース (トランザクション 1374753) で 5-X (排他) ロックを保持し、一方 SPID 55 は 3-S (共有) ロック (トランザクション 1374762) を保持しています。これにより、続いて [Lock:Deadlock] イベントがトリガーされます。デッドロックグラフは、SPID 52 と SPID 55 が、それぞれ test_shrink.dbo.Th12 と test_shrink.dbo.Th11 のインデックス i 上のキーロックリソースをロックしていることを示しています。2 つのプロセスが互いが保持するロックをリクエストし (リクエストモードは X/S)、デッドロックとなる循環依存が発生します。

推奨事項

デッドロックを解決し、防止するには、次の推奨事項を参考にしてください。

デッドロックを迅速に解決するには、ブロッキングセッションを終了してください。
長時間実行トランザクションを確認し、速やかにコミットしてロックを解放してください。
デッドロックが共有ロックによって引き起こされ、アプリケーションがダーティリードを許容できる場合は、WITH (NOLOCK) クエリヒントを使用してください。たとえば、クエリに SELECT * FROM table WITH (NOLOCK); を追加します。これにより、クエリはロックを要求せずに実行できるため、デッドロックの防止に役立ちます。
アプリケーションロジックを見直し、リソースに一貫した順序でアクセスするようにしてください。

ApsaraDB RDS:ApsaraDB RDS for SQL Server におけるデッドロックの処理方法

問題の説明

ソリューション

推奨事項

関連操作