RR级别下的幻读问题

MYSQL[8.0]

--- 事务A

show variables  like 'transaction_isolation';
[
    (transaction_isolation,REPEATABLE-READ)
]
begin ;
select * from menu where id>3;
[
  1.  结果是空
]

select * from menu where id>3;

[
  3.  结果是空
]
update menu set name ='测试幻读' where id>3;
[
  4.  1 row affected in 58 ms
]
select * from menu where id>3;
[
  5.  (4,测试幻读,"",0,"")
]
commit ;

----事务B
begin ;
select * from menu where id>3;
insert into menu(id, name, avatar, id_delete, asName) VALUE (4,'test','',0,'');
[
  2.  插入新数据
]
commit ;

按照上面的序号执行；

事务A 查询 >3 结果不存在
事务 B 插入一条符合 >3 的新数据
事务A再次查询>3的数据，结果还是不存在，此刻符合RR隔离级别下的可重复读(多次读取结果相同).
事务A更新>3的数据，显示的结果是1 row affected in 58 ms一行受到影响，说明更新操作使用的是真实数据
事务A更新后查询[此时A没有提交事务]>3的数据，显示结果 (4,测试幻读,"",0,"")查询到了事务B提交的数据，出现幻读。

PGSQL

---事务A
show transaction_isolation;
[
    (read committed)
]
BEGIN;
set transaction_isolation = 'repeatable read';
select * from error_wt_msg where org_id in ('1','2');

[
 1.   结果：(1,1,1)
]

select * from error_wt_msg where org_id in ('1','2');
[
 4.   结果：(1,1,1)
]

update error_wt_msg set bpartner_id='2' where org_id in ('1','2');

[
 5.   结果：(1 row affected in 10 ms)
]

select * from error_wt_msg;
[
 6.   结果：(1,1,1)
]

commit ;

---事务B
BEGIN TRANSACTION;

select * from error_wt_msg;

[
 2.   结果：(1,1,1)
]
insert into error_wt_msg values ('2','1','1');
[
 3. 插入数据
]
commit ;

按照上面的序号执行；

事务A查询(1,2)结果中只有1的数据
事务B查询所有数据也只有1的数据
事务B插入一条符合2的新数据
事务A再次查询(1,2)的数据，结果还是1的数据，此刻符合RR隔离级别下的可重复读(多次读取结果相同).
事务A更新(1,2)的数据，显示的结果是1 row affected in 58 ms一行受到影响，说明更新操作并未更新到事务B提交的数据
事务A更新后查询(1,2)的数据，结果中只有1的数据，没有出现幻读。

PGSQL的并发控制

每当事务开始时，事务管理器就会为其分配一个称为事务标识（transaction id, txid）的唯一标识符。

一个transaction snapshot将事务划分为过去的、当前的、未来的三个范围区域。

PostgreSQL中事务快照的状态信息包括如下内容：

testdb=# SELECT txid_current_snapshot();
 txid_current_snapshot 
-----------------------
 100:104:100,102
(1 row)

txid_current_snapshot的文本表示是xmin:xmax:xip_list，各部分描述如下。

xmin

最早仍然活跃的事务的txid。所有比它更早的事务txid < xmin要么已经提交并可见，要么已经回滚并生成死元组。
xmax

第一个尚未分配的txid。所有txid ≥ xmax的事务在获取快照时尚未启动，因而其结果对当前事务不可见。
xip_list

获取快照时活跃事务的txid列表。该列表仅包括xmin与xmax之间的txid。

例如，在快照100:104:100,102中，xmin是100，xmax是104，而xip_list为100,102。

样例

第一个例子是100:100:，如图图(a)所示，此快照表示：

因为xmin为100，因此txid < 100的事务是非活跃的
因为xmax为100，因此txid ≥ 100的事务是活跃的

第二个例子是100:104:100,102，如图(b)所示，此快照表示：

txid < 100的事务不活跃。
txid ≥ 104的事务是活跃的。
txid等于100和102的事务是活跃的，因为它们在xip_list中，而txid等于101和103的事务不活跃。

