Lock diagnostics

Procedure

1. Log on to the AnalyticDB for PostgreSQL console.
2. In the upper-left corner of the console, select a region.
3. Find the instance that you want to manage and click the instance ID.

4. In the left-side navigation pane, choose Diagnostics and Optimization > Query Analysis.

5. Click the Lock Diagnostics tab.
6. The Lock Diagnostics page displays the following information.

   You can filter the lock diagnostics by Keyword, Username, Database, Filter Conditions, and Time Range.

   The lock diagnostics list contains the following information:

   Parameter	Description
   SQL	The SQL statement.
   Start Time	The time the query started.
   Process ID	The process ID of the query.
   Session ID	The session ID of the query.
   Database	The database where the query ran.
   Status	The query status.
   Waiting Duration	The waiting duration of the query.
   Username	The user who ran the query.

7. In the Actions column of the target record, click Diagnose to view the Lock Wait Properties and Lock Diagnostics Details.
   The lock wait properties section shows information such as process ID, username, database, start time, waiting duration, and status. The lock diagnosis details section displays a table detailing both the blocked and blocking processes. This table includes the query status, process ID, username, query statement, application name, and lock grant. You can use this information to identify the source of the block and the related SQL statements.

Resolve lock contention

If a query is waiting for a lock on the lock diagnostics page, you can either wait for the blocking query to finish, which allows your query to proceed, or you can terminate the blocking query.

Use the following methods to cancel or terminate the blocking query.

• Cancel a query

  You can cancel a query only if its session is in a running state. After you cancel a query, the database requires time for cleanup and transaction rollbacks. Use the following statement to cancel a query:

  SELECT pg_cancel_backend(<process ID>);

  If the session is already in an idle state, you must terminate the query instead.

• Terminate a query

  Use the following statement to terminate a query:

  SELECT pg_terminate_backend(<process ID>);

Related documents

AnalyticDB for PostgreSQL also allows you to view information about running SQL statements by using the pg_stat_activity view. For more information, see Analyze and diagnose running SQL statements by using pg_stat_activity.

Related APIs