PostgreSQL的idle in transaction連線狀態

在平時查詢pg_stat_activity這個檢視的時候，每一行包含了一個程式的相關資訊，包含當前正在執行的SQL，或者會話的狀態等等，state欄位表示當前程式的狀態。在PostgreSQL資料庫裡，其實程式碼裡總共定義了7種BackendState，但是最終給我們展現在pg_stat_activity裡顯示的只有6種，這個不顯示的STATE_UNDEFINED是PostgreSQL中定義的一個連線狀態。它表示客戶端連線到伺服器，但伺服器無法確定連線的狀態。

而其他正常幾種能展現給我們的幾種分別是：

1、Active(活動): 程式正在執行某個語句，處於活躍狀態
2、Idle(空閒): 程式正在等待客戶端的指令
3、idle in transaction(事務空閒):程式開啟了事務，但當前沒有執行任何語句
4、idle in transaction (aborted)(事務空閒-退出):程式開啟了事務，但當前沒有執行任何語句。並且事務中的一個語句報錯退出。（一般整個事物回滾後的狀態）
除了事務中宣告一個錯誤外，其餘情況與idle in transaction相同
5、fastpath function call(快速通道函式呼叫): 後臺正在執行某個快速通道函式
6、Disabled(禁用): 如果後臺禁用track_activities，則報告這個狀態

這裡主要介紹下idle in transaction，它是一種特殊的程式狀態，它表示程式裡的一個事務已經開始，但尚未完成。當一個事務處於idle in transaction狀態時，它可以接受新的查詢，但不能提交或回滾。這種狀態通常是由於客戶端應用程式在傳送查詢之後沒有傳送提交或回滾指令而導致的。可能在應用程式碼中忘記關閉已開啟的事務，或者系統中存在僵死程式等。

資料庫里長時間存在idle in transaction狀態的程式，會嚴重影響資料庫的效能，因為它會阻止其他事務的執行，從而影響資料庫的效能。此外，如果一個事務處於idle in transaction狀態太長時間，它會阻止VACUUM程式回收空間，造成 表資料膨脹，會導致 事務ID wraparound，甚至嚴重可能會 佔用大量的記憶體，從而導致資料庫崩潰。

舉個例子：
開啟一個session

postgres=# begin;
BEGIN
postgres=*# select 1;
 ?column? 
----------
        1
(1 row)
postgres=*# select pg_backend_pid();
 pg_backend_pid 
----------------
          13975
(1 row)
postgres=*# select pg_backend_pid();
 pg_backend_pid 
----------------
          13975
(1 row)

然後用另一個session查詢

postgres=# select * from pg_stat_activity where wait_event_type='Client' and pid=13975;
-[ RECORD 1 ]----+------------------------------
datid            | 13008
datname          | postgres
pid              | 13975
leader_pid       | 
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2023-02-25 22:27:12.651381+08
xact_start       | 2023-02-25 22:27:19.020989+08
query_start      | 2023-02-25 22:31:16.316464+08
state_change     | 2023-02-25 22:31:16.31659+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 
backend_xmin     | 
query_id         | 
query            | select pg_backend_pid();
backend_type     | client backend

可以看到顯式開啟的事務的程式，此時處於idle in transaction的狀態。因為他當下在這個事務裡並沒有正在執行的SQL，在事務裡處於空閒狀態。

而在PostgreSQL 9.6版本開始支援了 idle_in_transaction_session_timeout引數，這個引數可以自動查殺超過指定時間的 idle in transaction 空閒事務連線，用於清理應用程式碼中忘記關閉已開啟的事務，或者系統中存在僵死程式等。
需要注意的是，修改idle_in_transaction_session_timeout引數需要重啟資料庫才能生效。而且它不會影響idle狀態的事物。

繼續舉個例子：
當我調整了idle_in_transaction_session_timeout為1min的時候。

postgres@xmaster:~/data$ psql
psql (14.1)
Type "help" for help.
postgres=# show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout 
-------------------------------------
 1min
(1 row)
postgres=# begin;
BEGIN
postgres=*# select 1;
 ?column? 
----------
        1
(1 row)

繼續進行上邊的測試，並且同步開啟一個視窗動態檢視pg_log,經過一分鐘後，會發現日誌裡會列印出這樣一條

FATAL:  terminating connection due to idle-in-transaction timeout

但是在開啟事務的session，是沒有任何反應的，這不代表引數沒有生效。
當你此時繼續在這個session裡執行下一步操作的時候，資料庫就會給你一個FATAL的提示，告訴我們連線達到了idle-in-transaction的超時時間。

剛才說到了，當連線長時間處於idle in transaction這個狀態，會佔用大量記憶體，因為它會導致程式陣列中的事務不會被回收，從而導致記憶體洩漏。並且也會阻止VACUUM程式回收空間，造成表資料膨脹，會導致事務ID wraparound等等問題。所以我們有必要對資料庫裡的這種狀態的連線做好監控，必要的時候需要介入處理，但是，也不可盲目得去殺掉回話，因為萬一這個事務裡還有未提交的SQL，那麼輕易殺掉連線的舉動則是不明智的。

這個時候，我們就要關注pg_stat_activity的backend_xid了，因為它對資料庫有寫操作所以需要申請事務號，因此backend_xid有值。

而此時它沒有SQL在執行，並且是read committed的事務隔離級別，所以目前沒有事務快照資訊，backend_xmin為空。如果後面有QUERY正在執行中，那麼backend_xmin會有一個值，即這條QUERY啟動時的事務快照ID。

但是對於我們來說，通常情況下最主要關注的就是backend_xid，如果它不為空，則表示這個事務有需要提交的資料。