PostgreSQL9.6：新增加“idle in transaction”超時(shí)空閑事務(wù)自動(dòng)查殺功能

meislzhua 發(fā)布于2019-07-25 12:34 / 3807人閱讀

摘要：以上出自發(fā)行說(shuō)明，這段指出版本支持自動(dòng)查殺超過(guò)指定時(shí)間的空閑事務(wù)連接，下面演示下。修改以下參數(shù)備注參數(shù)單位為毫秒，這里設(shè)置超時(shí)空閑事務(wù)時(shí)間為秒。數(shù)據(jù)庫(kù)日志備注數(shù)據(jù)庫(kù)日志里清晰地記錄了進(jìn)程的連接由于空閑事務(wù)超時(shí)被斷開連接。

熟悉 PostgreSQL 的朋友應(yīng)該知道 “idle in transaction” 進(jìn)程，引發(fā) idle in transaction 的原因很多，例如應(yīng)用代碼中忘記關(guān)閉已開啟的事務(wù)，或者系統(tǒng)中存在僵死進(jìn)程等，曾經(jīng)看到過(guò)某個(gè)庫(kù)中的 idle in transaction 進(jìn)程存在一年有余，這類進(jìn)程嚴(yán)重危害了數(shù)據(jù)庫(kù)的安全，例如它會(huì)阻止 VACUUM 進(jìn)程回收記錄，造成表數(shù)據(jù)膨脹，同時(shí)它有可能引起整個(gè) PostgreSQL 數(shù)據(jù)庫(kù) Transaction ID Wraparound 的風(fēng)險(xiǎn)。

Allow sessions to be terminated automatically if they sit too long in
an idle-in-transaction state (Vik Fearing) This behavior is enabled
and controlled by the new configuration parameter
idle_in_transaction_session_timeout. It can be useful to prevent
forgotten transactions from holding onto locks or preventing vacuum
cleanup for very long periods.

以上出自 PostgreSQL9.6 Beta1 發(fā)行說(shuō)明，這段指出9.6版本 PostgreSQL 支持自動(dòng)查殺超過(guò)指定時(shí)間的 idle in transaction 空閑事務(wù)連接，下面演示下。

--修改 postgresql.conf 以下參數(shù)

idle_in_transaction_session_timeout = 20000

備注：參數(shù)單位為毫秒，這里設(shè)置 idle in transaction 超時(shí)空閑事務(wù)時(shí)間為 20 秒。

--重載配置文件

[pg96@db1 pg_root]$ pg_ctl reload
server signaled

備注：此參數(shù)修改后對(duì)當(dāng)前連接依然生效，應(yīng)用不需要重連即能生效。

--開啟會(huì)話一：模擬一個(gè)事務(wù)

[pg96@db1 ~]$ psql francs francs
psql (9.6beta1)
Type "help" for help.

francs=> begin;
BEGIN

francs=> select 1;
 ?column? 
----------
        1
(1 row)

事務(wù)中，不提交也不回滾。

--開啟會(huì)話二：監(jiān)控

postgres=# select * from pg_stat_activity where pid<>pg_backend_pid();
-[ RECORD 1 ]----+------------------------------
datid            | 16386
datname          | francs
pid              | 7776
usesysid         | 16384
usename          | francs
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2016-06-01 16:03:12.557328+08
xact_start       | 2016-06-01 16:03:16.921353+08
query_start      | 2016-06-01 16:03:18.754706+08
state_change     | 2016-06-01 16:03:18.755422+08
wait_event_type  | 
wait_event       | 
state            | idle in transaction
backend_xid      | 
backend_xmin     | 
query            | select 1;

postgres=# select * from pg_stat_activity where pid<>pg_backend_pid();
(0 rows)

備注：開始還能監(jiān)控到這個(gè) "idle in transaction" 的事務(wù)，大概過(guò)了 20秒后，這個(gè)事務(wù)查詢不到了。

--再回到會(huì)話一

francs=> select 1;
 ?column? 
----------
        1
        
FATAL:  terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

備注：回到會(huì)話一執(zhí)行 select 1 測(cè)試命令，發(fā)現(xiàn)連接被斷開了，報(bào)錯(cuò)代碼很明顯，idle-in-transaction 超時(shí)了。

--數(shù)據(jù)庫(kù)日志

2016-06-01 16:03:38.756
CST,"francs","francs",7776,"[local]",574e96c0.1e60,1,"idle in
transaction",2016-06-01 16:03:12 CST,2/5887,0,FATAL,25P03,"terminating
connection due to idle-in-transaction timeout",,,,,,,,,"psql"

備注：數(shù)據(jù)庫(kù)日志里清晰地記錄了 7796 進(jìn)程的連接由于空閑事務(wù)超時(shí)被斷開連接。

--參考
idle_in_transaction_session_timeout (integer)
Preventing Transaction ID Wraparound Failures