如何識別是什麼原因導致 Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 相容版資料庫執行個體上的查詢遭到封鎖?
我想對 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。
相關資訊
- 語言
- 中文 (繁體)

相關內容
已提問 2 年前
已提問 3 年前