Oracle — transaction isolation level

Time:2021-6-10

Yesterday, I saw a question about the transaction isolation of MySQL database from the assistant of Mo Tianlun. I suddenly thought of a thing about the transaction isolation of Oracle database that I had met before. I thought it could help us deepen our understanding of the database transaction isolation, so I sorted it out and shared it with you.

Let’s start with transaction isolation.
Transaction isolation models is not a concept proposed by individual databases, but an international standard (ANSI and ISO / IEC). All databases (not only traditional relational databases, such as oarcle, mysql, SQL server, but also NoSQL databases, such as tidb, oceanbase, etc.) should be designed on this standard. The difference lies in which (or those) schema they support. It identifies the extent to which a database system can guarantee “read consistency”.

Let’s use a simple example to illustrate the transaction isolation models. There are two transactions, transaction a is responsible for updating table t1, and transaction B is responsible for viewing table t1.

Transaction a:
T1. C1 = 0 – > Update T1 (C1 = 1) — > commit — > Update T1 (C1 = 2) — > commit – >

Transaction B:
—– t1 ——————–> t2 ———> t3 —————- >t4 ———>t5

Suppose transaction B starts at T1.
If he sees T1. C1 = 0 at T1, T1. C1 = 1 at T2, T1. C1 = 1 at T3, T1. C1 = 2 at T4, and T1. C1 = 2 at T5, then it isRead uncommitted
If he sees T1. C1 = 0 at T1, T1. C1 = 0 at T2, T1. C1 = 1 at T3, T1. C1 = 1 at T4, and T1. C1 = 2 at T5, then it isRead committed
If he sees t1.c1 = 0 at T2, t1.c1 = 0 at T2, t1.c1 = 1 at T3, t1.c1 = 1 at T4, and t1.c1 = 1 at T5, then it isRepeatable read
If he sees t1.c1 = 0 at T2, t1.c1 = 0 at T2, t1.c1 = 0 at T3, t1.c1 = 0 at T4, and t1.c1 = 0 at T5, then it isSerializable read

Then come to answer the question of Mo Tianlun’s assistant.
Mysql database fully supports the above four transaction isolation degrees, and the default isolation degree is “repeatable read”.

Oracle -- transaction isolation level

Command: set session transaction isolation level XXXX; Transaction isolation can be modified.
(parameters can be read uncommitted, read committed, repeatable, serializable)

What about Oracle database?

Oracle database only supports read committed and serializable read transaction isolation. The default isolation is “read committed”.

Reference documents:
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
------------------------------------------------------------------------------------------------------------------

So what’s the real impact of this? Let me tell you an example of this.

In Oracle database, there is a method called“Materialized View”Object of materialized view. It provides a way to automatically or manually synchronize data from one table to another (materialized view). This synchronization process is called synchronization“Refresh”。

If there are many materialized views that need to be manually refreshed, it is obviously troublesome to refresh one by one, so Oracle provides the ability to group multiple materialized views(Group)And then refresh the method together.

Oracle database also has a kind of“Foreign Key”(foreign key constraint). It provides a data constraint function between two tables. I believe we all know how to restrict it. I won’t repeat it in this article. We only need to know that the data not in the main table cannot exist in the foreign key table.

Now there is a scene like this:

1. There are two tables with foreign key constraints: T1 is the main table and T2 is the foreign key table.
2. Both tables have a materialized view: MV10 and mv20.
3. There is a process for T1 and T2 to insert records. Because there is a foreign key constraint between T1 and T2, you must first insert data into T1 and commit. Then insert data into T2, commit.
4. While processing 3, another session refreshes the materialized view group MV10 ~ mv20.

Let’s think about it. In the above scenario, is it possible that MV10, the materialized view of the main table t1, does not exist, while mv20, the materialized view of the foreign key table t2, has records??

The answer is: probably.
Because when Mview group refreshes, the refresh order is the alphabetic sequence of Mview name. In the above scenario, first refresh MV1, then refresh MV2

Reference documents:
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.
------------------------------------------------------------------------------------------------------------------

In order to explain the above reasons, I still draw the following legend:
Transaction 3: — > Update T1 (insert into T1 values (1);) — > Commit — > Update T2 (insert into T2 values (1);) — > Commit —>
Transaction 4: — > refresh MV10 — > refresh mv11 — > refresh mv12 — >… Omitted… — > refresh mv19 — > refresh mv20 — >

Because the default transaction isolation of Oracle database is “read committed”.
That is to say, when the materialized view MV10 of main table t1 is refreshed, there is no commit in main table t1, and transaction 4 cannot see the update of transaction 3. When the materialized view mv20 of the foreign key table t2 is refreshed, both the main table t1 and the foreign key table t2 have committed, and transaction 4 sees the update of transaction 3.

Therefore, the above phenomenon seems unreasonable (there is data in the Mview of the foreign key table and no data in the Mview of the main table), but it is consistent with the style action(expected behavior)。

According to the above, Oracle finally released the official document.

Reference documents:
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.
------------------------------------------------------------------------------------------------------------------

But this problem can also be avoided by setting the session isolation of transaction 4.

For example:
set transaction isolation level serializable;

※“affair”And“thing”It’s too easy to confuse. We need to pay more attention in the future.
Thank you for your reminding!!