Postgres MVCC机制实验

一：实验环境&摘要

操作系统：CentOS Stream 9

PostgreSQL版本：16

本实验旨在通过实机操作，体验PostgreSQL数据库的MVCC多版本并发控制机制，从而对MVCC机制理解更加深入。

二、PG MVCC理论

1. 事务并发的四类问题

在数据库事务并发中，往往会遇到以下四个问题：

脏读：事务A对表中某行a这个字段进行了修改，还未COMMIT，此时事务B进来读取此行，结果就读到了事务A未COMMIT的这个字段，如果事务A发生了ROLLBACK，那么事务B读到的就是无效的脏数据。

不可重复读：事务A在第一时间读到某行中a=20,此时事务B开始执行并修改a=45然后COMMIT，事务A还未执行完，第二次读取发现a=45，事务中前后两次读同一个值发现前后不一致，称为不可重复读。

幻读：事务A第一次查询id<50的数据，发现有30条，然后此时事务B对其中一条数据进行了删除并COMMIT，第二次查询时发现只剩下29条了，像产生了幻觉，成为幻读。

序列化异常：两个事务同时提交会产生逻辑混乱，无法模拟出一个执行完另一个再执行的情况（谁先谁后），强制其中一个事务失败。

2. 事务隔离级别

为了解决以上三个问题，数据库引入了4种标准的隔离级别：

可重复读Repeatable Read：可重复读使用一种叫快照隔离的技术实现，事务在执行第一个非事务控制的语句时会保存数据库的快照，事务执行过程中，每次SELECT读取到的数据都来自此快照，只能看到在该快照前COMMIT的数据。但是如果在该事务本身进行修改的话，改动是可见的。

在可重复读的情况下对数据进行UPDATE/DELETE之类的修改操作，对于没有事务正在进行的数据来说，行为和SELECT是一样的，能被正常执行，执行时还会加上行级排他锁，直到COMMIT/ROLLBACK才被释放；而如果对其他事务已经UPDATEed/DELETEed/LOCKed的情况，可重复读会等待这个事务COMMIT或者ROLLBACK，如果等待的事务发生ROLLBACK的话，本事务将不会受到影响并正常对进行的行操作，然而，如果等待的事务COMMIT，本事务将会发生ROLLBACK，并报序列化异常的错误，因为可重复读事务不可以在事务开始后再修改或锁定其他事务已经修改的行。

注意：PG官方文档指出，可重复读虽然能为每个事务提供一个“稳定”状态的“快照”，能让每一个事务都以看似逻辑合法的状态下执行，但是在现实中可能是非法的。比如一个事务A先查询用户id为9的用户不存在，于是决定创建这个用户，但是这个用户其实在A事务开始后就被另外一个事务创建并COMMIT了，只是事务A依然停留在它所定格那个快照中，所以事务A创建这个用户时就发生问题了！！INSERT会报主键已存在，或者其他逻辑错误。

所以，当可重复读隔离级别要对数据进行修改操作时，应该在业务逻辑上准备好重试逻辑。

**因此，**不要在可重复读的事务里面做先查后改的决定。那有没有什么办法解决呢，有的，如果需要这样做的话，在查的时候加上FOR UPDATE（排他S锁）悲观锁，告诉数据库要锁定这条数据的最新版本。

注意！！如果想判断某条数据是否在表中存在并插入（创建用户场景），使用FOR UPDATE是拿不到锁的，因为根本没有对应的行（物理元组）给你加锁啊！！（但是MySQL可以！它有GapLock间隙锁）因此，这种场景时事务会失败，我们可以使用ON CONFLICT语句来指定失败后的业务逻辑，它是原子的，安全的。或者给在对这个ID操作的事务上都加上咨询锁，以模拟间隙锁的效果。

串行化Serializable：这是最高保障数据的一致性的事务级别。其能保证事务在执行的时候，最终结果和这些序列逐个排队执行的效果完全一样。PostgreSQL的串行化其实是基于可重复读实现的。不过相比可重复读，多了一个监控机制，当监控发现两个事务的读写有交织之后（具体来说是写撞写），事务提交的时候就会拒绝晚COMMIT的那个事务。