事务快照是由事务管理器提供的。在READ COMMITTED隔离级别，事务在执行每条SQL时都会获取快照；其他情况下（REPEATABLE READ或SERIALIZABLE隔离级别），事务只会在执行第一条SQL命令时获取一次快照。

可见性检查

            /* 创建元组的事务已经中止 */
Rule 1:     IF t_xmin status is ABORTED THEN
                RETURN Invisible
            END IF
 ---
            /* 创建元组的事务正在进行中 */
            IF t_xmin status is IN_PROGRESS THEN
                /* 当前事务自己创建了本元组 */
                IF t_xmin = current_txid THEN
                    /* 该元组没有被标记删除，则应当看见本事务自己创建的元组 */
Rule 2:             IF t_xmax = INVALID THEN 
                        RETURN Visible /* 例外，被自己创建的未删元组可见 */
Rule 3:             ELSE  
                    /* 这条元组被当前事务自己创建后又删除掉了，故不可见 */
                        RETURN Invisible
                    END IF
Rule 4:         ELSE   /* t_xmin ≠ current_txid */
                    /* 其他运行中的事务创建了本元组 */
                    RETURN Invisible
                END IF
            END IF
 ---
             /* 创建元组的事务已经提交 */
            IF t_xmin status is COMMITTED THEN
                /* 创建元组的事务在获取的事务快照中处于活跃状态，创建无效，不可见 */
Rule 5:         IF t_xmin is active in the obtained transaction snapshot THEN
                      RETURN Invisible
                /* 元组被删除，但删除元组的事务中止了，删除无效，可见 */
                /* 创建元组的事务已提交，且非活跃，元组也没有被标记为删除，则可见 */
Rule 6:         ELSE IF t_xmax = INVALID OR status of t_xmax is ABORTED THEN
                      RETURN Visible
                /* 元组被删除，但删除元组的事务正在进行中，分情况 */
                ELSE IF t_xmax status is IN_PROGRESS THEN
                    /* 如果恰好是被本事务自己删除的，删除有效，不可见 */
Rule 7:             IF t_xmax =  current_txid THEN
                        RETURN Invisible
                    /* 如果是被其他事务删除的，删除无效，可见 */
Rule 8:             ELSE  /* t_xmax ≠ current_txid */
                        RETURN Visible
                    END IF
                /* 元组被删除，且删除元组的事务已经提交 */
                ELSE IF t_xmax status is COMMITTED THEN
                    /* 删除元组的事务在获取的事务快照中处于活跃状态，删除无效，不可见 */
Rule 9:             IF t_xmax is active in the obtained transaction snapshot THEN
                        RETURN Visible
Rule 10:            ELSE /* 删除有效，可见 */
                        RETURN Invisible
                    END IF
                 END IF
            END IF

规则6是显而易见的，因为t_xmax为INVALID，或者t_xmax对应事务已经中止，相应元组可见。三个例外条件及规则8与规则9的描述如下。

第一个例外情况是t_xmin在获取的事务快照中处于活跃状态（规则5）。在这种情况下，这条元组是不可见的，因为t_xmin应该被视为正在进行中（取快照时创建该元组的事务尚未提交，因此对于REPEATABLE READ以及更高隔离等级而言，即使在判断时创建该元组的事务已经提交，但其结果仍然不可见）。

第二个例外情况是t_xmax是当前的txid（规则7）。这种情况与规则3类似，此元组是不可见的，因为它已经被此事务本身更新或删除。

相反，如果t_xmax的状态是IN_PROGRESS并且t_xmax不是当前的txid（规则8），则元组是可见的，因为它尚未被删除（因为删除该元组的事务尚未提交）。

第三个例外情况是t_xmax的状态为COMMITTED，且t_xmax在获取的事务快照中是非活跃的（规则10）。在这种情况下该元组不可见，因为它已被另一个事务更新或删除。

相反，如果t_xmax的状态为COMMITTED，但t_xmax在获取的事务快照中处于活跃状态（规则9），则元组可见，因为t_xmax对应的事务应被视为正在进行中，删除尚未提交生效。

Mysql版本控制

当前读和快照读

当前读读指取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读是不加锁的非阻塞读，基于 MVCC 实现，目的是提高并发性能的。快照读读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

