Concurrency Control併發控制是一種機制，在併發進行多個事務時維護一致性(Consistency)和隔離性(Isolation)，一致性和隔離性是資料庫事務ACID(Atomicity, Consistency, Isolation, Durability) 屬性中的C和I。
多版本併發控制(MVCC)是廣泛使用的併發控制技術,其主要優勢是讀不會阻塞寫，而寫也不會阻塞讀。MVCC有很多種變體，PostgreSQL使用一種稱為快照隔離Snapshot Isolation (SI)的MVCC變體實現併發控制。
在MVCC中，每個DML操作建立一個資料（包括Index）的新版本，同時保留之前的舊版本。當事務讀取資料時，選擇其中一個“正確”的版本，以確保各個事務之間的隔離。
下面按DML的型別（INSERT/UPDATE/DELETE)簡單介紹PostgreSQL中資料（Heap Tuple）的多版本儲存結構。

### 零、隱藏列和infomask標記
為了更好的說明Heap Tuple的儲存結構,有必要先簡要說明Tuple的隱藏列以及相關的標記.
**隱藏列**
```
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-資料表OID
cmax-刪除該tuple的事務內部命令ID
xmax-刪除該tuple的事務ID
cmin-插入該tuple的事務內部命令ID
xmin-插入該tuple的事務ID
ctid-heap tuple的ID

**infomask標記**
主要的標記包括t_infomask2和t_infomask.
*t_infomask2*
取值和說明如下
```
/*
 * information stored in t_infomask2:
 */
#define HEAP_NATTS_MASK 0x07FF /* 11 bits for number of attributes */
//低11位為屬性個數
/* 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 */
//把十六進位制值轉換為二進位制顯示
     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*
取值和說明如下
```
//t_infomask說明
               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 */

```

### 一、INSERT
建立資料表,插入資料
```
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=#

```
透過pageinspect外掛檢視page中的內容
```

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)
```
其中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）

### 二、UPDATE
**更新資料(提交事務)**
```
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
```
透過pageinspect外掛檢視page中的內容
```
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)
```
可以看到原資料仍存在,但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;
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)
```
透過pageinspect外掛檢視page中的內容
```
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號(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

### 三、DELETE
**刪除資料(提交事務)**
```
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)
```
透過pageinspect外掛檢視page中的內容
```
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號(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;
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修改為事務號2305(原為2303).
透過pageinspect外掛檢視page中的內容
```
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號(lp=4) tuple,但事務回滾,t_max修改為2305.
t_infomask2=40962,即0xA002
t_infomask=10498,即0x2902 -> HEAP_UPDATED | HEAP_XMAX_INVALID | HEAP_XMIN_COMMITTED | HEAP_HASVARWIDTH

### 四、參考資料
[Concurrency Control](http://www.interdb.jp/pg/pgsql05.html)
[Understanding System Columns in PostgreSQL](https://severalnines.com/blog/understanding-system-columns-postgresql)

PostgreSQL DBA(21) - MVCC#1(Multi Version Heap Tuple)

相關文章