咦？这样听起来好像和可重复读一样？其实不然，串行化和可重复读最微妙的区别在于——其解决了****写偏移的问题，欸！这是什么！这就是我们上面提到的可重复读里面很头疼的，需要加一个悲观锁才能解决的问题！

在可重复读中，如果想确保数据逻辑上正确，就必须要加悲观锁把这行给锁住，这样的话就会导致其他事务读的时候也会被阻塞！串行化则不会阻塞，PostgreSQL****写时加的是一个叫**谓词锁（SIReadLock）**的东东，这把锁会记住事务所读过的条件但不阻塞，是乐观锁，但是一旦COMMIT就会检测两个事务之间的谓词锁是否有重叠，先COMMIT的事务会成功，而后COMMIT的则会报序列化错误导致回滚，从而防止了可重复读中出现的——事务A读->事务B插入一条数据并COMMIT!->事务A根据读的修改导致的逻辑不一致问题！当然，串行隔离级别也要在业务逻辑层面上准备重试机制！

**读已提交ReadCommitted：**事务执行过程中，如果其他事务修改值并COMMIT，那么就可以读到COMMIT后的数据。与可重复读相比，读已提交实际上是在执行语句前保存了数据库的快照，并从该快照中读已COMMIT的数据。

如果其他事务没有对目标行进行修改加锁的话，UPDATE/DELETE/SELECT FOR UPDATE的行为表现和SELECT都是一样的——以语句开始执行的那一刻快照为基准，然而，当一个事务A打算修改一行已经被事务B更新或删除的行时，事务A会挂起等待B事务COMMIT或者ROLLBACK，如果事务BROLLBACK，那事务A将会继续按快照读到的那行执行并不受事务B的影响；如果事务BCOMMIT，并且是删除行的话，事务A将会忽略此行，否则，事务A的删除/修改操作将会在COMMIT后的新行进行，WHERE子句也会在更新后的行上进行条件搜索。

INSERT ON CONFLICT DO UPDATE子句在读已提交级别可以保证要么数据被插入，要么被更新，这是原子性的，如果事务B产生了一条对A不可见的冲突（A想要插入一条id=1的数据，但是B已经插入了未提交/提交），那么DO UPDATE子句会无视“快照”，直接在最新的那条数据上执行UPDATE子句。

这里按照可见性原则来说，事务A是不应该能看到B插入的数据的，但是****Postgres的MVCC机制会在触发冲突的时候，强制让UPDATE子句去修改那条不可见的隐藏数据。这是只有在读已提交才会发生的。（而不是像可重复读一样，抛出序列化错误）。

在读已提交的级别下，如果进行UPDATE操作，理论上永远都是按照语句开始前的那一份“快照”的基础上修改，然而——对于想要修改的行来说，Postgres会强制去读该行最新已提交的版本，在最新的版本上进行操作。然而这在复杂查询上会导致问题，因为复杂查询中用于判断（不需要改）的数据只会从快照中取，而需要修改的目标会强制读最新已提交数据。

用旧数据判断去修改新数据是很危险的，所以不要在读已提交的隔离级别上做复杂跨表逻辑更新。

PG文档官方的例子：

BEGIN; //ORIGINAL: hits=9,10
UPDATE website SET hits = hits + 1;//NEW: hits=10,11  NOT COMMIT
-- run from another session:  DELETE FROM website WHERE hits = 10; //WHERE按照快照查，id=9  不符合条件.id=10，准备修改！但是发现这行被其他事务修改了，PG的MVCC去尝试锁拿强制读最新值！COMMIT后 id变成了11!不符合条件，所以最后DONOTHING。
COMMIT;

**然而，对于简单的依赖主键更新数据的情况（库存，转账）来说，这反而是好事。PG在事务中更新余额时，发现这个余额已经被其他事务修改并提交了（修改未提交会挂起等待），那么更新余额的操作会在最新的值上进行，**读已提交很好地解决了并发更新逻辑。

读未提交Read Uncommitted：事务执行过程中，其他事务修改了数据，也能够被当前事务读取到。