undo日志

insert undo log

代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃。

update undo log

事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除。

对 MVCC 有帮助的实质是 update undo log ，undo log 实际上就是存在 rollback segment 中的旧记录链。

MVCC原理

隐藏字段

MySQL中，在每一行记录中除了数据字段，还有一些隐藏字段：

DB_ROW_ID：单调递增的行 ID，没定义主键时，InnoDB会以row_id为主键生成一个聚集索引。
DB_TRX_ID：事务ID：记录了新增/最近修改这条记录的事务id，事务id是自增的。
DB_ROLL_PTR：回滚指针：指向当前记录的上一个版本（在 undo log 中）。

版本链

在修改数据的时候，会向 undo log 记录数据原来的快照，用于回滚事务。
多个事务操作同一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指DB_ROLL_PTR连成一个链表，这个链表就称为版本链。

ReadView

ReadView就是事务执行SQL语句时，产生的读视图。

注意，ReadView是与SQL绑定的，而并不是事务，所以即使在同一个事务中，每次SQL启动时构造的ReadView（up_trx_id和low_trx_id）也都是不一样的

核心的数据结构

trx_id_t: 每个读写事务都会通过全局id产生器产生一个id，只读事务的事务id为0，只有当其切换为读写事务时候再分配事务id。
trx_sys_t: 这个结构体用来维护系统的事务信息，全局只有一个，在数据库启动的时候初始化。比较重要的字段有：

max_trx_id：这个字段表示系统当前还未分配的最小事务id，如果有一个新的事务，直接把这个值作为新事务的id，然后这个字段递增即可。
descriptors：这个是一个数组，里面存放着当前所有活跃的读写事务id，当需要开启一个readview的时候，就从这个字段里面拷贝一份，用来判断记录的对事务的可见性。

read_view_t:
InnDB为了判断某条记录是否对当前事务可见，需要对此记录进行可见性判断，这个结构体就是用来辅助判断的。
当需要一个一致性读的时候(即创建新的readview时)，会把全局读写事务id拷贝一份到readview本地(read_view_t->descriptors)，当做当前事务的快照。read_view_t中包含了3个重要的属性：

up_limit_id：read_view_t->descriptors中最小的值，所有小于此值的记录都应该被此readview看到，可以理解为low water mark。
low_limit_id：创建read_view_t时的max_trx_id，其一定大于read_view_t->descriptors中的最大值，所有大于等于此值的记录都不应该被此readview看到，可以理解为high water mark。
trx_ids：read_view_t->descriptors的事务id列表，即Read View初始化时当前未提交的事务列表。

可见性判断逻辑

当查询出一条记录后(记录上有一个trx_id，表示这条记录最后被修改时的事务id)，可见性判断的逻辑如下：

如果记录上的trx_id小于read_view_t->up_limit_id，则说明这条记录的最后修改在readview创建之前，因此这条记录可以被看见。
如果记录上的trx_id大于等于read_view_t->low_limit_id，则说明这条记录的最后修改在readview创建之后，因此这条记录不可以被看见。
如果记录上的trx_id在read_view_t->up_limit_id和read_view_t->low_limit_id之间，分两种情况：

trx_id在read_view_t->descriptors，则表示这条记录的最后修改是在readview创建之时，被另外一个活跃事务所修改，所以这条记录也不可以被看见。
trx_id不在read_view_t->descriptors之中，则表示这条记录的最后修改在readview创建之前，所以可以看到。

当进行RR读的时候，除了事务自己做的变更外，trx_ids中的事务对于本事务是不可见的。

可见性算法

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID ）取出来，如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID , 那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本。

分析

虽然规则很多，但是我们可以看到都是根据事务开启时生成的事务id，和活跃事务进行比较判断来得出当前事务可以看到的数据。

根据可见性算法，Mysql和Pgsql在RR级别下都保证了RR的可重复读部分，生成快照，多次读都读取快照的内容；

而在我们的例子中在A插入提交后B更新后Mysql的事务发生了变化，而Pgsql并没有发生变化依然可以读取原始快照。

我们从增删改入手分析。

Pgsql元组的增删改

元组的表现形式

插入

