一、环境介绍

操作系统：CentOS Linux release 7.6.1810 (Core) DB版本：PostgreSQL -11.5 on x86_64-pc-linux-gnu

二、问题描述

同一个实例运行的3个session，在T2时刻session 1向表table01插入一行数据之后，session 2和session 3两个会话执行相同的SQL查询的结果不一样。如下：


mysql 未提交 binlog sql中显示未提交行1数据_事务隔离级别


上图中，session 2查到的是2行记录，session 3却只有1条记录。为什么session 2能看到session 1新插入的记录，而session 3却看不到呢？这种情况是在什么场景下发生的呢？

三、相关理论知识回顾

如果有熟悉事务隔离级别的朋友可能已经想到大概的原因。关于事务的隔离级别的介绍，有兴趣的可以查看上一篇文章。

PostgreSQL的事务隔离级别介绍及更改

在说明原因之前，这里先介绍一下PostgreSQL中取名为“transaction snapshot”这个东西，即事务快照。

至于什么是事务快照，以及为什么需要事务快照，我在官方文档中暂时没有看到具体的描述。

下面是个人的理解，不代表官方：

平时我们执行SQL数据读取的时候，实际上读取的是一种状态数据，transaction snapshot本义上指是某个时刻事务的快照，实质代表的是具体时刻具体事务下数据的状态。

既然是状态，那么可能就有当前状态、上一个状态、下一个状态一说。数据库中所说的事务可看作是将数据从上一个状态进入到另一个状态的单位。

这是数据库中的“词典”，理解起来比较干涩，我们可以对应到人类词典中比较容易理解的三个阶段：过去的、当前的、未来的。

所以，我对事务快照的理解为三个阶段：一个transaction snapshot将事务划分为过去的、当前的、未来的三个区域。

比较友好的是，PostgreSQL官方给我们提供了一个获取事务快照的函数：txid_current_snapshot。下面是官网对txid_current_snapshot函数输出结果的原文解析：

Table 9.75. Snapshot Components for PostgreSQL-12

详细介绍见：https://www.postgresql.org/docs/current/functions-info.html

  • xmin，当前处于active状态的最小事务编号；
  • xmax，未来产生的事务中，第一个将被分配的事务编号；
  • xip_list,当前处于active 状态的事务列表(包括in progress和future状态的事务)，其余为inactive。

如下，查看当前时刻事务快照：

(postgres@[local]:5432)[akendb01]#select txid_current_snapshot();txid_current_snapshot-----------------------639:642:639,641 <<
  • 1.xmin=639,表示当前时刻快照中最小的是639这个事务。小于该编号的事务都已经终止(提交、回滚或异常终止)，这些事务属于“过去的”范围区域。
  • 2.xmax=642,表示将来新事务产生时分配到的第一个事务编号txid，大于等于642的事务未产生，属于“将来的”范围区域。
  • 3.xip_list=(639,641)，表示该快照时刻639和641这两个事务正处于active状态，属于“当前的”范围区域。

画成图就是下面这个样子：


mysql 未提交 binlog sql中显示未提交行1数据_sql查询时为什么会出现两个编号_02


transaction snapshot examples

四、原因分析

在PostgreSQL中，提交读(或者叫读提交)read committed事务隔离级别下，session中同一事务的每条SQL执行的时候都会自动去读取当前时刻的事务快照；而在repeatable read级别下，session中同一事务只会在事务开始的第一个SQL获取一次事务快照。

因为read committed级别下，同一事务中不同时刻的SQL获取的快照可能不一样，因此读到的数据可能会不一样。

而repeatable read在整个事务周期只获取一次事务快照，所以同一事务内所有SQL使用的快照都是一致的，因此可以实现重复读，规避了幻读的产生。

pg默认的事务隔离级别transaction isolation为read committed。这是上面文章开头session 2中read committed事务级别下产生幻读的原因，也是session 3中repeatable read可以实现重复读的原因。

请原谅我在文章开头故意将会话的事务隔离级别忽略，目的是为了引导大家可以一起思考。

说到这里，MySQL的朋友可能觉得PostgreSQL中transaction snapshot和MySQL中的一致性视图Read view有点像。

所以，对于文章开头的问题：

  • 1.对于session 2和session 3的结果来说，上述的问题并非因为数据的不一致，而是因为不同的事务隔离级别读取的结果有所区别。
  • 2.对于session 2来说，在同一个事务里面执行相同的查询语句前后得到的结果不一致，这种情况叫幻读。

什么是幻读? 下面是官方的原文解析：

phantom read

A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

大概意思指：

在一个事务中相同的SQL查询条件前后读取到的结果不一致，原因是后者读取到了其他事务中新提交的数据。

这个问题其实在PostgreSQL-12官方文档中有所提示，pg中repeatable read隔离级别下是不会出现幻读的。如下图标红处所示：


mysql 未提交 binlog sql中显示未提交行1数据_事务隔离级别_03


PostgreSQL-12事务隔离级别

为什么在PostgreSQL中的repeatable read下是Allowed,but not in PG呢？

这正是因为事务快照的作用。下面将文章开始时的例子进行充分的演示。

五、场景演示：提交读、可重复读事务快照对比