在众多数据库中，我们一般是可以自由选择四个事务隔离级别，但是对于PostgreSQL来说，是不允许脏读这个情况出现的，读未提交和读已提交这两个隔离级别在PG中表现一样，也就是说，PG的隔离级别比标准SQL要求更高。

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read uncommitted	Allowed, but not in PG	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible	Possible
Repeatable read	Not possible	Not possible	Allowed, but not in PG	Possible
Serializable	Not possible	Not possible	Not possible	Not possible

三、MVCC原理

概述

MySQL中，InnoDB引擎采用聚簇索引保存数据，MVCC通过版本链实现，旧版本保存在UndoLog中，数据表中只保存最新版本的数据。

而PostgreSQL采用堆表储存数据，MVCC通过在每行维护额外数据来实现，当数据被修改时，新数据作为新的行（元组）插入，旧数据依然保留在表中，而这就导致了表膨胀问题，需要通过VACUUM机制定时清理表空间。

事务ID

MVCC既然是多版本控制，那得有版本号，用于判断哪个数据是新的，哪个数据是旧的吧！最直接想到的就是时间戳，因为时间不可能逆转啊！但是系统的时间可不一定哦？万一被人动了手脚，或者时间自己重置，就乱套了！所以PostgreSQL采用的是一个32位无符号自增整数作为事务标识，来判断版本的新旧程度。

postgres=#  select txid_current();
 txid_current 
--------------
          829
(1 row)

Tuple结构

接下来我们要了解元组结构。一条数据就可以看成是一个元组，元组由HeapTupleHeaderData（元组头）、NULL 值位图以及用户数据所组成。

Alt text 元组头中有4个关键的隐藏列，是MVCC用来判断可见性的依据。

t_xmin：数据被创建/插入时的事务ID。
t_xmax：数据被删除或锁定时的事务ID。
- 如果这行为0，则代表数据未被删除
- 如果这行数据是一个具体的事务ID，那么代表这个数据可能已经过期（被删除|被更新）了。
t_cid：用于表示执行当前命令时，当前事务已经执行了多少条命令，用于判断游标的数据可见性。
t_ctid：指向物理存储的指针（块号+Offset)，如果是已更新的行，指针会指向最新的那条数据版本。
**t_infomask：位掩码，保存事务的执行状态，如 **XMIN_COMMITTED、XMAX_COMMITTED 等，还有游标相关的信息。

接下来我们通过一个小小实验来理解一下这个元组头的变化吧！

我们使用一个插件叫pageinspect，用于更好地观察表元组数据的内容。

CREATE EXTENSION IF NOT EXISTS pageinspect;

首先创建一个简简单单的测试表，并插入一条数据，然后创建一个用于观察的视图

CREATE TABLE mvcc_test (a int);
CREATE VIEW t_mvcc_test AS SELECT lp AS tuple,t_xmin,t_xmax,t_field3 AS t_cid,t_ctid FROM heap_page_items(get_raw_page('mvcc_test',0)) ORDER BY tuple;

`t_xmin`插入：

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

postgres=*# INSERT INTO mvcc_test VALUES (1);
INSERT 0 1

postgres=*# SELECT * FROM t_mvcc_test ;
 tuple | t_xmin | t_xmax | t_cid | t_ctid 
-------+--------+--------+-------+--------
     1 |    843 |      0 |     0 | (0,1)
(1 row)

可以看到，当前的t_xmin就是正在执行的事务ID，t_xmax为0，代表这个数据还未被删除（有效的）。

`t_xmax`更新/删除：

postgres=# BEGIN;
BEGIN
postgres=*# UPDATE mvcc_test SET a=2;
UPDATE 1
postgres=*# SELECT * FROM t_mvcc_test ;
 tuple | t_xmin | t_xmax | t_cid | t_ctid 
-------+--------+--------+-------+--------
     1 |    843 |    844 |     0 | (0,2) --DELETE的旧数据......
     2 |    844 |      0 |     0 | (0,2) --INSERT的新数据！
(2 rows)

postgres=*# SELECT txid_current();
 txid_current 
--------------
          844
(1 row)

