【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，如果它不为空，则表示这个事务有需要提交的数据。