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
Understanding System Columns in PostgreSQL
