首页 » MySQL, ORACLE 9i-23c, PostgreSQL/GaussDB » multi-version read consistency in Oracle、MySQL、PostGreSQL(数据库比较系列十二)

multi-version read consistency in Oracle、MySQL、PostGreSQL(数据库比较系列十二)

在多人同时访问与修改数据时， 最大的难题之一是：一方面要力争最大的并发访问，与此同时还要确保每个用户能以一致的方式读取和修改数据。ANSI/ISO SQL 标准定义了4 种事务隔离级别，对于相同的事务，采用不同的隔离级别分别有不同的结果。这些隔离级别是根据3 个“现象”定义的，如dirty read、nonrepeatable read、phantom read。Oracle 明确地支持READ COMMITTED（读已提交）和SERIALIZABLE（可串行化）隔离级别，在Oracle 中READ COMMITTED 则有得到读一致查询所需的所有属性，在其他数据库中的读READ COMMITTED 可能会有不同的答案， 最近有个客户在测试migrate oracle to postgreSQL测试发现一个批处理的结果并非一致，于是做一个小小的测试验证一下。

# ORACLE

SQL> CREATE TABLE test (id INT PRIMARY KEY);
Table created.

SQL> INSERT INTO test VALUES (1);
1 row created.

SQL> INSERT INTO test VALUES (2);
1 row created.

SQL> alter table test add ctime date;
Table altered.

SQL> select * from test;
        ID CTIME
---------- -------------------
         1
         2

SQL> update test set ctime=sysdate;
2 rows updated.

## session 1

SQL> set time on
12:22:03 SQL>
12:22:03 SQL>
12:22:03 SQL>
begin
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,sysdate);
end;
/
PL/SQL procedure successfully completed.

12:22:06 SQL> select * from test;
        ID CTIME
---------- -------------------
         2 2022-08-19 12:21:31
         1 2022-08-19 12:22:06

## session 2
SQL> set time on
12:21:47 SQL>
12:22:10 SQL>
begin
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,sysdate);
12:22:11   4  end;
12:22:12   5  /

--   hang
## session 1 
12:22:24 SQL> commit;
Commit complete.

## session 2
12:22:10 SQL>
begin
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,sysdate);
12:22:11   4  end;
12:22:12   5  /
PL/SQL procedure successfully completed.

12:23:00 SQL> select * from test;
             ID CTIME
--------------- -----------------
              1 20220819 12:22:36
              2 20220819 12:21:31

SELECT s.sid, s.serial#,decode(bitand(flag,power(2,28)),0,'READ COMMITTED',1,'SERIALIZABLE')  isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');
       SID    SERIAL# ISOLATION_LEVE
---------- ---------- --------------
         1      34211 READ COMMITTED

Note:
默认系统事务隔离级别是READ COMMITTED,也就是读已提交, Oracle 可以在begin end 匿名块中做到以事务开始时间的一致性，session 1更新 delete insert 作为一个独立的事务，session 2在session 1后发起事务（begin），oracle虽然是在读已提交事务隔离级别，但是一样可以解决了不可重复读与幻读在读一致性上的实现， session 2可以在begin 事务种读到一致性数据也可以读到session 1 insert的新数据，并delete后重新insert了新数据。

# POSTGRESQL

## SESSION1
sdbo=# CREATE TABLE test (id INT PRIMARY KEY);
CREATE TABLE
sdbo=# INSERT INTO test VALUES (1);
INSERT 0 1
sdbo=# INSERT INTO test VALUES (2);
INSERT 0 1

# session 1

sdbo=# alter table test add ctime time;
ALTER TABLE
sdbo=# update test set ctime=now();
UPDATE 2

sdbo=# select * from test;
 id |      ctime
----+-----------------
  2 | 14:47:37.415516
  1 | 14:47:37.415516
(2 行记录)

sdbo=# begin;
BEGIN
sdbo=*# DELETE FROM test WHERE id=1;
DELETE 1
sdbo=*# INSERT INTO test VALUES (1,now());
INSERT 0 1
sdbo=*# select * from test;
 id |      ctime
