这篇文章主要介绍“PostgreSQL中的Multi Version Heap Tuple分析”，在日常操作中，相信很多人在PostgreSQL中的Multi Version Heap Tuple分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中的Multi Version Heap Tuple分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

Concurrency Control并发控制是一种机制，在并发进行多个事务时维护一致性(Consistency)和隔离性(Isolation)，一致性和隔离性是数据库事务ACID(Atomicity, Consistency, Isolation, Durability) 属性中的C和I。
多版本并发控制(MVCC)是广泛使用的并发控制技术,其主要优势是读不会阻塞写，而写也不会阻塞读。MVCC有很多种变体，PostgreSQL使用一种称为快照隔离Snapshot Isolation (SI)的MVCC变体实现并发控制。
在MVCC中，每个DML操作创建一个数据（包括Index）的新版本，同时保留之前的旧版本。当事务读取数据时，选择其中一个“正确”的版本，以确保各个事务之间的隔离。

为了更好的说明Heap Tuple的存储结构,有必要先简要说明Tuple的隐藏列以及相关的标记.
隐藏列

testdb=#selectattname,attnum,atttypid::regtype,attisdropped::textfrompg_attributewhereattrelid=34374;attname|attnum|atttypid|attisdropped----------+--------+-------------------+--------------tableoid|-7|oid|falsecmax|-6|cid|falsexmax|-5|xid|falsecmin|-4|cid|falsexmin|-3|xid|falsectid|-1|tid|falsec1|1|integer|falsec2|2|charactervarying|falsec3|3|charactervarying|false(9rows)

tableoid-数据表OID
cmax-删除该tuple的事务内部命令ID
xmax-删除该tuple的事务ID
cmin-插入该tuple的事务内部命令ID
xmin-插入该tuple的事务ID
ctid-heap tuple的ID

infomask标记
主要的标记包括t_infomask2和t_infomask.
t_infomask2
取值和说明如下

/**informationstoredint_infomask2:*/#defineHEAP_NATTS_MASK0x07FF/*11bitsfornumberofattributes*///低11位为属性个数/*bits0x1800areavailable*/#defineHEAP_KEYS_UPDATED0x2000/*tuplewasupdatedandkeycols*modified,ortupledeleted*/#defineHEAP_HOT_UPDATED0x4000/*tuplewasHOT-updated*/#defineHEAP_ONLY_TUPLE0x8000/*thisisheap-onlytuple*/#defineHEAP2_XACT_MASK0xE000/*visibility-relatedbits*///把十六进制值转换为二进制显示11111111111#defineHEAP_NATTS_MASK0x07FF10000000000000#defineHEAP_KEYS_UPDATED0x2000100000000000000#defineHEAP_HOT_UPDATED0x40001000000000000000#defineHEAP_ONLY_TUPLE0x80001110000000000000#defineHEAP2_XACT_MASK0xE0001111111111111110#defineSpecTokenOffsetNumber0xfffe

t_infomask
取值和说明如下

//t_infomask说明1#defineHEAP_HASNULL0x0001/*hasnullattribute(s)*/10#defineHEAP_HASVARWIDTH0x0002/*hasvariable-widthattribute(s)*/100#defineHEAP_HASEXTERNAL0x0004/*hasexternalstoredattribute(s)*/1000#defineHEAP_HASOID0x0008/*hasanobject-idfield*/10000#defineHEAP_XMAX_KEYSHR_LOCK0x0010/*xmaxisakey-sharedlocker*/100000#defineHEAP_COMBOCID0x0020/*t_cidisacombocid*/1000000#defineHEAP_XMAX_EXCL_LOCK0x0040/*xmaxisexclusivelocker*/10000000#defineHEAP_XMAX_LOCK_ONLY0x0080/*xmax,ifvalid,isonlyalocker*//*xmaxisasharedlocker*/#defineHEAP_XMAX_SHR_LOCK(HEAP_XMAX_EXCL_LOCK|HEAP_XMAX_KEYSHR_LOCK)#defineHEAP_LOCK_MASK(HEAP_XMAX_SHR_LOCK|HEAP_XMAX_EXCL_LOCK|\HEAP_XMAX_KEYSHR_LOCK)100000000#defineHEAP_XMIN_COMMITTED0x0100/*t_xmincommitted*/1000000000#defineHEAP_XMIN_INVALID0x0200/*t_xmininvalid/aborted*/#defineHEAP_XMIN_FROZEN(HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)10000000000#defineHEAP_XMAX_COMMITTED0x0400/*t_xmaxcommitted*/100000000000#defineHEAP_XMAX_INVALID0x0800/*t_xmaxinvalid/aborted*/1000000000000#defineHEAP_XMAX_IS_MULTI0x1000/*t_xmaxisaMultiXactId*/10000000000000#defineHEAP_UPDATED0x2000/*thisisUPDATEdversionofrow*/100000000000000#defineHEAP_MOVED_OFF0x4000/*movedtoanotherplacebypre-9.0*VACUUMFULL;keptforbinary*upgradesupport*/1000000000000000#defineHEAP_MOVED_IN0x8000/*movedfromanotherplacebypre-9.0*VACUUMFULL;keptforbinary*upgradesupport*/#defineHEAP_MOVED(HEAP_MOVED_OFF|HEAP_MOVED_IN)1111111111110000#defineHEAP_XACT_MASK0xFFF0/*visibility-relatedbits*/一、INSERT

