ApsaraDB RDS for SQL Server におけるブロッキングの問題への対処 - ApsaraDB RDS

問題の説明

ApsaraDB RDS for SQL Server インスタンスでブロッキングが発生します。

原因

ブロッキングは、アプリケーションが特定のテーブルまたはリソースに対して頻繁に読み書きを行う際に発生します。深刻なブロッキングが発生すると、アプリケーション内のステートメントの実行速度が低下します。

トラブルシューティング

ApsaraDB RDS for SQL Server インスタンスでブロッキングの問題をトラブルシューティングするには、次の手順に従います。

SYS.SYSPROCESSES を継続的に監視して、ブロッキング情報を収集します。次のコマンドを実行します。
```
WHILE 1 = 1
BEGIN
    SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
    WAITFOR DELAY '00:00:01';
END;
```
説明
ポーリング間隔はカスタマイズできます。この例では 00:00:01 を使用しています。

クエリ結果は、spid=56 が spid=53 によってブロックされており (blocked=53)、LCK_M_S タイプのロックを待機していることを示しています。待機リソースは KEY: 5:72057594038845440 (98ec012aa510) です。複数のクエリサイクルにわたって、waittime の値が順次増加しており (9205、10206、11207、12208、13209)、ロックが解放されていないことを示しています。

説明
blocked 列はブロッキングセッションの session_id を示し、waitresource 列はブロックされたセッションが待機しているリソースを示します。返されるフィールドの詳細については、sys.sysprocesses の公式ドキュメントをご参照ください。

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 '00:00:01';
END;

クエリ結果の例： jacky データベースにおいて、blocking_session_id が 56 のセッションが、request_session_id が 62 のセッションをブロックしています。ブロックされたオブジェクトは Tbl1、リソースタイプは KEY、要求しているステートメント (RequestingText) は (@1 int,@2 int)INSERT INTO [dbo].[Tbl2]([id],[c...、ブロッキングステートメント (BlockingText) は BEGIN TRAN INSERT into dbo.Tbl1 (id, col) VALUE...、要求されたロックモードは S です。

次の表は、返されたパラメーターについて説明しています。

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

チューニングの推奨事項

パフォーマンスを向上させるには、次の推奨事項を参考にしてください。

ブロッキングを即座に解決するには、ブロッキングセッションの接続を閉じてください。
長時間実行されている未コミットのトランザクションを特定し、速やかにコミットしてください。
共有 (S) ロックがブロッキングの原因となっており、アプリケーションでダーティリードが許容される場合は、WITH (NOLOCK) クエリヒントを使用してください。たとえば、SELECT * FROM table WITH (NOLOCK); と記述することで、クエリがロックを要求しないようにし、ブロッキングを回避できます。
アプリケーションロジックを確認し、一貫した順序でリソースにアクセスするようにしてください。

ApsaraDB RDS:ApsaraDB RDS for SQL Server におけるブロッキングの問題への対処

問題の説明

原因

トラブルシューティング

チューニングの推奨事項

関連操作