事实上，PG中的UPDATE其实是DELETE+INSERT的操作组合。采用UPDATE修改或者DELETE删除字段内容之后，发现表中原数据的t_xmax从0变成了操作修改的事务ID，这表明这条元组已经被逻辑删除（已过期）了，这些过期元组会在VACUUM触时中被删除。

可见性判断

在PostgreSQL中，事务有4种状态：

TRANSACTION_STATUS_IN_PROGRESS: 事务正在运行中
TRANSACTION_STATUS_COMMITTED: 事务已提交
TRANSACTION_STATUS_ABORTED: 事务已回滚
TRANSACTION_STATUS_SUB_COMMITTED: 子事务已提交

当xmin事务的状态为ABORTED:

当我们开启了一个事务获取了快照，需要对一个tuple进行可见性判断时，如果这个tuple队的xmin对应的事务为ABORTED时，即已经回滚时，那么这条数据对当前快照不可见。

当xmin事务的状态为IN_PROGRESS：

当元组在一个事务中被创建且未提交时，这个元组理论上对快照是不可见的，有个例外是这个元组是被当前事务所创建的。

当xmin事务的状态为COMMITED:

当元组创建的事务已经被提交，如果没有被删除的话（xmin==0)，且不在活跃事务列表的话，那么就是可见的。

事务快照

事务快照是一个数据集合，用于保存某个时间点所看到的特定事务状态信息，包含哪些事务已经完成，哪些事务还未开始，哪些事务正在进行，可以通过SELECT txid_current_snapshot();查看当前事务快照状态。

postgres=# SELECT txid_current_snapshot();
 txid_current_snapshot 
-----------------------
 840:849:847,848
(1 row)

这个快照状态由三部分组成：xmin:xmax:[xip_list],其中xmin表示最小的一个正在活跃的id，小于它的事务都已经提交或者回滚。（可见的）xmax表示活跃或未开始的事务ID，所有大于等于它的事务都是正在进行中（未提交）或者未开始（不可见的）。而xip_list表示还在进行中的事务列表（不可见的）。

以上面为例，当前还在活跃的最老的事务txid是840，最新的事务id是txid是849（未提交或者未开始），而847，848两个事务还正在执行当中。

三、事务隔离级别实验

表结构

              Table "public.mvcc_test"
 Column  |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 id      | integer |           | not null | 
 payload | text    |           |          |

启用pageinspect扩展：

CREATE EXTENSION IF NOT EXISTS pageinspect;

关闭自动清理：

ALTER TABLE mvcc_test SET (autovacuum_enabled = false);

可重复读

实验1：MVCC 可见性与快照机制

实验目标：验证 PG 如何通过快照（Snapshot）过滤掉“脏读”，哪怕数据已经物理落盘。

事务执行第一个非事务控制语句时会保存数据库快照，只能读取在该快照前COMMIT的数据，以及事务内部对数据的更改。

步骤	事务 A (Session A)	事务 B (Session B)	说明
1	`BEGIN ISOLATION LEVEL REPEATABLE READ;`	`BEGIN ISOLATION LEVEL REPEATABLE READ;`	开启 RR 级别事务
2		`SELECT pg_current_xact_id();` `INSERT INTO mvcc_test VALUES (1,'V1');`	B 获取 XID=771 并插入数据。此时 B 未提交。
3		`SELECT xmin, xmax, * FROM mvcc_test;`	B 视角：能看到数据。`xmin`=B_XID，`xmax`=0。
4	`SELECT xmin, * FROM mvcc_test;`		A 视角：查无数据。PG 拒绝脏读。这是事务A第一次执行的非事务控制语句，此时 A 的快照才正式固定，因此`A_XID`>`B_XID`
5	`SELECT pg_current_xact_id();`		A 获取 XID=772。
6	`SELECT lp , t_xmin, t_xmax , t_ctid, t_data FROM heap_page_items(get_raw_page('mvcc_test', 0));`		A 虽然查不到数据，但能看到磁盘上有一行`t_xmin`=B_XID 的记录。
7		`UPDATE mvcc_test SET payload='V2'...`	B 修改数据，产生新版本。
8	`SELECT * FROM mvcc_test;`		A 视角：依然查无数据。
9	`INSERT INTO mvcc_test VALUES (2,'V1');` `SELECT * FROM mvcc_test;`		A 视角：能看到自己刚插的 id=2，仍看不到 id=1。
10	`COMMIT;`	`COMMIT;`

