PostgreSQL 如何判断idle in transaction的事务中有没有东西要提交
背景
如何判断idle in transaction的事务中有没有东西要提交？
比如在一些情况下，你可能发现数据库中很多idle in transaction的事务，可能是一些僵尸事务，也可能是一些应用框架引起的，连接建立后就开启一个事务，实际上里面啥也没有。
postgres=# select pid, state from pg_stat_activity ;
pid | state
-------+---------------------
33231 |
33233 |
50650 | active
50723 | active
50801 | active
54168 | idle in transaction
51197 | idle
51983 | active
33229 |
33228 |
33230 |
(11 rows)
那么如果你需要清理掉一些事务时，如何让他优雅的退出，或者如何快速的清退那些没有什么影响的事务呢？
那就需要判断idle in transaction的事务中有没有东西要提交？
通过pg_stat_activity会话判断
pg_stat_activity会话中有两个字段，backend_xid，backend_xmin用来表示会话是否申请了事务号，以及会话的快照ID。
if (TransactionIdIsValid(local_beentry->backend_xid))
values[15] = TransactionIdGetDatum(local_beentry->backend_xid);
else
nulls[15] = true;
if (TransactionIdIsValid(local_beentry->backend_xmin))
values[16] = TransactionIdGetDatum(local_beentry->backend_xmin);
/* ----------
* LocalPgBackendStatus
*
* When we build the backend status array, we use LocalPgBackendStatus to be
* able to add new values to the struct when needed without adding new fields
* to the shared memory. It contains the backend status as a first member.
* ----------
*/
typedef struct LocalPgBackendStatus
{
/*
* Local version of the backend status entry.
*/
PgBackendStatus backendStatus;
/*
* The xid of the current transaction if available, InvalidTransactionId
* if not.
*/
TransactionId backend_xid;
/*
* The xmin of the current session if available, InvalidTransactionId if
* not.
*/
TransactionId backend_xmin;
} LocalPgBackendStatus;
例子1：
1、开启一个read committed事务
postgres=# begin;
BEGIN
postgres=# select 1;
?column?
----------
1
(1 row)
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
3497
(1 row)
2、查询它的事务状态
postgres=# select * from pg_stat_activity where pid=3497;
-[ RECORD 1 ]----+------------------------------
datid | 16461
datname | postgres
pid | 3497
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2018-02-07 13:09:44.506794+08
xact_start | 2018-02-07 13:12:48.322815+08
query_start | 2018-02-07 13:13:03.454437+08
state_change | 2018-02-07 13:13:03.454926+08
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid |
backend_xmin |
query | select pg_backend_pid();
backend_type | client backend
可以看到这个会话的backend_xid和backend_xmin都没有值，因为它没有对数据库有任何写操作所以不需要申请事务号，因此backend_xid为空。
并且它当前没有SQL在执行，并且它是read committed的事务隔离级别，因此目前没有事务快照信息，backend_xmin为空。
如果后面有QUERY正在执行中，那么backend_xmin会有一个值，即这条QUERY启动时的事务快照ID。
例子2：
1、开启一个repeatable read事务
postgres=# begin isolation level repeatable read ;
BEGIN
postgres=# select 1;
?column?
----------
1
(1 row)
2、查询它的事务状态
postgres=# select * from pg_stat_activity where pid=3497;
-[ RECORD 1 ]----+------------------------------
datid | 16461
datname | postgres
pid | 3497
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2018-02-07 13:09:44.506794+08
xact_start | 2018-02-07 13:16:30.995233+08
query_start | 2018-02-07 13:16:40.65339+08
state_change | 2018-02-07 13:16:40.653504+08
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid |
backend_xmin | 152207375
query | select 1;
backend_type | client backend
可以看到这个会话的backend_xid没有值，因为它没有对数据库有任何写操作所以不需要申请事务号，因此backend_xid为空。
它是repeatable read的事务隔离级别，因此在它执行了第一条SQL后，就会获得一个事务快照，并且跟随它到事务结束，因此backend_xmin不为空，并且对于rr以上隔离级别的事务，在整个事务过程中backend_xmin是一个不变的值。
例子3：
1、前面提到的任意一个事务，执行一个INSERT。
postgres=# insert into a values (2);
INSERT 0 1
2、查询它的事务状态
postgres=# select * from pg_stat_activity where pid=3497;
-[ RECORD 1 ]----+------------------------------
datid | 16461
datname | postgres
pid | 3497
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2018-02-07 13:09:44.506794+08
xact_start | 2018-02-07 13:16:30.995233+08
query_start | 2018-02-07 13:18:07.129475+08
state_change | 2018-02-07 13:18:07.13004+08
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid | 152207375
backend_xmin | 152207375
query | insert into a values (2);
backend_type | client backend
可以看到这个会话的backend_xid和backend_xmin都有值了，因为写入操作要申请事务号，因此backend_xid不为空。
它是repeatable read的事务隔离级别，因此在它执行了第一条SQL后，就会获得一个事务快照，并且跟随它到事务结束，因此backend_xmin也不为空。
小结
如何判断idle in transaction的事务中有没有东西要提交？
pg_stat_activity.backend_xid不为空的，即说明这个事务有东西要提交。