Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.