postgres=# SELECT lp ,             
       t_xmin, 
       t_xmax , 
       t_ctid, 
       t_data
FROM heap_page_items(get_raw_page('mvcc_test', 0));
 lp | t_xmin | t_xmax | t_ctid |      t_data      
----+--------+--------+--------+------------------
  1 |    771 |    771 | (0,2)  | \x01000000075631
  2 |    771 |      0 | (0,2)  | \x01000000075632
  3 |    772 |      0 | (0,3)  | \x02000000075631
(3 rows)

实验二：写写冲突 —— 锁机制与序列化异常

实验目标：在可重复读隔离级别下，验证并发修改同一数据的不同行为差异（ROLLBACK/COMMIT）

步骤	事务 A (Session A)	事务 B (Session B)	说明
1	`BEGIN ISOLATION LEVEL REPEATABLE READ;`	`BEGIN ISOLATION LEVEL REPEATABLE READ;`	开启 RR 级别事务
2	`UPDATE mvcc_test SET payload='V3' WHERE id = 1`		没有其他事务正在操作这行，事务A进行修改并为`id=1`的行加上了行级排他锁。
3		`UPDATE mvcc_test SET payload='V3' WHERE id = 2`	没有其他事务正在操作这行，事务B进行修改并为`id=2`的行加上了行级排他锁。
4	`DELETE FROM mvcc_test WHERE id = 2`		出事了！！因为事务B已对`id=2`的行加排他锁，事务A只能等待释放。
5		`COMMIT`/`ROLLBACK`	COMMIT: 事务A报序列化错误，并`ROLLBACK` ROLLBACK: 事务A拿到了锁，成功删除行。
6	`COMMIT`

实验三：逻辑死锁与解决方案——显式锁

实验目标：在可重复读隔离级别下，尝试事务A中执行包含先查后改的业务逻辑，且另外一个事务B在A未COMMIT的时候提交了一个对查询内容修改的事务导致逻辑死锁的情况。

——在RR中执行先查后改绝对不是最佳实践。

步骤	事务 A (Session A)	事务 B (Session B)	说明
1	`BEGIN ISOLATION LEVEL REPEATABLE READ;`	`BEGIN ISOLATION LEVEL REPEATABLE READ;`	开启 RR 级别事务
2	`SELECT * FROM table WHERE id= 1`		查询`id=1`的用户不存在，准备创建。
3		`INSERT INTO mvcc_test VALUES (1,'V1');`	这时候突然闯出来一个事务B，把`id=1`的用户抢先创建了
4		`COMMIT;`	事务B提交
5	`INSERT INTO mvcc_test VALUES (1,'V1');`		出事了！！事务A的快照停留在步骤2的语句执行的开始，此时并不存在`id=1`的用户，但实际上数据已经刷写落盘，插入会发生主键冲突！！
6	`ROLLBACK`		事务只能回滚了。

为了避免这一问题，如果非要在可重复读级别进行先查后改的操作，就需要在****查询时显式加上行级排他锁，避免其他事务进行修改。

步骤	事务 A (Session A)	事务 B (Session B)	说明
1	`BEGIN ISOLATION LEVEL REPEATABLE READ;`	`BEGIN ISOLATION LEVEL REPEATABLE READ;`	开启 RR 级别事务
2	`SELECT * FROM table WHERE id= 1 FOR UPDATE`		查询到了用户1的信息，准备对其进行更新。
3		`DELETE FROM mvcc_test WHERE id = 1`	突然蹦出来个事务B想要注销`id=1`的用户，但是拿不到行级锁，被挂起了。
4	`UPDATE mvcc_test SET payload='V3' WHERE id = 1`		事务A顺利地完成了更新。
5	`COMMIT`		事务A COMMIT
6		`ROLLBACK`	事务B报错，只能回滚了。

Dango