如何在執行 Amazon RDS PostgreSQL 或 Aurora PostgreSQL 的資料庫執行個體上，識別出封鎖查詢的原因？

解決方案

當未經認可的交易超過鎖定等待逾時或陳述式逾時時，可能會導致新查詢遭到封鎖、進入休眠狀態和失敗。若要解決此問題，請識別並停止封鎖查詢的交易。

1.    對 pg_stat_activity 檢視執行以下查詢，以識別遭到封鎖的交易的目前狀態：

SELECT * FROM pg_stat_activity WHERE query iLIKE '%TABLE NAME%' ORDER BY state;

**注意事項：**將 TABLE NAME 取代為您的表格名稱或條件。

如果 wait_event_type 欄位的值為 Lock，則該查詢會遭到其他交易或查詢封鎖。如果 wait_event_type 欄位是任何其他值，則表示 CPU、儲存體或網路容量等資源存在效能瓶頸。若要解決效能瓶頸，請調整資料庫的效能。例如，您可以新增索引、重寫查詢或執行 vacuum 和 analyze 命令。如需詳細資訊，請參閱使用 PostgreSQL 的最佳實務。

如果您啟用效能深入分析，請檢視依等待事件、主機、SQL 查詢或使用者分組的資料庫負載，識別出遭到封鎖的交易。如需詳細資訊，請參閱使用 Amazon RDS 上的效能深入分析監控資料庫負載。

2.    如果 wait_event_type 欄位的值為 Lock，請執行以下命令來識別出交易遭到封鎖的原因：

SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocked_activity.client_addr as blocked_client_addr,
       blocked_activity.client_hostname as blocked_client_hostname,
       blocked_activity.client_port as blocked_client_port,
       blocked_activity.application_name as blocked_application_name,
       blocked_activity.wait_event_type as blocked_wait_event_type,
       blocked_activity.wait_event as blocked_wait_event,
       blocked_activity.query    AS blocked_statement,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocking_activity.client_addr as blocking_user_addr,
       blocking_activity.client_hostname as blocking_client_hostname,
       blocking_activity.client_port as blocking_client_port,
       blocking_activity.application_name as blocking_application_name,
       blocking_activity.wait_event_type as blocking_wait_event_type,
       blocking_activity.wait_event as blocking_wait_event,
       blocking_activity.query   AS current_statement_in_blocking_process
 FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted ORDER BY blocked_activity.pid;

3.    檢閱字首為 blocking 的欄位。在以下範例表格中，您可以看到遭到封鎖的交易在 27.0.3.146 主機上執行並使用 psql。使用 blocking_user、blocking_user_addr 和 blocking_client_port 來協助識別出封鎖交易的工作階段。

blocked_pid                           | 9069
blocked_user                          | master
blocked_client_addr                   | 27.0.3.146
blocked_client_hostname               |
blocked_client_port                   | 50035
blocked_application_name              | psql
blocked_wait_event_type               | Lock
blocked_wait_event                    | transactionid
blocked_statement                     | UPDATE test_tbl SET name = 'Jane Doe' WHERE id = 1;
blocking_pid                          | 8740
blocking_user                         | master
blocking_user_addr                    | 27.0.3.146
blocking_client_hostname              |
blocking_client_port                  | 26259
blocking_application_name             | psql
blocking_wait_event_type              | Client
blocking_wait_event                   | ClientRead
current_statement_in_blocking_process | UPDATE tset_tbl SET name = 'John Doe' WHERE id = 1;

**重要事項：**在終止交易之前，請評估每個交易對資料庫和應用程式狀態的潛在影響。

4.    執行以下查詢來停止交易：

SELECT pg_terminate_backend(PID);

**注意事項：在使用上述步驟找出的程序中，**將 PID 取代為 blocking_pid。

相關資訊

檢視鎖定的 PostgreSQL 說明文件

伺服器傳送訊號函式的 PostgreSQL 說明文件

wait_event 描述的 PostgreSQL 說明文件

適用於鎖定監控的 PostgreSQL Wiki

Amazon Aurora PostgreSQL 等待事件