本文已参与「新人创作礼」活动，一起开启掘金创作之路
PostgreSQL每张表中都包含系统字段，详见：PostgreSQL表的系统字段
对于XMAX字段，其官方文档的解释如下：
The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting transaction hasn’t committed yet, or that an attempted deletion was rolled back.
通过上面的解释我们可以知道，xmax表示删除某行记录的事务ID，这也和我们之前所了解的是一样的，但事实上xmax字段不仅仅如此，不信且看下面的例子：
创建测试表插入一条测试数据，可以看到xmax字段为0。
bill@bill=>create table t1(id int,info text);
CREATE TABLE
bill@bill=>insert into t1 values(1,'bill');
INSERT 0 1
bill@bill=>select xmin,xmax,* from t1;
xmin | xmax | id | info
------+------+----+------
2668 | 0 | 1 | bill
(1 row)
接下来对改行数据执行select for update操作。
xmax字段竟然不是0了，可以看到xmax不仅仅是删除数据时会变化啊。
bill@bill=>select * from t1 where id = 1 for update;
id | info
----+------
1 | bill
(1 row)
bill@bill=>select xmin,xmax,* from t1;
xmin | xmax | id | info
------+------+----+------
2668 | 2669 | 1 | bill
(1 row)
那我们再看看update数据时xmax字段是不是会变化呢？
bill@bill=>select xmin,xmax,* from t1;
xmin | xmax | id | info
------+------+----+------
2672 | 0 | 1 | bill
(1 row)
bill@bill=>begin;
BEGIN
bill@bill=>update t1 set info = 'foucus' where id = 1;
UPDATE 1
bill@bill=>commit;
COMMIT
bill@bill=>select ctid,xmin,xmax,* from t1;
ctid | xmin | xmax | id | info
-------+------+------+----+--------
(0,2) | 2673 | 0 | 1 | foucus
(1 row)
似乎update并不会修改改行的xmax值，真的如此吗？且慢，再看看下面的例子：
bill@bill=>select ctid,xmin,xmax,* from t1;
ctid | xmin | xmax | id | info
-------+------+------+----+--------
(0,2) | 2673 | 0 | 1 | foucus
(1 row)
bill@bill=>begin;
BEGIN
bill@bill=>update t1 set info = 'bill' where id = 1;
UPDATE 1
bill@bill=>rollback;
ROLLBACK
bill@bill=>select ctid,xmin,xmax,* from t1;
ctid | xmin | xmax | id | info
-------+------+------+----+--------
(0,2) | 2673 | 2674 | 1 | foucus
(1 row)
奇怪，当我们rollback该事务后，刚刚执行的update操作竟然将xmax字段修改了，这是为什么呢？那么update操作究竟会不会修改xmax的值呢？
其实这些都是因为pg中的行锁也和xmax字段有关导致的。
因为这时候，xmax里的值存储的是一个行锁。当xid为2674的事务修改这条记录的时候，原来的元组的xmax就会写入这个xid号，表示这个元组被某个事务锁住了。而新的数据会被写入一个新的元组中。当我们回滚这个UPDATE操作的时候，新元组就死掉了。而写入了锁的xid的老元组又变得可见了。这时候，我们就看到了xmax中有值了。
我们可以通过pageinspect插件来继续一探究竟。
bill@bill=>select lp,
bill-# t_ctid as ctid,
bill-# t_xmin as xmin,
bill-# t_xmax as xmax,
bill-# t_attrs [ 1 ] as a,
bill-# t_attrs [ 2 ] as b
bill-# from heap_page_item_attrs(get_raw_page('t1', 0), 't1');
lp | ctid | xmin | xmax | a | b
----+-------+------+------+------------+------------------
1 | (0,2) | 2672 | 2673 | \x01000000 | \x0b62696c6c
2 | (0,3) | 2673 | 2674 | \x01000000 | \x0f666f75637573
3 | (0,3) | 2674 | 0 | \x01000000 | \x0b62696c6c
(3 rows)
可以看到前面的这条记录产生了3行数据，分别是第一行为insert产生的，第二行是第一次update产生的，第三行是第二次update产生的。而从我们上面查询的结果可以知道第二行数据目前是可见的。
那么我们就无法通过xmax字段来判断数据的可见性了，因为可能像上面这种情况，xmax字段存放的是行锁的信息。那么pg中要怎么确定元组的可见性呢？之前我在PostgreSQL clog详解中也提到过，可以通过infomask字段。
当我们对改行数据进行select for update操作时，可以看到其xmax_is_lock列变为true了。
bill@bill=>begin;
BEGIN
bill@bill=>select * from t1 for update;
id | info
----+--------
1 | foucus
(1 row)
bill@bill=>select lp,
bill-*# t_ctid as ctid,
bill-*# t_xmin as xmin,
bill-*# t_xmax as xmax,
bill-*# t_attrs [ 1 ] as a,
bill-*# t_attrs [ 2 ] as b,
bill-*# (t_infomask &128) ::boolean as xmax_is_lock,
bill-*# (t_infomask &1024) ::boolean as xmax_commited,
bill-*# (t_infomask &2048) ::boolean as xmax_rolled_back,
bill-*# (t_infomask &4096) ::boolean as xmax_multixact
bill-*# from heap_page_item_attrs(get_raw_page('t1', 0), 't1');
lp | ctid | xmin | xmax | a | b | xmax_is_lock | xmax_commited | xmax_rolled_back | xmax_multixact
----+-------+------+------+------------+------------------+--------------+---------------+------------------+----------------
1 | (0,2) | 2672 | 2673 | \x01000000 | \x0b62696c6c | f | t | f | f
2 | (0,2) | 2673 | 2675 | \x01000000 | \x0f666f75637573 | t | f | f | f
3 | (0,3) | 2674 | 0 | \x01000000 | \x0b62696c6c | f | f | t | f
关于t_infomask的值的含义，可以参考：src/include/access/htup_details.h，这里不再赘述。
既然该行被锁定后，xmax记录的时锁定改行的事务id，那么如果同一行被多个会话同时锁定又是什么情况呢？
创建测试表t1和t2：
bill@bill=>create table t1(c1 int primary key,info text);
CREATE TABLE
bill@bill=>create table t2(c1 int,c2 int references t1(c1));
CREATE TABLE
bill@bill=>insert into t1 values(1,'bill');
INSERT 0 1
更新t1的数据：
bill@bill=>update t1 set info = 'foucus' where c1 = 1;
UPDATE 1
bill@bill=>select lp,
bill-# t_ctid as ctid,
bill-# t_xmin as xmin,
bill-# t_xmax as xmax,
bill-# t_attrs [ 1 ] as a,
bill-# t_attrs [ 2 ] as b,
bill-# (t_infomask &128) ::boolean as xmax_is_lock,
bill-# (t_infomask &1024) ::boolean as xmax_commited,
bill-# (t_infomask &2048) ::boolean as xmax_rolled_back,
bill-# (t_infomask &4096) ::boolean as xmax_multixact
bill-# from heap_page_item_attrs(get_raw_page('t1', 0), 't1');
lp | ctid | xmin | xmax | a | b | xmax_is_lock | xmax_commited | xmax_rolled_back | xmax_multixact
----+-------+------+------+------------+------------------+--------------+---------------+------------------+----------------
1 | (0,2) | 2688 | 2689 | \x01000000 | \x0b62696c6c | f | f | f | f
2 | (0,2) | 2689 | 0 | \x01000000 | \x0f666f75637573 | f | f | t | f
(2 rows)
我们再接着往子表t2中插入数据，可以看到xmax变化了，同时通过infomask字段可以看到改行被锁住了。
bill@bill=>insert into t2 values(1,1);
INSERT 0 1
bill@bill=>select lp,
bill-# t_ctid as ctid,
bill-# t_xmin as xmin,
bill-# t_xmax as xmax,
bill-# t_attrs [ 1 ] as a,
bill-# t_attrs [ 2 ] as b,
bill-# (t_infomask &128) ::boolean as xmax_is_lock,
bill-# (t_infomask &1024) ::boolean as xmax_commited,
bill-# (t_infomask &2048) ::boolean as xmax_rolled_back,
bill-# (t_infomask &4096) ::boolean as xmax_multixact
bill-# from heap_page_item_attrs(get_raw_page('t1', 0), 't1');
lp | ctid | xmin | xmax | a | b | xmax_is_lock | xmax_commited | xmax_rolled_back | xmax_multixact
----+-------+------+------+------------+------------------+--------------+---------------+------------------+----------------
1 | (0,2) | 2688 | 2689 | \x01000000 | \x0b62696c6c | f | t | f | f
2 | (0,2) | 2689 | 2690 | \x01000000 | \x0f666f75637573 | t | f | f | f
(2 rows)
接着我们再另一个会话中往子表再插入一条数据：
bill@bill=>begin;
BEGIN
bill@bill=>insert into t2 values(2,1);
INSERT 0 1
观察t1表：
bill@bill=>select lp,
bill-*# t_ctid as ctid,
bill-*# t_xmin as xmin,
bill-*# t_xmax as xmax,
bill-*# t_attrs [ 1 ] as a,
bill-*# t_attrs [ 2 ] as b,
bill-*# (t_infomask &128) ::boolean as xmax_is_lock,
bill-*# (t_infomask &1024) ::boolean as xmax_commited,
bill-*# (t_infomask &2048) ::boolean as xmax_rolled_back,
bill-*# (t_infomask &4096) ::boolean as xmax_multixact
bill-*# from heap_page_item_attrs(get_raw_page('t1', 0), 't1');
lp | ctid | xmin | xmax | a | b | xmax_is_lock | xmax_commited | xmax_rolled_back | xmax_multixact
----+-------+------+------+------------+------------------+--------------+---------------+------------------+----------------
1 | (0,2) | 2688 | 2689 | \x01000000 | \x0b62696c6c | f | t | f | f
2 | (0,2) | 2689 | 1 | \x01000000 | \x0f666f75637573 | t | f | f | t
(2 rows)
可以看到这个时候xmax字段变成1了，显然这个不是事务id了，同时xmax_multixact字段变成true了。这表示xmax字段插入的值变成了Multixact ID。
这个Multixact ID=1表示什么呢？
bill@bill=>select * from pg_get_multixact_members('1');
xid | mode
------+-------
2692 | keysh
2693 | keysh
(2 rows)
当有多个事务锁了这个元组的时候，xmax是无法表示的，这时候，需要另外一个数据结构来存储这个信息了。我们通过pg_get_multixact_member可以看到锁定这条记录的两个事务。
总结：
通过上面的例子我们可以得出结论，xmax字段的作用有以下几种：
- 存储MVCC副本的最大可见xid，即数据被删除时；或者为0，即第一次插入数据的行。
- 当有行锁的时候，存储锁定这个元组的XID。
- 当有多个事务锁住这个元组的时候，存储multixact的ID。
