溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“PostgreSQL中的Multi Version Heap Tuple分析”，在日常操作中，相信很多人在PostgreSQL中的Multi Version Heap Tuple分析問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”PostgreSQL中的Multi Version Heap Tuple分析”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！
Concurrency Control并發(fā)控制是一種機(jī)制，在并發(fā)進(jìn)行多個(gè)事務(wù)時(shí)維護(hù)一致性(Consistency)和隔離性(Isolation)，一致性和隔離性是數(shù)據(jù)庫(kù)事務(wù)ACID(Atomicity, Consistency, Isolation, Durability) 屬性中的C和I。
多版本并發(fā)控制(MVCC)是廣泛使用的并發(fā)控制技術(shù),其主要優(yōu)勢(shì)是讀不會(huì)阻塞寫，而寫也不會(huì)阻塞讀。MVCC有很多種變體，PostgreSQL使用一種稱為快照隔離Snapshot Isolation (SI)的MVCC變體實(shí)現(xiàn)并發(fā)控制。
在MVCC中，每個(gè)DML操作創(chuàng)建一個(gè)數(shù)據(jù)（包括Index）的新版本，同時(shí)保留之前的舊版本。當(dāng)事務(wù)讀取數(shù)據(jù)時(shí)，選擇其中一個(gè)“正確”的版本，以確保各個(gè)事務(wù)之間的隔離。
為了更好的說(shuō)明Heap Tuple的存儲(chǔ)結(jié)構(gòu),有必要先簡(jiǎn)要說(shuō)明Tuple的隱藏列以及相關(guān)的標(biāo)記.
隱藏列
testdb=# select attname, attnum, atttypid::regtype, attisdropped::text from pg_attribute where attrelid=34374; attname | attnum | atttypid | attisdropped ----------+--------+-------------------+-------------- tableoid | -7 | oid | false cmax | -6 | cid | false xmax | -5 | xid | false cmin | -4 | cid | false xmin | -3 | xid | false ctid | -1 | tid | false c1 | 1 | integer | false c2 | 2 | character varying | false c3 | 3 | character varying | false (9 rows)
tableoid-數(shù)據(jù)表OID
cmax-刪除該tuple的事務(wù)內(nèi)部命令I(lǐng)D
xmax-刪除該tuple的事務(wù)ID
cmin-插入該tuple的事務(wù)內(nèi)部命令I(lǐng)D
xmin-插入該tuple的事務(wù)ID
ctid-heap tuple的ID
infomask標(biāo)記
主要的標(biāo)記包括t_infomask2和t_infomask.
t_infomask2
取值和說(shuō)明如下
/* * information stored in t_infomask2: */ #define HEAP_NATTS_MASK 0x07FF /* 11 bits for number of attributes */ //低11位為屬性個(gè)數(shù) /* bits 0x1800 are available */ #define HEAP_KEYS_UPDATED 0x2000 /* tuple was updated and key cols * modified, or tuple deleted */ #define HEAP_HOT_UPDATED 0x4000 /* tuple was HOT-updated */ #define HEAP_ONLY_TUPLE 0x8000 /* this is heap-only tuple */ #define HEAP2_XACT_MASK 0xE000 /* visibility-related bits */ //把十六進(jìn)制值轉(zhuǎn)換為二進(jìn)制顯示 11111111111 #define HEAP_NATTS_MASK 0x07FF 10000000000000 #define HEAP_KEYS_UPDATED 0x2000 100000000000000 #define HEAP_HOT_UPDATED 0x4000 1000000000000000 #define HEAP_ONLY_TUPLE 0x8000 1110000000000000 #define HEAP2_XACT_MASK 0xE000 1111111111111110 #define SpecTokenOffsetNumber 0xfffe
t_infomask
取值和說(shuō)明如下
//t_infomask說(shuō)明 1 #define HEAP_HASNULL 0x0001 /* has null attribute(s) */ 10 #define HEAP_HASVARWIDTH 0x0002 /* has variable-width attribute(s) */ 100 #define HEAP_HASEXTERNAL 0x0004 /* has external stored attribute(s) */ 1000 #define HEAP_HASOID 0x0008 /* has an object-id field */ 10000 #define HEAP_XMAX_KEYSHR_LOCK 0x0010 /* xmax is a key-shared locker */ 100000 #define HEAP_COMBOCID 0x0020 /* t_cid is a combo cid */ 1000000 #define HEAP_XMAX_EXCL_LOCK 0x0040 /* xmax is exclusive locker */ 10000000 #define HEAP_XMAX_LOCK_ONLY 0x0080 /* xmax, if valid, is only a locker */ /* xmax is a shared locker */ #define HEAP_XMAX_SHR_LOCK (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK) #define HEAP_LOCK_MASK (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \ HEAP_XMAX_KEYSHR_LOCK) 100000000 #define HEAP_XMIN_COMMITTED 0x0100 /* t_xmin committed */ 1000000000 #define HEAP_XMIN_INVALID 0x0200 /* t_xmin invalid/aborted */ #define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID) 10000000000 #define HEAP_XMAX_COMMITTED 0x0400 /* t_xmax committed */ 100000000000 #define HEAP_XMAX_INVALID 0x0800 /* t_xmax invalid/aborted */ 1000000000000 #define HEAP_XMAX_IS_MULTI 0x1000 /* t_xmax is a MultiXactId */ 10000000000000 #define HEAP_UPDATED 0x2000 /* this is UPDATEd version of row */ 100000000000000 #define HEAP_MOVED_OFF 0x4000 /* moved to another place by pre-9.0 * VACUUM FULL; kept for binary * upgrade support */ 1000000000000000 #define HEAP_MOVED_IN 0x8000 /* moved from another place by pre-9.0 * VACUUM FULL; kept for binary * upgrade support */ #define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN) 1111111111110000 #define HEAP_XACT_MASK 0xFFF0 /* visibility-related bits */
創(chuàng)建數(shù)據(jù)表,插入數(shù)據(jù)
testdb=# drop table if exists t_mvcc1; DROP TABLE testdb=# create table t_mvcc1 (c1 int,c2 varchar(40)); CREATE TABLE testdb=# testdb=# insert into t_mvcc1 values(1,'C2-1'); INSERT 0 1 testdb=# insert into t_mvcc1 values(2,'C2-2'); INSERT 0 1 testdb=#
通過(guò)pageinspect插件查看page中的內(nèi)容
testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_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 | 2050
2 | 8112 | 1 | 2301 | 0 | 0 | (0,2) | 2 | 2050
(2 rows)其中l(wèi)p為L(zhǎng)ine Pointer(ItemID,行指針),t_xmin(分別是2300&2301)為插入數(shù)據(jù)的事務(wù)ID,t_xmax為0(Invalid事務(wù)號(hào)),t_cid是命令編號(hào),t_ctid是heap tuple ID,詳細(xì)解釋請(qǐng)參見參考資料.
t_infomask2為0x0002,說(shuō)明有2個(gè)字段(低11位為屬性的個(gè)數(shù));
t_infomask為2050,即0x0802,標(biāo)記存在可變長(zhǎng)屬性（HEAP_HASVARWIDTH）/XMAX無(wú)效（HEAP_XMAX_INVALID）
更新數(shù)據(jù)(提交事務(wù))
testdb=# testdb=# begin; BEGIN testdb=# testdb=# update t_mvcc1 set c2='C2#1' where c1 = 1; UPDATE 1 testdb=# update t_mvcc1 set c2='C2#2' where c1 = 2; UPDATE 1 testdb=# testdb=# commit; COMMIT
通過(guò)pageinspect插件查看page中的內(nèi)容
testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_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 | 258
2 | 8112 | 1 | 2301 | 2302 | 1 | (0,4) | 16386 | 258
3 | 8072 | 1 | 2302 | 0 | 0 | (0,3) | 32770 | 10242
4 | 8032 | 1 | 2302 | 0 | 1 | (0,4) | 32770 | 10242
(4 rows)可以看到原數(shù)據(jù)仍存在,但t_xmax值為2302,表示這兩行已被更新,同時(shí)t_ctid指向新的heap tuple.
1/2號(hào)tuple的t_infomask2是16386即0x4002 -> HEAP_HOT_UPDATED
t_infomask是258,即0x0102 -> HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH
3/4號(hào)tuple的t_infomask2是32770,即0x8002 -> HEAP_ONLY_TUPLE
t_infomask是10242,即0x2802 -> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_HASVARWIDTH
更新數(shù)據(jù)(回滾事務(wù))
testdb=# begin; BEGIN testdb=# testdb=# update t_mvcc1 set c2='C2_1' where c1 = 1; UPDATE 1 testdb=# update t_mvcc1 set c2='C2_2' where c1 = 2; UPDATE 1 testdb=# testdb=# rollback; ROLLBACK testdb=# select cmin,cmax,xmin,xmax,ctid,c1,c2 from t_mvcc1; cmin | cmax | xmin | xmax | ctid | c1 | c2 ------+------+------+------+-------+----+------ 0 | 0 | 2302 | 2303 | (0,3) | 1 | C2#1 1 | 1 | 2302 | 2303 | (0,4) | 2 | C2#2 (2 rows)
通過(guò)pageinspect插件查看page中的內(nèi)容
testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_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 | 1282
2 | 8112 | 1 | 2301 | 2302 | 1 | (0,4) | 16386 | 1282
3 | 8072 | 1 | 2302 | 2303 | 0 | (0,5) | 49154 | 8450
4 | 8032 | 1 | 2302 | 2303 | 1 | (0,6) | 49154 | 8450
5 | 7992 | 1 | 2303 | 0 | 0 | (0,5) | 32770 | 10242
6 | 7952 | 1 | 2303 | 0 | 1 | (0,6) | 32770 | 10242
(6 rows)3/4號(hào)(lp=3/4)tuple被更新,t_xmax設(shè)置為更新事務(wù)的ID,但事務(wù)rollback(PG通過(guò)clog記錄事務(wù)狀態(tài),clog后續(xù)再行討論).
t_infomask2=49154,即0xC002
t_infomask=8450,即0x2102 -> HEAP_UPDATED | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH
5/6號(hào)tuple是新生成的更新記錄,但事務(wù)rollback.
t_infomask2=32770,即0x8002 -> HEAP_ONLY_TUPLE
t_infomask=10242,即0x2802 -> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_HASVARWIDTH
刪除數(shù)據(jù)(提交事務(wù))
testdb=# begin; BEGIN testdb=# testdb=# delete from t_mvcc1 where c1 = 1; DELETE 1 testdb=# testdb=# commit; COMMIT testdb=# testdb=# select cmin,cmax,xmin,xmax,ctid,c1,c2 from t_mvcc1; cmin | cmax | xmin | xmax | ctid | c1 | c2 ------+------+------+------+-------+----+------ 1 | 1 | 2302 | 2303 | (0,4) | 2 | C2#2 (1 row)
通過(guò)pageinspect插件查看page中的內(nèi)容
testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_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 | 1282
2 | 8112 | 1 | 2301 | 2302 | 1 | (0,4) | 16386 | 1282
3 | 8072 | 1 | 2302 | 2304 | 0 | (0,3) | 40962 | 9474
4 | 8032 | 1 | 2302 | 2303 | 1 | (0,6) | 49154 | 10498
5 | 7992 | 1 | 2303 | 0 | 0 | (0,5) | 32770 | 10754
6 | 7952 | 1 | 2303 | 0 | 1 | (0,6) | 32770 | 10754
(6 rows)3號(hào)(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
刪除數(shù)據(jù)(回滾事務(wù))
testdb=# begin; BEGIN testdb=# testdb=# delete from t_mvcc1 where c1 = 2; DELETE 1 testdb=# testdb=# rollback; ROLLBACK testdb=# testdb=# select cmin,cmax,xmin,xmax,ctid,c1,c2 from t_mvcc1; cmin | cmax | xmin | xmax | ctid | c1 | c2 ------+------+------+------+-------+----+------ 0 | 0 | 2302 | 2305 | (0,4) | 2 | C2#2 (1 row)
xmax修改為事務(wù)號(hào)2305(原為2303).
通過(guò)pageinspect插件查看page中的內(nèi)容
testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as t_cid,t_ctid,t_infomask2,t_infomask from heap_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 | 1282
2 | 8112 | 1 | 2301 | 2302 | 1 | (0,4) | 16386 | 1282
3 | 8072 | 1 | 2302 | 2304 | 0 | (0,3) | 40962 | 9474
4 | 8032 | 1 | 2302 | 2305 | 0 | (0,4) | 40962 | 10498
5 | 7992 | 1 | 2303 | 0 | 0 | (0,5) | 32770 | 10754
6 | 7952 | 1 | 2303 | 0 | 1 | (0,6) | 32770 | 10754
(6 rows)刪除4號(hào)(lp=4) tuple,但事務(wù)回滾,t_max修改為2305.
t_infomask2=40962,即0xA002
t_infomask=10498,即0x2902 -> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH
到此，關(guān)于“PostgreSQL中的Multi Version Heap Tuple分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