----+-----------------
  2 | 14:47:37.415516
  1 | 14:49:49.151784
(2 行记录)


# session 2

begin;
DELETE FROM test WHERE id=1;
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,now());

sdbo=# begin;
BEGIN
sdbo=*# DELETE FROM test WHERE id=1;
-- hang

# session 1
sdbo=*# commit;
COMMIT
sdbo=#

# session 2
sdbo=# begin;
BEGIN
sdbo=*# DELETE FROM test WHERE id=1;
DELETE 0
sdbo=*# DELETE FROM test WHERE id=1;
DELETE 1
sdbo=*# INSERT INTO test VALUES (1,now());
INSERT 0 1
sdbo=*# select * from test;
 id |      ctime
----+-----------------
  2 | 14:47:37.415516
  1 | 14:50:13.062228
(2 行记录)


sdbo=# show transaction_isolation
sdbo-# ;
 transaction_isolation
-----------------------
 read committed
(1 行记录)

Note:
注意在postgresql中和oracle是不同的行为， 在同样读已提交事务隔离级别下， session 2的第一条delete在session 1 提交后，显示delete 0条记录，第二条delete在同一个begin 事务中显示delete 1条记录。同一个事务中相同SQL显示了不同的结果， 可见在一些应用中如果把多个SQL放在一个事务begin中，如果多用户并发，最终会导致和oracle不一样的结果。

Table 13.1. Transaction Isolation Levels

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

观察快照

# session 1
[local]:5432 postgres@anbob=# update test set ctime=now();
UPDATE 2
[local]:5432 postgres@anbob=# select xmin,xmax,t.* from test t;
 xmin | xmax | id |     ctime
------+------+----+----------------
  623 |    0 |  2 | 21:05:43.59544
  623 |    0 |  1 | 21:05:43.59544
(2 rows)

[local]:5432 postgres@anbob=# begin;
BEGIN
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |     ctime
------+------+----+----------------
  623 |    0 |  2 | 21:05:43.59544
  623 |    0 |  1 | 21:05:43.59544
(2 rows)

[local]:5432 postgres@anbob=#* DELETE FROM test WHERE id=1;
DELETE 1
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |     ctime
------+------+----+----------------
  623 |    0 |  2 | 21:05:43.59544
(1 row)

[local]:5432 postgres@anbob=#* INSERT INTO test VALUES (1,now());
INSERT 0 1
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |      ctime
------+------+----+-----------------
  623 |    0 |  2 | 21:05:43.59544
  624 |    0 |  1 | 21:06:03.277602
(2 rows)

# session 2
[local]:5432 postgres@anbob=#
[local]:5432 postgres@anbob=# begin;
BEGIN
[local]:5432 postgres@anbob=#* select txid_current();
 txid_current
--------------
          625
(1 row)
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |     ctime
------+------+----+----------------
  623 |    0 |  2 | 21:05:43.59544
  623 |  624 |  1 | 21:05:43.59544
(2 rows)

[local]:5432 postgres@anbob=#* DELETE FROM test WHERE id=1;
-- hang

# session 1
[local]:5432 postgres@anbob=#* commit;
COMMIT

# session 2
[local]:5432 postgres@anbob=#* DELETE FROM test WHERE id=1;
DELETE 0
[local]:5432 postgres@anbob=#* select txid_current();
 txid_current
--------------
          625
(1 row)

