Oracle Database Isolation LEVEL

Transaction ISOLATION Level: the degree to which a transaction's modification of a database is isolated from another transaction in parallel.

The following three issues may exist when two concurrent transactions access the same row as the database table:

1, Fantasy read : Transaction T1 Reads a statement that specifies where condition, and returns the result set. At this point the transaction T2 inserts a new row of records that exactly satisfies the T1 's where condition. Then the T1 uses the same criteria to query again, the result set can see T2 inserted records, this new record is fantasy.

2, non-repeatable read : Transaction T1 read a row of records, immediately after the transaction T2 modified the T1 just read the record, and then T1 again query, found that the first read the record, which is called non-repeatable read.

3, dirty read : Transaction T1 updated a row of records, did not commit the changes, the T2 read the updated data, and then T1 to perform a rollback operation, cancel just the modification, so T2 read the row is invalid, that is, dirty data.

to address these issues, the SQL standard defines the following types of transaction isolation levels

Read uncommitted fantasy reads, non-repeatable reads, and dirty reads are allowed.

Read COMMITTED allows fantasy read, non-repeatable read, dirty read not allowed

Repeatable read allows fantasy reads, does not allow non-repeatable reads and dirty reads

SERIALIZABLE Fantasy Reading, non-repeatable reading and dirty reading are not allowed

The Oracle database supports both transaction isolation levels of Read COMMITTED and serializable. So Oracle does not support dirty reads

The default transaction isolation level defined by the SQL standard is serializable, but Oracle uses read COMMITTED by default

set isolation levels using SET TRANSACTION isolation level [READ uncommitted| READ committed| Repeatable read| SERIALIZABLE]

The following is an example of Oracle setting serializable isolation level:

The left is the transaction T1, the right side is the transaction T2, because the T2 level is serializable, so even if the transaction T1 after the data is submitted, the transaction T2 still cannot see the data submitted by T1, the fantasy read and non-repeatable reads are not allowed.

How can you see the new records of T1? The above T1 and T2 are executed concurrently, and the transaction T2 has started when the insert is executed T1, because the T2 level is serializable, so the dataset queried by T2 is the data for the database before the start of the T2 transaction. That is, the effect of the insert and update operations of the transaction T1 after the start of the transaction T2 does not affect the transaction T2. Now re-open a transaction T3 to see the T1 new record.

When the following events occur, the transaction begins:

1. Connect to the database and execute the first DML statement

2. After the end of the previous transaction, another DML statement was entered

Isolation levels (Isolation level) are the degree of isolation between transactions and transactions.

Obviously, the higher the transaction isolation, the worse the concurrency, the lower the performance, the lower the transaction isolation, the more concurrency, and the higher the performance.

The Ansi/iso SQL92 standard defines 4 levels of transaction isolation:

1. Serialization (Serializable)

The highest isolation level. All transactions in the system are performed one after the other. Therefore, there is no conflict between transactions.

2. Repeatable READ (REPEATABLE Read)

Data records read by an office are not allowed to be modified by other firms.

3. Read submitted (committed)

This level allows other transactions to modify the data records read by the current transaction, and after that transaction commits, the current transactions can see the modified data.

4. Reading uncommitted (READ UNCOMMITTED)

This level allows other transactions to modify the data records read by the current transaction, and when that transaction has not yet been committed, the current transactions can see the modified data. That is, dirty reads are allowed.

The transaction isolation level is different, executing a database query, the results are likely to surprise you, here is a summary of these situations:

1. Dirty Reading

Read data that has not yet been committed by another transaction.

2. Non-repeatable reading

The data record that the current transaction has read, modified or deleted by another transaction.

3. Phantom Read

Other transactions insert new data, and the current transaction queries the data before and after the transaction inserts the data with the same query criteria, resulting in a different number of data records.

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom read
Serialization of	N	N	N
REPEATABLE READ	N	N	Y
Read Committed	N	Y	Y
Read not submitted	Y	Y	Y

Oracle explicitly supports the serializable and read committed two transaction isolation levels defined in Ansi/iso SQL92.

At the same time, Oracle provides its own unique level of transaction isolation: Read Only.

So, it can be said that Oracle supports a total of 3 transaction isolation levels:

1. Serializable

2. Read Committed

3. Read Only

The default isolation level for Oracle is read committed.

Oracle Database Isolation LEVEL