Oracle implementation at TRANSACTION ISOLATION level

The ANSI-defined transaction ISOLATION level standard (http://blog.csdn.net/u010415792/article/details/8977635) is mentioned in the previous article, but different vendors implement the same approach. This article mainly introduces the transaction isolation level implemented by Oracle, all of which are dependent on undo, and the higher the isolation level, the greater the likelihood of ORA-01555 errors.

1, Read committed (Default)

The lowest isolation level for Oracle is read committed, which has the following characteristics:

This is the default transaction isolation level for Oracle.

Each statement in a transaction complies with the statement-level read consistency.

It is guaranteed not to be dirty, but there may be non repetitive reads and illusions.

Some people may wonder, how is the lowest isolation level of Oracle not READ UNCOMMITTED? Because Oracle has undo, it is born to read and write without blocking, so in Oracle, there will be no dirty reading at all.

Here's an explanation of "statement-level read consistency," which means that when a statement starts executing, it sees both the impact of the data before the transaction, and the impact of the committed transaction on the data when the statement starts executing. Look at the following example:

1 Empty the test table data:

sql> Delete from test;

1 row deleted.

Sql> commit;

Commit complete.

2) Set the isolation level of Session1 to read committed:

Session1> ALTER session set Isolation_level=read committed;

Session altered.

3 Insert a piece of data in the Session1 (uncommitted):

session1> INSERT INTO test values (1);

1 row created.

4 in Session1 query:

Sql> select * from test;

Id

-------- --

1

You can see the data that was inserted before this transaction in Session1.

5 Insert a piece of data in the Session2 and submit:

session2> INSERT INTO test values (2);

1 row created.

Session2> commit;

Commit complete.

6) in the Session1 query:

Session1> select * from test;

Id

----------

2

1

The submitted data in the Session2 can be seen in the Session1.

In this isolation level, it is possible to see the results of other transactions on data operations as long as the other transaction commits (the immediate other transaction begins after this transaction), so it cannot prevent non repeat reads and Phantom reads.

2, Serializable

The next level of Oracle is not repeatable Read, but serializable, which has the following characteristics:

Simply put, the serializable is to make the transaction appear to be executed sequentially, one after another.

You can only see changes committed by other transactions before the start of this transaction and changes made in this transaction (transactional-level consistency).

Ensure that no duplicate reads and illusions are present.

The Serializable isolation level provides read consistency (transactional-level read consistency) provided by the read-only firm while allowing DML operations.

"Transactional-level consistency" means that by this isolation level, the results of the query have been determined at the time the transaction was started, and the changes to the data by other transactions since the transaction started have no effect on the query for this transaction. Look at the following example:

1 Empty the test table data:

sql> Delete from test;

2 rows deleted.

Sql> commit;

Commit complete.

2) Set the isolation level of session1 to serializable:

Session1> alter session set isolation_level=serializable;

Session altered.