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 provides the following information.

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

   The lock diagnostics list contains the following fields:

   Parameter	Description
   SQL	The SQL text of the query statement.
   Start Time	The time when the query statement starts to execute.
   Process ID	The process ID of the query statement.
   Session ID	The session ID of the query statement.
   Database	The database on which the query statement is executed.
   Status	The status of the query statement.
   Waiting Duration	The waiting duration of the query statement.
   Username	The user who executes the query statement.

7. Click Diagnose in the Actions column of the target diagnostic entry to view Lock Wait Properties and Lock Diagnostics Details.
   The Lock Wait Properties section displays information such as Process ID, Username, Database, Start Time, Waiting Duration, and Status. The Lock Diagnostics Details section displays the information about blocked and blocking processes in a table, including Query Status, Process ID, Username, Query Statement, Application Name, and Lock Grant. You can use this information to identify the source of blocking and the related SQL statements.

Resolution

If a query does not return results for a long time and the lock diagnostics page shows that the query status is waiting for a lock, you can wait for the preceding queries to complete so that the system automatically executes your query task, or you can terminate the blocking query task.

You can cancel or terminate the blocking query. The following methods are available.

• Cancel a query

  The session to which the query belongs must be in the running state to cancel the query. After a query is canceled, the database requires some time to perform cleanup and roll back transactions. The SQL statement for canceling a query is as follows:

  SELECT pg_cancel_backend(<process ID>);

  If the session is already in the idle state, you must use the terminate method instead.

• Terminate a query

  The SQL statement for terminating a query is as follows:

  SELECT pg_terminate_backend(<process ID>);

References

AnalyticDB for PostgreSQL also supports the pg_stat_activity view for viewing the execution information of SQL statements. For more information, see Analyze and diagnose running SQL statements by using pg_stat_activity.

Related API operations