创建数据表,插入数据

testdb=#droptableifexistst_mvcc1;DROPTABLEtestdb=#createtablet_mvcc1(c1int,c2varchar(40));CREATETABLEtestdb=#testdb=#insertintot_mvcc1values(1,'C2-1');INSERT01testdb=#insertintot_mvcc1values(2,'C2-2');INSERT01testdb=#

通过pageinspect插件查看page中的内容

testdb=#selectlp,lp_off,lp_flags,t_xmin,t_xmax,t_field3ast_cid,t_ctid,t_infomask2,t_infomaskfromheap_page_items(get_raw_page('t_mvcc1',0));lp|lp_off|lp_flags|t_xmin|t_xmax|t_cid|t_ctid|t_infomask2|t_infomask----+--------+----------+--------+--------+-------+--------+-------------+------------1|8152|1|2300|0|0|(0,1)|2|20502|8112|1|2301|0|0|(0,2)|2|2050(2rows)

其中lp为Line Pointer(ItemID,行指针),t_xmin(分别是2300&2301)为插入数据的事务ID,t_xmax为0(Invalid事务号),t_cid是命令编号,t_ctid是heap tuple ID,详细解释请参见参考资料.
t_infomask2为0x0002,说明有2个字段(低11位为属性的个数);
t_infomask为2050,即0x0802,标记存在可变长属性（HEAP_HASVARWIDTH）/XMAX无效（HEAP_XMAX_INVALID）

更新数据(提交事务)

testdb=#testdb=#begin;BEGINtestdb=#testdb=#updatet_mvcc1setc2='C2#1'wherec1=1;UPDATE1testdb=#updatet_mvcc1setc2='C2#2'wherec1=2;UPDATE1testdb=#testdb=#commit;COMMIT

通过pageinspect插件查看page中的内容

testdb=#selectlp,lp_off,lp_flags,t_xmin,t_xmax,t_field3ast_cid,t_ctid,t_infomask2,t_infomaskfromheap_page_items(get_raw_page('t_mvcc1',0));lp|lp_off|lp_flags|t_xmin|t_xmax|t_cid|t_ctid|t_infomask2|t_infomask----+--------+----------+--------+--------+-------+--------+-------------+------------1|8152|1|2300|2302|0|(0,3)|16386|2582|8112|1|2301|2302|1|(0,4)|16386|2583|8072|1|2302|0|0|(0,3)|32770|102424|8032|1|2302|0|1|(0,4)|32770|10242(4rows)

可以看到原数据仍存在,但t_xmax值为2302,表示这两行已被更新,同时t_ctid指向新的heap tuple.
1/2号tuple的t_infomask2是16386即0x4002 -> HEAP_HOT_UPDATED
t_infomask是258,即0x0102 -> HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH

3/4号tuple的t_infomask2是32770,即0x8002 -> HEAP_ONLY_TUPLE
t_infomask是10242,即0x2802 -> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_HASVARWIDTH

更新数据(回滚事务)

testdb=#begin;BEGINtestdb=#testdb=#updatet_mvcc1setc2='C2_1'wherec1=1;UPDATE1testdb=#updatet_mvcc1setc2='C2_2'wherec1=2;UPDATE1testdb=#testdb=#rollback;ROLLBACKtestdb=#selectcmin,cmax,xmin,xmax,ctid,c1,c2fromt_mvcc1;cmin|cmax|xmin|xmax|ctid|c1|c2------+------+------+------+-------+----+------0|0|2302|2303|(0,3)|1|C2#11|1|2302|2303|(0,4)|2|C2#2(2rows)

通过pageinspect插件查看page中的内容

