修改oracle数据库的隔离级别,Oracle ---- 事务隔离级别那些事儿

昨天看到墨天轮小助手的一道关于Mysql数据库事务隔离度的问题，忽然想到了以前遇到过的一件关于ORACLE数据库事务隔离度的事儿，觉得可以帮助大家加深关于数据库事务隔离的理解，于是整理出来分享给大家。

首先讲一下事务隔离度。

事务隔离度(Transaction isolation models)并不是个别数据库提出的概念，而是一个国际化标准( ANSI and ISO/IEC)。所有数据库(不仅仅是传统的关系数据库，例如OARCLE，Mysql，SQL Server，也包括NoSQL数据库，例如TiDB，OceanBase等等)都要在这个标准上设计，区别在于支持哪个(或那些)模式。它标识一个数据库系统能在什么程度上保证“读一致性”的能力。

下面用一个简单的小例子说明一下事务隔离度(Transaction isolation models)。有两个事务，事务A负责更新表T1，事务B负责查看表T1。

事务A：

T1.c1=0-> 更新T1 (c1=1) ---> Commit ---> 更新T1 (c1=2) ---> Commit->

事务B：

----- t1 --------------------> t2 ---------> t3 ---------------- >t4 --------->t5

假设事务B从 t1 时刻开始。

如果他在 t1 时刻看到 T1.c1=0 ，t2 时刻看到 T1.c1=1 ，在 t3 时刻看到 T1.c1=1 ，在 t4 时刻看到 T1.c1=2，在 t5 时刻看到 T1.c1=2，那么就是Read uncommitted。

如果他在 t1 时刻看到 T1.c1=0 ，t2 时刻看到 T1.c1=0 ，在 t3 时刻看到 T1.c1=1 ，在 t4 时刻看到 T1.c1=1，在 t5 时刻看到 T1.c1=2，那么就是Read committed。

如果他在 t2 时刻看到 T1.c1=0 ，t2 时刻看到 T1.c1=0 ，在 t3 时刻看到 T1.c1=1 ，在 t4 时刻看到 T1.c1=1，在 t5 时刻看到 T1.c1=1，那么就是Repeatable read。

如果他在 t2 时刻看到 T1.c1=0 ，t2 时刻看到 T1.c1=0 ，在 t3 时刻看到 T1.c1=0 ，在 t4 时刻看到 T1.c1=0，在 t5 时刻看到 T1.c1=0，那么就是Serializable read。

然后来回答一下墨天轮小助手的问题。

MySQL数据库是全面支持上面4种事务隔离度的，默认隔离度是“Repeatable read”。

使用命令：SET session TRANSACTION ISOLATION LEVEL xxxx; 可以修改事务隔离度。

(参数可以为：Read uncommitted，Read committed，Repeatable，Serializable)

那么ORACLE数据库呢？

ORACLE数据库只支持 Read committed 和 Serializable read 两种事务隔离度，默认隔离度是“Read committed”。参考文档：

Master Note: Oracle Transaction Management (Local) Overview (ドキュメントID 1506115.1)

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

Oracle database provides Read committed and Serializable isolation levels with "Read committed" as the default. In addition,

Oracle database also provides another isolation level - Read-only isolation level,

which is similar to the Serializable level but doesn't allow DML statements in the transaction(except for SYS).

These isolation levels can be set at the session level using the "SET TRANSACTION..." command

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

那这件事儿到底有啥实际影响呢？给大家讲一个这样的事例。

ORACLE数据库里有一种叫做“Materialized View”(物化视图)的Object。它提供一种可以自动或手动的把一个表的数据同步到另外一个表(物化视图)的方法。这个同步的过程就叫做“Refresh”。

如果有很多物化视图需要手动Refresh时，一个一个的刷新显然是比较麻烦的，于是ORACLE提供了把多个物化视图编组(Group)，然后一起Refresh的方法。

ORACLE数据库还有一种叫做“Foreign Key”(外键约束)的东西。它提供一种两个表之间的数据约束机能。具体是怎么约束的，相信大家都知道，这篇文章里不再赘述。我们只需要知道主表中没有的数据是不可能在外键表中存在的。

现在有这样一个场景：1.有两个具有外键约束的表：T1是主表，T2是外键表。

2.这两个表都有一个物化视图：MV10 和 MV20。

3.有一个处理给T1和T2插入记录。因为T1，T2之间存在外键约束，必须先对T1插入数据，Commit。然后对T2插入数据，Commit。

4.在处理3进行的同时，另外一个Session对物化视图Group MV10 ~ MV20进行Refresh。

大家想一下，在上面的场景中，有没有可能主表T1的物化视图 MV10不存在，而在外键表T2的物化视图 MV20中存在的记录呢？？

答案是：有可能。

因为Mview Group进行Refresh时，刷新顺序是Mview名的字母顺。在上面的场景中就是先刷新 MV1 ，再刷新 MV2参考文档：

Materialized Views (MVIEWs) Refresh Order Using "DBMS_SNAPSHOT.REFRESH" LIST Parameter; 9i Vs 10g and Higher Versions (ドキュメントID 1452382.1)

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

From 10g onwards, it refreshes in alphabetical order, i.e the refresh starts with "MVIEWa"

instead of "MVIEWi" and ends with "MVIEWk" instead of "MVIEWa".

Because of this, dependency MVIEWs are not getting correct data.

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

为了解释上面的原因，我还是画这样的图例：

事务3： ---> 更新T1 (insert into t1 values(1);) ---> Commit ---> 更新T2 (insert into t2 values(1);) ---> Commit --->

事务4： ----> Refresh MV10 ----> Refresh MV11 ----> Refresh MV12-----> …略… -----> Refresh MV19------> Refresh MV20-------->

因为ORACLE数据库的默认事务隔离度是“Read committed”。

也就是说刷新主表T1的物化视图 MV10时，主表T1并没有Commit，事务4看不见事务3的更新。等到刷新外键表T2的物化视图 MV20时，主表T1和外键表T2都已经Commit，事务4看见了事务3的更新。

所以，上面的现象看起来是不合理的(外键表的Mview里有数据而主表的Mview里没有数据)，但却是符合式样动作的(expected behavior)。

根据上面的事项，ORACLE最后也公布了官方文档。参考文档：

MVIEW of Child Table is Refreshed but MVIEW of Parent Table with Foreign Key Constraint is not Refreshed (ドキュメントID 2697569.1)

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

Cause

It's an expected behavior, as describe in Doc ID 1452382.1

If a group of materialized views are refreshing with "DBMS_SNAPSHOT.REFRESH" LIST Parameter,

Oracle will refresh the MVIEWs in alphabetical order.

So if the Data inserts and Mview refresh operations are at the same,

Because of the commit and refresh timing, MVIEW of Parent Table may be not Refreshed with some new data.

Reference:

Materialized Views (MVIEWs) Refresh Order Using "DBMS_SNAPSHOT.REFRESH" LIST Parameter; 9i Vs 10g and Higher Versions (Doc ID 1452382.1)

Solution

Don't Insert data to base table and Refresh the mview at the same time.

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

但是这个问题也可以通过设置事务4的SESSION隔离度来回避。

例如：

set transaction isolation level serializable;

※“事务”和“事物”太容易混淆了，以后需要多多注意。

特此感谢Gary的提醒！！