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
## session 1
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.
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不一样的结果。
Transaction Isolation Levels
观察快照
# 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的实现方式不同的原因吧。