下面针对read committed和repeatable read两种事务隔离模式下的事务快照进行对比测试，例子如下：


mysql 未提交 binlog sql中显示未提交行1数据_SQL_04


1.T0时间段：

session 1在默认情况下开启事务，txid=666。

session 2在read committed隔离模式下开启事务，txid=674；

session 3在可重复读repeatable read隔离模式下开启事务，txid=675；

session 4开启事务txid=676(略)。

1)事务开始前table01中只有一行记录：tuple 1

(postgres@[local]:5432)[akendb01]#select * from table01; id | name----+-------- 1 | aken01(1 row)(postgres@[local]:5432)[akendb01]#

2)session 1在默认提交读模式下开启事务，事务编号txid=666。

(postgres@[local]:5432)[akendb01]#begin;BEGIN(postgres@[local]:5432)[akendb01]#show default_transaction_isolation; default_transaction_isolation------------------------------- read committed(1 row)(postgres@[local]:5432)[akendb01]#(postgres@[local]:5432)[akendb01]#select txid_current(); txid_current-------------- 666(1 row)(postgres@[local]:5432)[akendb01]#

3)session 2:在提交读隔离级别下开启事务，事务编号txid=674。

(postgres@[local]:5432)[akendb01]#start transaction isolation level read committed;START TRANSACTION(postgres@[local]:5432)[akendb01]#select txid_current(); txid_current-------------- 674(1 row)

4)session 3:在可重复读隔离级别下开启事务，事务编号txid=675

(postgres@[local]:5432)[akendb01]#start transaction isolation level repeatable read;START TRANSACTION(postgres@[local]:5432)[akendb01]#select txid_current(); txid_current-------------- 675(1 row)

5)session 4:分配一个事务txid=676

(postgres@[local]:5432)[akendb01]#select txid_current(); txid_current-------------- 676(1 row)

2.T1时刻，session 1、2、3获取当前事务快照，并读取table01的记录。

1)session 1：读取到的事务快照为'666:676:674,675'，读取表的记录数为1行。

(postgres@[local]:5432)[akendb01]#select txid_current_snapshot(); txid_current_snapshot-----------------------666:676:674,675   <<< 实际上txid=676在session 4已经分配，这个和官网将xmax解析为将来产生的第一个事务有矛盾，pg获取事务快照时最后一个txid是否会滞后？(1 row)(postgres@[local]:5432)[akendb01]#(postgres@[local]:5432)[akendb01]#select * from table01;id | name----+--------1 | aken01(1 rows)(postgres@[local]:5432)[akendb01]#

2)session 2：读取到的事务快照为'666:676:666,675'，读取表的记录数为1行。

(postgres@[local]:5432)[akendb01]#select txid_current_snapshot(); txid_current_snapshot----------------------- 666:676:666,675(1 row)(postgres@[local]:5432)[akendb01]#(postgres@[local]:5432)[akendb01]#select * from table01;id | name----+--------1 | aken01(1 rows)(postgres@[local]:5432)[akendb01]#

3)session 3：读取到的事务快照为'666:676:666,674'，读取表的记录数为1行。

(postgres@[local]:5432)[akendb01]#select txid_current_snapshot(); txid_current_snapshot----------------------- 666:676:666,674(1 row)(postgres@[local]:5432)[akendb01]#(postgres@[local]:5432)[akendb01]#select * from table01;id | name----+--------1 | aken01(1 rows)(postgres@[local]:5432)[akendb01]#

3.T2时刻，session 1往table01插入一行记录并commit提交，session 1、2、3读取table01的记录。

1)session 1在事务txid=666中获取的事务快照为'674:676:674,675'，查看结果中可以看到自己新插入的tuple 2。

(postgres@[local]:5432)[akendb01]#insert into table01 values(2,'aken02');INSERT 0 1(postgres@[local]:5432)[akendb01]#commit；COMMITTED(postgres@[local]:5432)[akendb01]#select txid_current_snapshot();txid_current_snapshot-----------------------674:676:674,675 <<< 事务666已提交，session 1事务快照改变，xmin=674(1 row)(postgres@[local]:5432)[akendb01]#select * from table01;id | name----+--------1 | aken012 | aken02(2 rows)(postgres@[local]:5432)[akendb01]#

2)session 2:

session 2在事务txid=674中获取到的快照为'674:676:675'和T1时刻不同，能看到事务txid=666新插入的tuple 2，产生幻读。

(postgres@[local]:5432)[akendb01]#select txid_current_snapshot();txid_current_snapshot-----------------------674:676:675  <<< session 1的事务666

3)session 3：

session 3在事务txid=675中获取的事务快照依旧为'666:676:666,674'，和T1时刻的保持一致，看不到事务txid=666新插入的tuple 2，无幻读产生。

(postgres@[local]:5432)[akendb01]#select txid_current_snapshot();txid_current_snapshot-----------------------666:676:666,674  <<

4.T3时间段

session 2、session 3事务结束，session 1、2、3读取到的事务快照都为“676:676:”，且查询结果相同。

(postgres@[local]:5432)[akendb01]#select txid_current_snapshot();txid_current_snapshot-----------------------676:676: <<