資料庫在交易連線中長時間閒置

資料庫的連線已經超過 1800 秒都處在 idle in transaction 狀態。

主題

支援的引擎版本
Context
造成此問題的可能原因
動作
相關指標

支援的引擎版本

所有版本的 Aurora PostgreSQL 皆支援此洞察資訊。

Context

idle in transaction 狀態的交易可以擁有封鎖其他查詢的鎖定。也可以防止 VACUUM (包含自動清空) 清理無效資料列，導致索引或資料表膨脹，或導致交易 ID 包圍。

造成此問題的可能原因

尚未使用 COMMIT、ROLLBACK 或 END 命令，關閉以 BEGIN 或 START TRANSACTION 在互動式工作階段中啟動的交易。這會導致交易移至 idle in transaction 狀態。

動作

您可以透過查詢 pg_stat_activity，找出閒置的交易。

請在您的 SQL 用戶端中執行下列查詢，以列出 idle in transaction 狀態的所有連線，並按持續時間排序：


SELECT now() - state_change as idle_in_transaction_duration, now() - xact_start as xact_duration,* 
FROM  pg_stat_activity 
WHERE state  = 'idle in transaction'
AND   xact_start is not null
ORDER BY 1 DESC;

根據洞察的原因，我們會建議不同的動作。

主題

End 交易
終止連線
設定 idle_in_transaction_session_timeout 參數
檢查 AUTOCOMMIT 狀態
檢查應用程式程式碼中的交易邏輯

End 交易

使用 BEGIN 或 START TRANSACTION 在互動式工作階段中啟動交易時，該筆交易會移至 idle in transaction 狀態。交易會保持在此狀態，直到您發出 COMMIT、ROLLBACK、END 命令結束交易，或完全斷開連線以轉返結束交易。

終止連線

使用以下查詢，終止與閒置交易的連線：


SELECT pg_terminate_backend(pid);

pid 是連線的程序 ID。

設定 idle_in_transaction_session_timeout 參數

在新的參數群組中設定 idle_in_transaction_session_timeout 參數。設定此參數的優點在於，不需要手動介入即可終止長時間閒置的交易。如需此參數的詳細資訊，請參閱 PostgreSQL 文件。

當交易處於 idle_in_transaction 狀態超過指定時間時，PostgreSQL 日誌檔會在連線終止之後報告下列訊息。


FATAL: terminating connection due to idle in transaction timeout

檢查 AUTOCOMMIT 狀態

根據預設，AUTOCOMMIT 為啟用狀態。但是，若客戶端意外將其關閉，請確認重啟。

在 psql 用戶端執行下列命令：
```
postgres=> \set AUTOCOMMIT on
```
在 pgadmin 中，從向下箭頭選擇 AUTOCOMMIT 選項以將其開啟。

檢查應用程式程式碼中的交易邏輯

調查應用程式邏輯，找出可能的問題。考慮下列動作：

檢查 JDBC 自動遞交是否在您的應用程式中設為 true。另外，請考慮在程式碼中使用明確的 COMMIT 命令。
檢查錯誤處理邏輯，確認其是否會在發生錯誤後關閉交易。
檢查交易開啟時，您的應用程式是否需要長時間處理查詢傳回的資料列。若是如此，請考慮對應用程式進行編碼，在處理資料列之前關閉交易。
檢查交易是否包含許多長時間執行的操作。若是如此，請將單一交易分割為多筆交易。