KingbaseESV8R6不同隔离级下xmin的区别

背景

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)，快照持续到事务结束释放。

[](javascript:void(0)😉

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)

[](javascript:void(0)😉

read committed隔离级别的事务，事务的每一条SQL都会获取快照，SQL执行结束就会释放快照。

[](javascript:void(0)😉

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)

[](javascript:void(0)😉

总结

以上实验帮助大家理解不同隔离级别下，根据pid查出backend_xid,backend_xmin对应的区别变化，更好理解这两个字段的涵义。当这两个字段任何一个有值时都表示有未结束的事务，这也对应起来开头提到的查询长事务的语句中的where条件：backend_xid is not null or backend_xmin is not null。

当然我们最常用的隔离级别是read committed，这是我们KES数据库的默认隔离级别，也是oracle的默认隔离级别。只有一些特殊的业务场景需要用到repeatable read隔离级别。