testdb=#selectlp,lp_off,lp_flags,t_xmin,t_xmax,t_field3ast_cid,t_ctid,t_infomask2,t_infomaskfromheap_page_items(get_raw_page('t_mvcc1',0));lp|lp_off|lp_flags|t_xmin|t_xmax|t_cid|t_ctid|t_infomask2|t_infomask----+--------+----------+--------+--------+-------+--------+-------------+------------1|8152|1|2300|2302|0|(0,3)|16386|12822|8112|1|2301|2302|1|(0,4)|16386|12823|8072|1|2302|2303|0|(0,5)|49154|84504|8032|1|2302|2303|1|(0,6)|49154|84505|7992|1|2303|0|0|(0,5)|32770|102426|7952|1|2303|0|1|(0,6)|32770|10242(6rows)

3/4号(lp=3/4)tuple被更新,t_xmax设置为更新事务的ID,但事务rollback(PG通过clog记录事务状态,clog后续再行讨论).
t_infomask2=49154,即0xC002
t_infomask=8450,即0x2102 -> HEAP_UPDATED | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH

5/6号tuple是新生成的更新记录,但事务rollback.
t_infomask2=32770,即0x8002 -> HEAP_ONLY_TUPLE
t_infomask=10242,即0x2802 -> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_HASVARWIDTH

删除数据(提交事务)

testdb=#begin;BEGINtestdb=#testdb=#deletefromt_mvcc1wherec1=1;DELETE1testdb=#testdb=#commit;COMMITtestdb=#testdb=#selectcmin,cmax,xmin,xmax,ctid,c1,c2fromt_mvcc1;cmin|cmax|xmin|xmax|ctid|c1|c2------+------+------+------+-------+----+------1|1|2302|2303|(0,4)|2|C2#2(1row)

通过pageinspect插件查看page中的内容

testdb=#selectlp,lp_off,lp_flags,t_xmin,t_xmax,t_field3ast_cid,t_ctid,t_infomask2,t_infomaskfromheap_page_items(get_raw_page('t_mvcc1',0));lp|lp_off|lp_flags|t_xmin|t_xmax|t_cid|t_ctid|t_infomask2|t_infomask----+--------+----------+--------+--------+-------+--------+-------------+------------1|8152|1|2300|2302|0|(0,3)|16386|12822|8112|1|2301|2302|1|(0,4)|16386|12823|8072|1|2302|2304|0|(0,3)|40962|94744|8032|1|2302|2303|1|(0,6)|49154|104985|7992|1|2303|0|0|(0,5)|32770|107546|7952|1|2303|0|1|(0,6)|32770|10754(6rows)

3号(lp=3) tuple被删除,t_xmax修改为2304,t_ctid修改为(0,3).
t_infomask2=40962,即0xA002
t_infomask=9474,即0x2502 -> HEAP_UPDATED | HEAP_XMAX_COMMITTED | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH

删除数据(回滚事务)

testdb=#begin;BEGINtestdb=#testdb=#deletefromt_mvcc1wherec1=2;DELETE1testdb=#testdb=#rollback;ROLLBACKtestdb=#testdb=#selectcmin,cmax,xmin,xmax,ctid,c1,c2fromt_mvcc1;cmin|cmax|xmin|xmax|ctid|c1|c2------+------+------+------+-------+----+------0|0|2302|2305|(0,4)|2|C2#2(1row)

xmax修改为事务号2305(原为2303).
通过pageinspect插件查看page中的内容

testdb=#selectlp,lp_off,lp_flags,t_xmin,t_xmax,t_field3ast_cid,t_ctid,t_infomask2,t_infomaskfromheap_page_items(get_raw_page('t_mvcc1',0));lp|lp_off|lp_flags|t_xmin|t_xmax|t_cid|t_ctid|t_infomask2|t_infomask----+--------+----------+--------+--------+-------+--------+-------------+------------1|8152|1|2300|2302|0|(0,3)|16386|12822|8112|1|2301|2302|1|(0,4)|16386|12823|8072|1|2302|2304|0|(0,3)|40962|94744|8032|1|2302|2305|0|(0,4)|40962|104985|7992|1|2303|0|0|(0,5)|32770|107546|7952|1|2303|0|1|(0,6)|32770|10754(6rows)

删除4号(lp=4) tuple,但事务回滚,t_max修改为2305.
t_infomask2=40962,即0xA002
t_infomask=10498,即0x2902 -> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH

到此，关于“PostgreSQL中的Multi Version Heap Tuple分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！