假设元组是由txid=99的事务插入页面中的，在这种情况下，被插入元组的首部字段会依以下步骤设置。

Tuple_1：

t_xmin设置为99，因为此元组由txid=99的事务所插入。
t_xmax设置为0，因为此元组尚未被删除或更新。
t_cid设置为0，因为此元组是由txid=99的事务所执行的第一条命令所插入的。
t_ctid设置为(0,1)，指向自身，因为这是该元组的最新版本。

删除

在删除操作中，目标元组只是在逻辑上被标记为删除。目标元组的t_xmax字段将被设置为执行DELETE命令事务的txid

情况下，Tuple_1的首部字段会依以下步骤设置。

Tuple_1：

t_xmax被设为111。

如果txid=111的事务已经提交，那么Tuple_1就不是必需的了。通常不需要的元组在PostgreSQL中被称为死元组（dead tuple）。

死元组最终将从页面中被移除。

更新

在更新操作中，PostgreSQL在逻辑上实际执行的是删除最新的元组，并插入一条新的元组

假设由txid=99的事务插入的行，被txid=100的事务更新两次。

当执行第一条UPDATE命令时，Tuple_1的t_xmax被设为txid 100，在逻辑上被删除；然后Tuple_2被插入；接下来重写Tuple_1的t_ctid以指向Tuple_2。Tuple_1和Tuple_2的头部字段设置如下。

Tuple_1：

t_xmax被设置为100。
t_ctid从(0,1)被改写为(0,2)。

Tuple_2：

t_xmin被设置为100。
t_xmax被设置为0。
t_cid被设置为0。
t_ctid被设置为(0,2)。

当执行第二条UPDATE命令时，和第一条UPDATE命令类似，Tuple_2被逻辑删除，Tuple_3被插入。Tuple_2和Tuple_3的首部字段设置如下。

Tuple_2：

t_xmax被设置为100。
t_ctid从(0,2)被改写为(0,3)。

Tuple_3：

t_xmin被设置为100。
t_xmax被设置为0。
t_cid被设置为1。
t_ctid被设置为(0,3)。

与删除操作类似，如果txid=100的事务已经提交，那么Tuple_1和Tuple_2就成为了死元组，而如果txid=100的事务中止，Tuple_2和Tuple_3就成了死元组。

Mysql的增删改

ReadView 的特性

固定不可变：一旦 ReadView 被创建，通常不会在事务生命周期内更新。它代表事务在某一时间点看到的快照，保证在整个事务过程中读取到的数据一致性。
只能用于查询：ReadView 主要用于查询操作，用来控制在多版本情况下，当前事务能看到哪些行数据版本。写操作（如 UPDATE、INSERT、DELETE）不会使用 ReadView，而是直接对数据进行修改。

因此，在 MySQL 的 RR 隔离级别下：

ReadView 不能动态更新，因为它必须保持一致性，用于事务的多次读取操作。
ReadView 的创建时机通常是在 第一次读操作 时，且在整个事务期间一直有效，直到事务结束为止。

当事务在 RR 隔离级别下执行 SELECT 查询时，它会根据 ReadView 来决定能看到哪些行版本。只要 ReadView 一旦创建，它就会保持不变，保证所有的查询看到的是相同的数据快照。

数据的多版本存储

InnoDB 会为每一行数据保存多个版本，这些版本保存在 undo log（回滚日志） 中。当数据被修改时，InnoDB 并不会立即删除旧的行版本，而是将旧版本保存在 undo log 中，并在原数据行上生成新的版本。

行的隐藏列

：每个数据行除了用户定义的列外，InnoDB 还会维护两个隐藏列，用于支持 MVCC：
- trx_id：表示最后一次修改该行的事务 ID（即哪个事务修改了这行）。
- roll_pointer：指向 undo log 中该行数据的上一个版本（即上一版本的快照）。
通过这两个字段，InnoDB 可以在需要时通过

trx_id

和

undo log

追溯到该行的历史版本。

当一个事务修改数据时，InnoDB 会将旧数据的快照写入 undo log，然后对数据行进行修改。这些 undo log 保存了数据行的旧版本，使得 InnoDB 能够根据需要返回某个时间点的快照数据。

