Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Multi Version Heap Tuple Analysis in PostgreSQL". In daily operation, I believe many people have doubts about Multi Version Heap Tuple analysis in PostgreSQL. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "Multi Version Heap Tuple Analysis in PostgreSQL". Next, please follow the editor to study!

Concurrency Control concurrency control is a mechanism that maintains consistency (Consistency) and isolation (Isolation) when multiple transactions occur concurrently. Consistency and isolation are the C and I of database transaction ACID (Atomicity, Consistency, Isolation, Durability) attributes.

Multi-version concurrency control (MVCC) is a widely used concurrency control technology, and its main advantage is that read does not block write, and write does not block read. There are many variants of MVCC, and PostgreSQL uses a MVCC variant called Snapshot isolation Snapshot Isolation (SI) for concurrency control.

In MVCC, each DML operation creates a new version of the data, including Index, while retaining the previous version. When a transaction reads data, select one of the "correct" versions to ensure isolation between transactions.

Zero, hidden columns, and infomask tags

In order to better explain the storage structure of Heap Tuple, it is necessary to briefly describe the hidden columns of Tuple and related tags.

Hide column

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- data sheet OID

Cmax- deletes the transaction internal command ID of the tuple

Xmax- deletes the transaction ID of the tuple

Cmin- inserts the transaction internal command ID of the tuple

Xmin- inserts the transaction ID of the tuple

ID of ctid-heap tuple

Infomask marker

The main markers include t_infomask2 and t_infomask.

T_infomask2

The values and instructions are as follows

/ * * information stored in t_infomask2: * / # define HEAP_NATTS_MASK 0x07FF / * 11 bits for number of attributes * / the lower 11 bits are the number of attributes / * 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 * / / convert hexadecimal values to binary display 11111111111 # define HEAP_NATTS_MASK 0x07FF 10000000000000 # define HEAP_KEYS_UPDATED 0x2000 1000000000000 # define HEAP_HOT_UPDATED 0x4000 10000000000000 # define HEAP_ONLY_ TUPLE 0x8000 1110000000000000 # define HEAP2_XACT_MASK 0xE000 1111111111111110 # define SpecTokenOffsetNumber 0xfffe

T_infomask

The values and instructions are as follows

/ / t_infomask description 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 * / I, INSERT

Create a data table and insert data

Testdb=# drop table if exists tactimvcc1: drop TABLEtestdb=# create table t_mvcc1 (C1 int,c2 varchar (40)); CREATE TABLEtestdb=# testdb=# insert into t_mvcc1 values (1meme C2-1'); INSERT 0 1testdb=# insert into t_mvcc1 values (2meme C2-2'); INSERT 0 1testdb=#

View the contents of page through the pageinspect plug-in

Testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask2 testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask from heap_page_items (get_raw_page ('tweemvcc1)) Lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask-+-+- | 1 | 8152 | 1 | 2300 | 0 | 0 | (0 rows 1) | 2 | 2050 2 | 8112 | 1 | 2301 | 0 | 0 | (0 line 2) | 2 | 2050 (2)

Where lp is Line Pointer (ItemID, row pointer), t_xmin (2300-2301 respectively) is the transaction inserting data ID,t_xmax is 0 (Invalid transaction number), t_cid is the command number, t_ctid is heap tuple ID. For more information, please see Resources.

T_infomask2 is 0x0002, indicating that there are 2 fields (the lower 11 bits are the number of attributes)

T_infomask is 2050, that is, 0x0802. The tag has a variable length attribute (HEAP_HASVARWIDTH) / XMAX is invalid (HEAP_XMAX_INVALID).

II. UPDATE

Update data (commit transaction)

Testdb=# testdb=# begin;BEGINtestdb=# testdb=# update t_mvcc1 set c2 / c2 / 1 'where C1 = 1 / 1testdb=# update t_mvcc1 set / c2 / C2 / 2 / where / C1 = 2 / update 1testdb=# testdb=# commit;COMMIT

View the contents of page through the pageinspect plug-in

Testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask2 testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask from heap_page_items (get_raw_page ('tweemvcc1)) Lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask-+-+- | 1 | 8152 | 1 | 2300 | 2302 | 0 | (0Magazine 3) | 16386 | 2582 | 8112 | 1 | 2301 | 2302 | 1 | 16386 | 2583 | 8072 | 1 | 2 302 | 0 | 0 | 0 | 3 2770 | 102424 | 8032 | 1 | 2 302 | 0 | 0 | 1 | (0P4) | 32770 | 10242 (4 rows)

You can see that the original data still exists, but the txmax value is 2302, indicating that the two rows have been updated, and t_ctid points to the new heap tuple.

The t_infomask2 of tuple is 16386, that is, 0x4002-> HEAP_HOT_UPDATED

T_infomask is 258, that is, 0x0102-> HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH

The t_infomask2 of tuple No. 3 is 32770, that is, 0x8002-> HEAP_ONLY_TUPLE.

T_infomask is 10242, which means 0x2802-> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_HASVARWIDTH

Update data (roll back transactions)

Testdb=# begin;BEGINtestdb=# testdb=# update t_mvcc1 set c2 / c2 / 1 'where C1 = 1 / 1testdb=# update t_mvcc1 set / c2 / C2 / 2 / where / C1 = 2 / update 1testdb=# testdb=# rollback;ROLLBACKtestdb=# select cmin,cmax,xmin,xmax,ctid,c1,c2 from t_mvcc1 Cmin | cmax | xmin | xmax | ctid | C1 | c2-+-+-0 | 0 | 2302 | 2303 | (0 rows 3) | 1 | C2pm 11 | 1 | 2302 | (0Pen4) | 2 | C2Q 2 (2 rows)

View the contents of page through the pageinspect plug-in

Testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask2 testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask from heap_page_items (get_raw_page ('tweemvcc1)) Lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask-+-+- | 1 | 8152 | 1 | 2300 | 2302 | 0 | (0Magazine 3) | 16386 | 1282 2 | 8112 | 1 | 2301 | 2302 | (0Magin4) | 16386 | 12823 | 8072 | 1 | 2302 | 2303 | 0 | (0Magin5) | 49154,84504 | 8032 | 1 | 2302 | 2303 | 1 | (0jue 6) | 49154 | 8450 5 | 7992 | 1 | 2303 | 0 | 0 | (0 rows 5) | 32770 | 10242 6 | 7952 | 1 | 2303 | 0 | 1 | (0 Power6) | 32770 | 10242 (6 0)

(lp=3/4) tuple is updated and t_xmax is set to update the ID of the transaction, but the transaction rollback (PG records the transaction status through clog, which will be discussed later by clog).

T_infomask2=49154, or 0xC002

T_infomask=8450, i.e. 0x2102-> HEAP_UPDATED | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH

Tuple No. 5 is a newly generated update record, but transaction rollback.

T_infomask2=32770, i.e. 0x8002-> HEAP_ONLY_TUPLE

T_infomask=10242, i.e. 0x2802-> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_HASVARWIDTH

III. DELETE

Delete data (commit transaction)

Testdb=# begin;BEGINtestdb=# testdb=# delete from t_mvcc1 where C1 = 1th delete 1testdb=# testdb=# commit;COMMITtestdb=# testdb=# select cmin,cmax,xmin,xmax,ctid,c1,c2 from t_mvcc1 Cmin | cmax | xmin | xmax | ctid | C1 | c2-+-- +-1 | 1 | 2302 | 2303 | (0 row 4) | 2 | C2prof2 (1 row)

View the contents of page through the pageinspect plug-in

Testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask2 testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask from heap_page_items (get_raw_page ('tweemvcc1)) Lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask-+-+- | 1 | 8152 | 1 | 2300 | 2302 | 0 | (0Magazine 3) | 16386 | 1282 2 | 8112 | 1 | 2301 | 2302 | (0Magin4) | 16386 | 12823 | 8072 | 1 | 2302 | 2304 | 0 | (0Power3) | 40962 | 94744 | 8032 | 1 | 2302 | 2303 | 1 | (0jue 6) | 49154 | 10498 5 | 7992 | 1 | 2303 | 0 | 0 | (0 rows 5) | 32770 | 10754 6 | 7952 | 1 | 2303 | 0 | 1 | (0 dint 6) | 32 770 | 10 754 (6 0)

No. 3 (lp=3) tuple is deleted, and t_xmax is modified to 2304 (0meme3).

T_infomask2=40962, or 0xA002

T_infomask=9474, i.e. 0x2502-> HEAP_UPDATED | HEAP_XMAX_COMMITTED | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH

Delete data (rollback transaction)

Testdb=# begin;BEGINtestdb=# testdb=# delete from t_mvcc1 where C1 = 2th delete 1testdb=# testdb=# rollback;ROLLBACKtestdb=# testdb=# select cmin,cmax,xmin,xmax,ctid,c1,c2 from t_mvcc1 Cmin | cmax | xmin | xmax | ctid | C1 | c2-+-- +-0 | 0 | 2302 | 2305 | (0 row 4) | 2 | C2prof2 (1 row)

Xmax was changed to transaction number 2305 (formerly 2303).

View the contents of page through the pageinspect plug-in

Testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask2 testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as infomask from heap_page_items (get_raw_page ('tweemvcc1)) Lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask-+-+- | 1 | 8152 | 1 | 2300 | 2302 | 0 | (0Magazine 3) | 16386 | 1282 2 | 8112 | 1 | 2301 | 2302 | (0Magin4) | 16386 | 12823 | 8072 | 1 | 2302 | 2304 | 0 | (0Power3) | 40962 | 94744 | 8032 | 1 | 2302 | 2305 | 0 | (0 rows 4) | 40962 | 10498 5 | 7992 | 1 | 2303 | 0 | 0 | 0 | (0 rows 5) | 32770 | 10754 6 | 7952 | 1 | 2303 | 0 | 1 | (0 Force 6) | 32770 | 10754 (6 0)

Delete No. 4 (lp=4) tuple, but the transaction is rolled back, and the t_max is modified to 2305.

T_infomask2=40962, or 0xA002

T_infomask=10498, i.e. 0x2902-> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH

At this point, the study of "Multi Version Heap Tuple Analysis in PostgreSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.