PG vs MySQL mvcc机制实现的异同

MVCC实现方法比较

MySQL
写新数据时，把旧数据写入回滚段中，其他人读数据时，从回滚段中把旧的数据读出来

PostgreSQL
写新数据时，旧数据不删除，直接插入新数据。

MVCC实现的原理

PG的MVCC实现原理

• 定义多版本的数据——使用元组头部信息的字段来标示元组的版本号
• 定义数据的有效性、可见性、可更新性——通过当前的事务快照和对应元组的版本号判断
• 实现不同的数据库隔离级别——通过在不同时机获取快照实现

PG的数据多版本实现

pg中元组由三部分组成——元组头结点、空值位图、用户数据。没一行元组，都有一个版本号。
该版本由如下几个数据组成。

t_xmin：保存插入该元组的事务txid（该元组由哪个事务插入）
t_xmax：保存更新或删除该元组的事务txid。若该元组尚未被删除或更新，则t_xmax=0，即invalid
t_cid：保存命令标识（command id,cid），指在该事务中，执行当前命令之前还执行过几条sql命令（从0开始计算）
t_ctid：一个指针，保存指向自身或新元组的元组的标识符（tid）。

当更新该元组时，t_ctid会指向新版本元组。若元组被更新多次，则该元组会存在多个版本，各版本通过t_cid串联，形成一个版本链。通过这个版本链，可以找到最新的版本。t_ctid是一个二元组（页号,页内偏移量），其中页号从0开始，页内偏移量从1开始。

元组insert时版本号规则

postgres=# CREATE TABLE test (id int);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=*# SELECT txid_current();
 txid_current 
--------------
          778
(1 row)

postgres=*# insert into test values(1);
INSERT 0 1
postgres=*# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid FROM heap_page_items(get_raw_page('test', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid 
-------+--------+--------+-------+--------
     1 |    778 |      0 |     0 | (0,1)
(1 row)

• t_xmin 被设置为778，表示插入该元组的txid
  （当事务开始，事务管理器会为该事务分配一个txid（transaction id）作为唯一标识符。）
• t_xmax 被设置为0，因为该元组还未被更新或删除过
• t_cid 被设置为0，因为这是该事务的第一条命令
• t_ctid 指向自身，被设置为（0,1），表示该元组位于0号page的第1个位置上

元组delete时版本号规则

pg的删除只是将目标元组在逻辑上标为删除（将t_xmax设为执行delete命令的事务txid），实际该元组依然存在于数据库的存储页面，直至该元组被清理进程清理掉。

postgres=# begin;
BEGIN
postgres=*# SELECT txid_current();
 txid_current 
--------------
          779
(1 row)

postgres=*# delete from test where id=1;
DELETE 1
postgres=*# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid FROM heap_page_items(get_raw_page('test', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid 
-------+-----