看了一些关于PostgreSQL的xmin与xmax的解释，感觉理解不到位，不妨做几个小实验来深入理解一下。纸上得来终觉浅，绝知此事要躬行！ 准备工作
CREATE TABLE parent( p_id integer PRIMARY KEY, p_val text ); CREATE TABLE child( c_id integer PRIMARY KEY, p_id integer REFERENCES parent(p_id), c_val text ); INSERT INTO parent (p_id, p_val) VALUES (42, 'parent');
第一个实验 #### 刚刚开始，表里面没有值
dump=# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val ------+------+------+------+------- (0 rows)
#### 插入一行记录到表parent
dump=# begin; BEGIN dump=*# dump=*# INSERT INTO parent (p_id, p_val) VALUES (42, 'parent'); INSERT 0 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2705 (1 row) dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val -------+------+------+------+-------- (0,1) | 2705 | 0 | 42 | parent (1 row)
ctid is the physical location of the tuple (Block 0, item 1) xmin contains the ID(2705) of the inserting transaction xmax is zero because the row is alive. #### 做一个rollback操作，这个时候表里面的数据就没有了
dump=*# rollback; ROLLBACK dump=# dump=# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val ------+------+------+------+------- (0 rows)
#### 但是实际上这行的数据保留在page里面，可以通过extension pageinspect中的方法来查看
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2705 | 0 | f | f | t | f | \x2a000000 | \x0f706172656e74
(1 row)
因为我们做了insert的rollback操作,这里 xmax_committed => f, xmax_rolled_back => t 没有delete操作 xmax => 0 #### 做vacuum操作，数据才从page里面被删除掉
dump=# vacuum parent; VACUUM
第二个实验 #### 插入一行
dump=# begin; BEGIN dump=*# INSERT INTO parent (p_id, p_val) VALUES (42, 'parent'); INSERT 0 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2707 (1 row) dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val -------+------+------+------+-------- (0,1) | 2707 | 0 | 42 | parent (1 row) dump=*# commit; COMMIT
#### 删除此行
dump=# begin;
BEGIN
dump=*# DELETE FROM parent WHERE p_id = 42;
DELETE 1
dump=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
2708
(1 row)
dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent;
ctid | xmin | xmax | p_id | p_val
------+------+------+------+-------
(0 rows)
dump=*# commit;
COMMIT
dump=#
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2707 | 2708 | f | t | f | f | \x2a000000 | \x0f706172656e74
(1 row)
dump=#
因为我们做了删除的commit操作,这里 xmax_committed => f, xmax_rolled_back => t 有delete操作 xmax => 2708 (delete 操作的transaction id) 第三个实验 #### 插入一行数据
dump=# begin; BEGIN dump=*# INSERT INTO parent (p_id, p_val) VALUES (42, 'parent'); INSERT 0 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2710 (1 row) dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val -------+------+------+------+-------- (0,1) | 2710 | 0 | 42 | parent (1 row) dump=*# commit; COMMIT
#### 删除，然后做rollback操作
dump=# begin; BEGIN dump=*# DELETE FROM parent WHERE p_id = 42; DELETE 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2711 (1 row) dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val ------+------+------+------+------- (0 rows) dump=*# rollback; ROLLBACK dump=# dump=# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val -------+------+------+------+-------- (0,1) | 2710 | 2711 | 42 | parent (1 row)
注意这里的 xmax: 2711 即使delete操作被rollback了，这里的xmax仍然记录了做delete操作的transaction id
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2710 | 2711 | f | f | t | f | \x2a000000 | \x0f706172656e74
(1 row)
xmax_committed: f 表示删除操作被rollback了，数据仍然存在，可以被其他连接读取 第四个实验 #### Session 1: 做select ... for update 操作
dump=# begin; BEGIN dump=*# SELECT * FROM parent WHERE p_id = 42 FOR UPDATE; p_id | p_val ------+-------- 42 | parent (1 row) dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2712 (1 row)
#### Session 2: 可以看到 xmax_is_lock: t 表示在此行有lock了
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2710 | 2712 | t | f | f | f | \x2a000000 | \x0f706172656e74
(1 row)
dump=#
if xmax contains a row lock(xmax_is_lock: t), the row is active, no matter what the state of the locking transaction is. 第五个实验 #### Session 1
dump=# begin; BEGIN dump=*# INSERT INTO child (c_id, p_id, c_val) VALUES (1, 42, 'first session'); INSERT 0 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2713 (1 row) dump=*# commit; COMMIT
#### Session 2
dump=# begin;
BEGIN
dump=*# INSERT INTO child (c_id, p_id, c_val) VALUES (2, 42, 'second session');
INSERT 0 1
dump=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
2714
(1 row)
dump=*# commit;
COMMIT
#### Session 3
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2710 | 1 | t | f | f | t | \x2a000000 | \x0f706172656e74
(1 row)
这个地方的xmax的值变成了1, xmax_multixact变成了 t 这是因为有两个transaction（2713，2714）对parent表的这一行施加了lock，那么这里就不能在xmax只记录其中一个transaction了，因为单独记录 2713或者2714 它记录了1，那么通过下面的sql可以找到1对应的2个transaction id
dump=# SELECT * FROM pg_get_multixact_members('1');
xid | mode
------+-------
2713 | keysh
2714 | keysh
(2 rows)
dump=#
