PostgreSQL如何判斷idleintransaction的事務中有沒有東西要提交

標籤

PostgreSQL , idle in transaction , 事務快照 , 修改 , xid

---

背景

如何判斷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不為空的，即說明這個事務有東西要提交。