POSTGRESQL RC事务处理与ORACLE MYSQL 的区别 --对PGFANS 群里面的问题的分解

有一个同学在PGFANS 群里面提了一个问题，在他实验的某个操作中发现PG 和 ORACLE 使用同样的操作流程后，得到的结果不一致。所以下面准备验证并找到一些可以解释的原因。

测试库名test

测试表 test

测试数据

id    age 

1     20

2     22

3     24

首先我们要确认 PG 的隔离 RC的模式 ，另外我要排除一些不存在的问题

下面是整个的操作流程，由于截图需要截图好几个，不利于查看，所以将其变成文字，并序列化， 每行中有执行的顺序号 和 属于的SESSION 

1   test=# begin;  SESSION 1   #开始SESSION 1 

BEGIN

2 test=# select xmin,xmax,cmin,cmax,* from test;  SESSION 1

  xmin   | xmax | cmin | cmax | id | age 

---------+------+------+------+----+-----

 2027732 |    0 |    0 |    0 |  2 |  40

 2027735 |    0 |    1 |    1 |  1 |  20

(2 rows)

#查看当前的每行的事务情况

3  test=# select txid_current_if_assigned();   SESSION 1

 txid_current_if_assigned 

--------------------------

                         

(1 row)

# 当前并未生成事务号

4 test=# begin;  SESSION 2  # 同时启动SESSION 2

BEGIN

test=# select * from test;  SESSION 2

 id | age 

----+-----

  2 |  40

  1 |  20

(2 rows)

# SESSION 2 中查看到的数据

 5 test=# select txid_current_if_assigned();  SESSION 2

 txid_current_if_assigned 

--------------------------

                         

(1 row)

6 test=# select txid_current_if_assigned();  SESSION 2

 txid_current_if_assigned 

--------------------------

                         

(1 row)

7 test=# delete from test where id =1 ;  SESSION 1

DELETE 1

test=# select txid_current_if_assigned();

 txid_current_if_assigned 

--------------------------

                  2027737

(1 row)

 

#SESSION 1 删除了数据

8 test=# update test set age = 100 where id =1 ;  SESSION 2   WAITING...................

UPDATE 0

test=# select txid_current_if_assigned();

 txid_current_if_assigned 

--------------------------

                  2027738

(1 row)

#SESSION 2 更新数据  处于等待状态

9  test=# select xmin,xmax,cmin,cmax,* from test; SESSION 1

  xmin   | xmax | cmin | cmax | id | age 

---------+------+------+------+----+-----

 2027732 |    0 |    0 |    0 |  2 |  40

(1 row)

10test=# select txid_current_if_assigned();  SESSION 1

 txid_current_if_assigned 

--------------------------

                  2027737

(1 row)

11 test=# select xmin,xmax,cmin,cmax,* from test;  SESSION 2

  xmin   | xmax | cmin | cmax | id | age 

---------+------+------+------+----+-----

 2027732 |    0 |    0 |    0 |  2 |  40

 2027737 |    0 |    1 |    1 |  1 |  20

(2 rows)

#SESSION 1 插入数据

12 test=# insert into test (id,age) values (1,20);  SESSION 1

INSERT 0 1

test=# select txid_current_if_assigned();

 txid_current_if_assigned 

--------------------------

                  2027737

(1 row)

13 test=# select xmin,xmax,cmin,cmax,* from test;  SESSION 1

  xmin   | xmax | cmin | cmax | id | age 

---------+------+------+------+----+-----

 2027732 |    0 |    0 |    0 |  2 |  40

 2027737 |    0 |    1 |    1 |  1 |  20

(2 rows)

14  test=# commit;  SESSION 1

COMMIT

#SESSION 1 COMMIT

15  test=# select xmin,xmax,cmin,cmax,* from test; SESSION 1

  xmin   | xmax | cmin | cmax | id | age 

---------+------+------+------+----+-----

 2027732 |    0 |    0 |    0 |  2 |  40

 2027737 |    0 |    1 |    1 |  1 |  20

(2 rows)

16   test=# commit;         SESSION  2   

COMMIT

#SESSION 2 COMMIT

17 test=# select xmin,xmax,cmin,cmax,* from test;  SESSION 2

  xmin   | xmax | cmin | cmax | id | age 

---------+------+------+------+----+-----

 2027732 |    0 |    0 |    0 |  2 |  40

 2027737 |    0 |    1 |    1 |  1 |  20

(2 rows)

结果：SESSION 2 不会更新 SESSION 1中后插入的数据。

从上面的步骤中我们能看到或者领会到PG 的那些特性

• 事务ID 是自增的  

• 每行数据会用（t_xmin, t_xmax）来标示自己的可用性

• t_xmin 存储的是产生这个元组的事务ID，可能是insert或者update语句t_xmax 存储的是删除或者锁定这个元组的XID

• 事务只能看见t_xmin比自己XID 小且没有被删除的元组

以上是官方文档中的提示，已经明确的说明了上述的问题，并且也给出了一些建议。

那我们的工作到底完成了没有，没有，我们提升PG 的隔离级别到 RR.

结果如下：

SESSION 1

SESSION 2

在将SESSION的级别提升后，结果就变化了，再次在SESSION 2中操作，直接报错。

最后的问题是，提出问题的同学反映ORACLE 与PG的在类似的环节情况下，反馈的情况不一。同时我这边也通过MYSQL 8 来将上述的操作同样做了，与那位同学反映的情况一样。

个人认为这并不是PG数据库本身的缺陷，这是一种数据库处理某种复杂情况和隔离级别对数据一致性的一种取舍。

如果遇到这样的情况如何操作，有如下建议

1  可以提高数据库的隔离级别到RR （如果你的数据库中有类似业务或操作）

2  在设计业务逻辑时，通过逻辑删除而不是物理删除来对业务表进行操作。

3 可以在操作时添加 selecr for update 类似这样的语句对于数据的可操作性进行一个确认。