Transaction isolation level of oracle
This article covers the level of transaction isolation in oracle.
The ANSI SQL standard defines four isolation levels:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Three phenomena allowed or not allowed by the isolation level
Dirty read (dirty reading): can read unsubmitted data.
Nonrepeatable read (non-repeatable): missing updates may occur.
Phantom read (Phantom Reading): the data that has been read will not change, and there may be more data that meets the conditions than before.
ANSI isolation level
Isolation level dirty reading can not be repeated phantom reading
READ UNCOMMITTED allow
READ COMMITTED does not allow permission
Whether REPEATABLE READ does not allow permission
SERIALIZABLE does not allow
The following part of the test example.
1 、 READ UNCOMMITTED
Dirty reading, not repetitive reading and phantom reading are allowed. If you want to change the isolation level here, report an error directly.
SQL > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
*
ERROR at line 1:
ORA-02179: valid options: ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}
2 、 READ COMMITTED
The default isolation option for oracle. Transactions can only read committed data in the database. Dirty reading is not allowed.
Session 1:
SQL > conn test/test
Connected.
SQL > SET TRANSACTION
2 ISOLATION LEVEL
3 READ COMMITTED
Transaction set.
SQL > select * from t
X
-
one
Session 2:
SQL > conn test/test
Connected.
SQL > SET TRANSACTION
2 ISOLATION LEVEL
3 READ COMMITTED
Transaction set.
SQL > select * from t
X
-
one
Session 1:
SQL > update t set Xero2
1 row updated.
SQL > insert into t values (3)
1 row created.
SQL > commit
Commit complete.
SQL > select * from t
X
-
two
three
Session 2:
SQL > select * from t
X
-
two
three
It is verified that unrepeatable and phantom reads are allowed.
3 、 REPEATABLE READ
Can give a correct result to avoid losing updates. That is, dirty reading and repeated reading are not allowed, and phantom reading is allowed.
4 、 SERIALIZABLE
The highest degree of isolation. That is, dirty reading, repeated reading and phantom reading are not allowed.
Session 1
SQL > select * from t
X
-
one
Session2:
SQL > select * from t
X
-
one
SQL > SET TRANSACTION
2 ISOLATION LEVEL SERIALIZABLE
Transaction set.
SQL > select * from t
X
-
one
Session1:
SQL > insert into t values (2)
1 row created.
SQL > commit
Commit complete.
SQL > select * from t
X
-
one
two
Session 2:
SQL > select * from t
X
-
one
SQL > update t set Xero2
Update t set Xero2
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
= "since the transaction, session1 has added a row of 2 records, and session2 changes have reported errors.
Summary:
1.oracle only allows the isolation level to be changed to SERIALIZABLE and READ COMMITTED, and the default is READ COMMITTED.
two。 When the isolation option (SERIALIZABLE) is set to the highest level, ORA-08177 may be encountered within a transaction.
End
