sys_stat_activity视图中用两个字段表示:
backend_xid表示事务开始需要申请的事务id
backend_xmin表示一个事务快照,表示当前数据库中最小的正在运行的事务号,这个快照有可能是很久之前申请过的快照号,如果事务不结束,那么这个快照号一直保留,这符合MVCC特性,但是代价是表膨胀,vacuum不能及时回收。
我们可以通过这两个字段判断数据库中是否有长事务:
select * from sys_stat_activity where state<>'idle' and pg_backend_pid() != pid and (backend_xid is not null or backend_xmin is not null ) and extract(epoch from (now() - xact_start)) > 3; <时间阈值,单位秒> ;
下面我们看一下不同隔离级别下xmin的变化情况,借此理解MVCC。
实验
repeatable read隔离级别的事务,事务的第一条SQL会获取快照(xmin),快照持续到事务结束释放。
session A:
获取当前会话PID,并开启一个repeatable read隔离级别的事务
test=# select sys_backend_pid();
sys_backend_pid
----------------
25481
(1 row)
test=# begin transaction isolation level repeatable read;
BEGIN
session B:
查询会话A的xmin,xid
test=# select pid,backend_xid,backend_xmin from sys_stat_activity where pid=25481;
pid | backend_xid | backend_xmin
-------+-------------+--------------
25481 | |
(1 row)
session A:
执行第一条SQL
test=# select 1;
?column?
----------
1
(1 row)
session B:
115494为session A事务开启时数据库集群中未分配的最小事务号,或者未结束的最小事务号
test=# select pid,backend_xid,backend_xmin from sys_stat_activity where pid=25481;
pid | backend_xid | backend_xmin
-------+-------------+--------------
25481 | | 115494
(1 row)
在session B消耗2个事务(使用两种方法,各消耗1个事务ID)
test=# select txid_current();
txid_current
--------------
115494
(1 row)
test=# insert into tbl1 values (1);
INSERT 0 1
session A:
执行第二条SQL
test=# select 2;
?column?
----------
2
(1 row)
session B:
对于repeatable read隔离级别的事务来说,xmin不会变化。
test=# select pid,backend_xid,backend_xmin from sys_stat_activity where pid=25481;
pid | backend_xid | backend_xmin
-------+-------------+--------------
25481 | | 115494
(1 row)
session A:
执行一条会申请XID的SQL,例如插入数据。
test=# insert into tbl1 values (1);
INSERT 0 1
session B:
xid有值了,为session A对应事务申请下来的事务号,是已消耗掉的,XID不会变化。
test=# select pid,backend_xid,backend_xmin from sys_stat_activity where pid=25481;
pid | backend_xid | backend_xmin
-------+-------------+--------------
25481 | 115495| 115494
(1 row)
read committed隔离级别的事务,事务的每一条SQL都会获取快照,SQL执行结束就会释放快照。
session A:
获取当前会话PID,并开启一个read committed隔离级别的事务
test=# select sys_backend_pid();
sys_backend_pid
----------------
57479
(1 row)
test=# begin transaction isolation level read committed;
BEGIN
session B:
查询会话A的xmin,xid
test=# select pid,backend_xid,backend_xmin from sys_stat_activity where pid=57479;
pid | backend_xid | backend_xmin
-------+-------------+--------------
57479 | |
(1 row)
session A:
执行第一条SQL
test=# select 1;
?column?
----------
1
(1 row)
session B:
并没有观察到xmin,因为select 1;开始时获取,SQL执行结束,xmin马上就释放了
test=# select pid,backend_xid,backend_xmin from sys_stat_activity where pid=57479;
pid | backend_xid | backend_xmin
-------+-------------+--------------
57479 | |
(1 row)
session A:
执行第一条long SQL
test=# select sys_sleep(20);
session B:
sys_sleep执行结束前查看,可以观察到xmin,SQL执行结束再查看xmin就会消失。
test=# select pid,backend_xid,backend_xmin from sys_stat_activity where pid=57479;
pid | backend_xid | backend_xmin
-------+-------------+--------------
57479 | | 78339
(1 row)
session B:
在session B消耗2个事务(使用两种方法,各消耗1个事务ID)
test=# select txid_current();
txid_current
--------------
78339
(1 row)
test=# insert into tbl1 values (1);
INSERT 0 1
session A:
执行第2条long SQL
test=# select sys_sleep(20);
session B:
sys_sleep执行结束前查看,可以观察到xmin,SQL执行结束再查看xmin就会消失。
xmin与第一条SQL看到的78339不一样,因为read committed隔离级别的事务,每条SQL开始时都会新申请快照。注意这里和repeatable read隔离级别不一样,它对应的xmin不会变化。
test=# select pid,backend_xid,backend_xmin from sys_stat_activity where pid=57479;
pid | backend_xid | backend_xmin
-------+-------------+--------------
57479 | | 78341
(1 row)
session A:
执行一条会申请XID的SQL,例如插入数据。
test=# insert into tbl1 values (1);
INSERT 0 1
session B:
xid有值了,为session A对应事务申请下来的事务号,是已消耗掉的,XID不会变化,直到事务结束(session A commit后,backend_xid:78341变为空)。而backend_xmin为空表示已经没有未结束的最老的快照版本。这是好的现象,这不会引起表膨胀后vacuum进程回收失败。
test=# select pid,backend_xid,backend_xmin from sys_stat_activity where pid=57479;
pid | backend_xid | backend_xmin
-------+-------------+--------------
57479 | 78341 |
(1 row)
总结
以上实验帮助大家理解不同隔离级别下,根据pid查出backend_xid,backend_xmin对应的区别变化,更好理解这两个字段的涵义。当这两个字段任何一个有值时都表示有未结束的事务,这也对应起来开头提到的查询长事务的语句中的where条件:backend_xid is not null or backend_xmin is not null。
当然我们最常用的隔离级别是read committed,这是我们KES数据库的默认隔离级别,也是oracle的默认隔离级别。只有一些特殊的业务场景需要用到repeatable read隔离级别。