[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |      ctime
------+------+----+-----------------
  623 |    0 |  2 | 21:05:43.59544
  624 |    0 |  1 | 21:06:03.277602
(2 rows)

[local]:5432 postgres@anbob=#* DELETE FROM test WHERE id=1;
DELETE 1
[local]:5432 postgres@anbob=#* select txid_current();
 txid_current
--------------
          625
(1 row)

[local]:5432 postgres@anbob=#* INSERT INTO test VALUES (1,now());
INSERT 0 1
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |      ctime
------+------+----+-----------------
  623 |    0 |  2 | 21:05:43.59544
  625 |    0 |  1 | 21:06:36.750481
(2 rows)

# session 1
[local]:5432 postgres@anbob=# select xmin,xmax,t.* from test t;
 xmin | xmax | id |      ctime
------+------+----+-----------------
  623 |    0 |  2 | 21:05:43.59544
  624 |  625 |  1 | 21:06:03.277602
(2 rows)

[local]:5432 postgres@anbob=#

Note:
在postgresql中即使在begin同一个事务中，不同的SQL执行时，每个SQL会取一次当前数据的快照， 像上面session 2的事务块第1个delete执行前快照记录忆被session delete ,insert 虽已提交，但是postgresql中无法幻读，也就是无法像oracle一样根据事务开始时间保证一致性， 就读取不到insert的新数据。 第二个delete sql执行前的快照读取到了session 1 inserted的数据。

# mysql

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
设置隔离级别
方式1：通过set命令
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
其中level有4种值：
level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

# session 1
MYSQL_root@localhost [anbob]> select * from test;
+----+----------+
| id | ctime    |
+----+----------+
|  1 | 15:31:15 |
|  2 | 15:31:15 |
+----+----------+
2 rows in set (0.00 sec)

MYSQL_root@localhost [anbob]>
MYSQL_root@localhost [anbob]> begin;
Query OK, 0 rows affected (0.00 sec)

MYSQL_root@localhost [anbob]> DELETE FROM test WHERE id=1;
Query OK, 1 row affected (0.00 sec)

MYSQL_root@localhost [anbob]> INSERT INTO test VALUES (1,now());
Query OK, 1 row affected (0.00 sec)

# session 2

begin;
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,now());

MYSQL_root@localhost [anbob]> begin;
Query OK, 0 rows affected (0.00 sec)

MYSQL_root@localhost [anbob]> DELETE FROM test WHERE id=1;
-- hang 

# session 1
MYSQL_root@localhost [anbob]> commit;
Query OK, 0 rows affected (0.01 sec)

MYSQL_root@localhost [anbob]> select * from test;
+----+----------+
| id | ctime    |
+----+----------+
|  1 | 15:31:40 |
|  2 | 15:31:15 |
+----+----------+
2 rows in set (0.00 sec)

# session 2
Database changed
MYSQL_root@localhost [anbob]> begin;
Query OK, 0 rows affected (0.00 sec)

MYSQL_root@localhost [anbob]> DELETE FROM test WHERE id=1;
Query OK, 1 row affected (7.87 sec)

MYSQL_root@localhost [anbob]> INSERT INTO test VALUES (1,now());
Query OK, 1 row affected (0.00 sec)

MYSQL_root@localhost [anbob]> select * from test;
+----+----------+
| id | ctime    |
+----+----------+
|  1 | 15:31:56 |
|  2 | 15:31:15 |
+----+----------+
2 rows in set (0.00 sec)


MYSQL_root@localhost [anbob]> commit;
Query OK, 0 rows affected (0.01 sec)

MYSQL_root@localhost [anbob]> select * from test;
+----+----------+
| id | ctime    |
+----+----------+
|  1 | 15:31:56 |
|  2 | 15:31:15 |
+----+----------+
2 rows in set (0.00 sec)

# session 1
MYSQL_root@localhost [anbob]> select * from test;
+----+----------+
| id | ctime    |
+----+----------+
|  1 | 15:31:56 |
|  2 | 15:31:15 |
+----+----------+
2 rows in set (0.00 sec)

MYSQL_root@localhost [anbob]> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.01 sec)

NOTE:
默认mysql是可重复读隔离级别，可见行为和oracle基本一致， 但是该模式下有间隙锁问题，建议修改为读已提交；


MYSQL_root@localhost [anbob]> set TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

修改隔离级别后 该问题现象一致；

Summary:
在某些事务应用中begin delete xxx; insert ; end类似多条DML的事务中，oracle和mysql基本一致的形为，可以以begin 事务时间保持一致性， 而在postgresql中虽是begin 事务，但是不同的SQL是在执行时取数据的快照，产生了不致的数据， 可能这是undo 与 postgresql中的mvcc的实现方式不同的原因吧。

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：