PostgreSQL 如何判断idle in transaction的事务中有没有东西要提交-CSDN博客

背景

如何判断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也不为空。