撤销（undo）机制：当事务执行回滚时，InnoDB 可以通过 undo log 恢复数据的旧版本。
一致性读：当事务读取数据时，InnoDB 通过 ReadView 判断当前事务应该看到的数据版本，如果当前行的版本是事务不可见的，InnoDB 会通过 undo log 还原到可见的版本。

当一个事务执行 UPDATE 操作时，InnoDB 并不会复制一份数据来进行修改，而是直接修改原始数据行。但这个修改并不意味着旧数据被永久覆盖，InnoDB 使用 MVCC 来保留旧版本数据以支持并发读取和回滚操作。

具体过程如下：

更新原始数据行：UPDATE 操作会直接修改当前数据行，并生成新的事务 ID（trx_id），表明该行数据是由当前事务最新修改的。
保存旧版本数据到 undo log：在修改之前，InnoDB 会将当前数据行的旧版本写入 undo log，作为该行的历史版本。这意味着，如果事务未提交，旧版本数据依然可以被读取或回滚。
更新 trx_id 和 roll_pointer：修改后的行会更新其 trx_id，标记为由当前事务修改，并通过 roll_pointer 指向旧版本数据（存储在 undo log 中）。

ReadView 失效原因

UPDATE、INSERT、DELETE 操作会导致当前事务的快照失效，这是因为：

MySQL 需要确保在执行写操作后，事务可以看到自己所修改的数据，因此修改后再执行的读操作需要生成新的 ReadView。
旧的 ReadView 已经不再适用于描述当前数据的状态，事务需要基于新的版本链读取数据。
如果不更新 ReadView，事务可能无法看到自己修改的数据，违反了事务隔离的基本要求。

数据案例

根据上面描述的原因，我们强化例子来验证描述。

每次操作时候增加查看当前事务ID的代码。

Pgsql

-- 事务A
show default_transaction_isolation;

show transaction_isolation;

BEGIN;
set transaction_isolation = 'repeatable read';
select * from error_wt_msg where org_id in ('1','2');
select txid_current_snapshot();
[
1.    结果: (28644693:28644693:)
]

select * from error_wt_msg where org_id in ('1','2');

update error_wt_msg set bpartner_id='2' where org_id in ('1','2');

select * from error_wt_msg;

select txid_current_snapshot();

[
4.    结果: (28644693:28644693:)
]
commit ;

-- 事务B
BEGIN TRANSACTION;

select * from error_wt_msg;
select txid_current_snapshot();
[
2.    结果: (28644693:28644693:)
]
insert into error_wt_msg values ('2','1','1');

commit ;
[
3. 提交事务
]

Mysql

-- 事务A
begin ;
show variables  like 'transaction_isolation';
select * from menu where id>3;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
[
 1.   结果: 
    trx_id: 421274251923456
]
select *
from menu where id>3;

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

[
  4.  结果: 
    trx_id: 421274251923456
]
update menu set name ='测试幻读' where id>3;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
[
  5.  结果: 
    trx_id: 1823
]
select *
from menu where id>3;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
[
  6.  结果: 
    trx_id: 1823
]
commit ;

-- 事务B
begin ;

select * from menu where id>3;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
[
2.    结果: 
    trx_id: 421274251923456,421274251922648
]
insert into menu(id, name, avatar, id_delete, asName) VALUE (4,'test','',0,'');

commit ;
[
    3. 提交事务
]

我们可以发现，在Pgsql中无论其他事务如何操作，当前事务所能够查询到的事务信息都不会发生变化[当前例子]。又因为Pgsql插入删除更新都是增加新的元祖，所以即使不更新当前事务的事务信息也能够实现RR隔离级别下的可重复读。

反观Mysql,因readView的不可变性和update操作直接操作的原始数据行，并通过两个隐藏字段来实现其他事务可见版本，所以为了保证当前事务的RR级别下的可重复读[自己更新数据后需要能够看到更新，而更新的数据是在原始数据行上做的更新]，需要进行当前事务的readview版本更新，即生成新的readview，导致读取到了本不该属于自己的数据[范围数据中的幻读]。

文章版权归作者所有，未经允许请勿转载。

THE END