PostgreSQL9.6+新增空闲事务自动查杀功能

[TOC]

概述

PostgreSQL9.6 版本较之前版本新增了一个参数：idle_in_transaction_session_timeout ，该参数是为了自动查杀存在的空闲事物
idle in transaction。下面我们详细了解下这个参数

参数简介

PG在日常使用中会有 ‘idle in transaction’ 进程，引发这个进程的原因一般都是 代码中忘记关闭已开启的事物，或是系统中存在僵死的进程等。
‘idle in transaction’ 会阻止vacuum，造成数据膨胀，又有可能引起PG的事物ID wraparound的危险

为解决这个问题。9.6新增超时自动查杀空闲事物的功能
默认：idle_in_transaction_session_timeout = 20000
单位毫秒，也就是20秒就会自动查杀空闲事物

这个参数的设置，可以先仔细观察自己系统中是否经常存在空闲事物，要是存在的多，可以设置一下
如果自己的业务逻辑上，在事物开启后需要等待一些时间的话。需要考虑下这个超市设置多少合适，根据自己业务而定。
如果设置的过短，可能会对一些业务产生影响

简单测试

打开参数

该参数并不是默认开启，需要手动配置才能开启此功能。在postgresql.conf文件中找到添加 一行

idle_in_transaction_session_timeout = 20000 

修改此参数无需重启数据库，reload即可。应用连接也无需重连

pg_ctl reload 

设置之后在命令行可查看该参数设置

postgres=# show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout 
-------------------------------------
 20s
(1 row)

即表示已经启用

测试事物

开启一个事物，创建一张表插入一条数据

postgres=# begin;
BEGIN

postgres=# create table test03(id int, name text);
CREATE TABLE

postgres=# insert into test03 values (1,'dasd');
INSERT 0 1

此时查看事物状态，可以看到 state 为 idle in transaction，此时就触发了上述参数控制的内容。若在超时时间内继续事物操作，则无任何报错。但是一旦超过超时时间。事物就会被强制中断。

select * from pg_stat_activity where pid<>pg_backend_pid();
-[ RECORD 3 ]----+--------------------------------------
datid            | 13285
datname          | postgres
pid              | 2015
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2019-04-23 19:16:30.454131+08
xact_start       | 2019-04-23 21:25:04.850255+08
query_start      | 2019-04-23 21:25:46.20997+08
state_change     | 2019-04-23 21:25:46.210208+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 606
backend_xmin     | 
query            | insert into test03 values (1,'dasd');
backend_type     | client backend

超时等待

我们等待20s至事物超时。
再在原事物中随便执行一个命令
可以发现已经报错

postgres=# select 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.
postgres=# 

我们在看下之前我们创建的表和插入的一条数据，已经不存在了，说明此参数杀事物时采用回滚的方式

结语

该参数在复杂的应用场景中可能会满足客户的业务需求。具体的超时时间需要客户根据自己的业务类型来做出选择。但是宜多不宜少，过少的话会将一些正在进行的事物回滚，导致出错。过多的话只会让清理慢一些。影响相对小一点。