跳至內容

如何識別是什麼原因導致 Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 相容版資料庫執行個體上的查詢遭到封鎖?

2 分的閱讀內容
0

我想對 Amazon Relational Database Service (Amazon RDS) for PostgreSQL 或 Amazon Aurora PostgreSQL 相容版本資料庫執行個體上,遭到封鎖的查詢進行疑難排解。

解決方法

未提交的交易可能會封鎖新的查詢、使其進入休眠狀態,或在超過鎖定等待逾時或陳述式逾時時導致失敗。若要解決此問題,請找出並停止未提交的交易。

找出遭封鎖的交易

若要找出遭封鎖的交易,請開啟 Performance Insights 並使用 Database Insights。請使用資料庫負載圖表查看 Waits、Host、SQL 或 User 的資料庫負載維度。如需更多資訊,請參閱在 Amazon RDS 上使用 Performance Insights 監控資料庫負載。

若要找出遭封鎖交易的目前狀態,請執行以下陳述式:

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

**注意:**請將 TABLE NAME 替換為您的資料表名稱或條件。

如果 wait_event_type 欄中的值不是 Lock,則表示 CPU、儲存體或網路容量等資源出現效能瓶頸。若要解決效能瓶頸,請調校資料庫效能。例如,您可以新增索引、重寫查詢,或執行 vacuum 和 analyze 命令。如需更多資訊,請參閱使用 PostgreSQL 的最佳實務。

如果 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;

若要找出哪些工作階段封鎖了交易,請檢查 blocking_user、blocking_user_addr 和 blocking_client_port。在以下範例輸出中,主使用者使用 psql 封鎖了在 27.0.3.146 主機上執行的交易。blocking_pid 是 8740。

blocked_pid                           | 9069blocked_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;

停止交易

在您終止交易之前,請評估每筆交易對資料庫狀態和應用程式可能造成的影響。

若要停止交易,請執行以下陳述式:

SELECT pg_terminate_backend(PID);

**注意:**請將 PID 替換為交易的 blocking_pid。

相關資訊

Amazon Aurora PostgreSQL 等待事件

AWS 官方已更新 1 年前