Oracle Transaction ISOLATION Level __oracle

Represents the transaction isolation level many editions of this East translation AH

Or excerpt from the Internet and SQL Server comparison will be more convenient to understand it

In the SQL92 standard, the transaction isolation level is divided into four categories: READ UNCOMMITTED, Read committed, read repeatable, Serializable

Where READ uncommitted and read Committed are statement-level, and read repeatable and serializable are for transaction-level.

Statements that set TRANSACTION isolation levels in Oracle and SQL Server are the same, using the SQL92 standard syntax, which is:

Set Transaction Isolation Level Read committed

　isolation levels and implementation mechanisms in 1.SQL server

All of these four isolation levels are available in SQL Server.

Read UNCOMMITTED

A session can read the results of uncommitted updates from other transactions, and if the transaction ends with a rollback, the read result may be incorrect, so most database applications will not use this isolation level.

Read committed

This is the default isolation level for SQL Server, and a transaction set to this isolation level can only read the result of an update that has been committed by another transaction, otherwise the wait occurs, but other sessions can modify the records read in this transaction without waiting for the end of the transaction, and obviously, at this isolation level, Two of the same read operations in a transaction may have different results.

Read Repeatable

In a transaction, if there is no action to add records between read operations with the same conditions two times, and no other update operation results in an increase in the number of records under this query condition, the two reads are the same. In other words, the record being read for the first time in a transaction is guaranteed not to change during this transaction. SQL Server implements this isolation level by locking records that are read during the entire transaction, so that other sessions cannot modify the records read in the transaction until the end of the transaction, but wait for the transaction to end, but SQL Server does not prevent other sessions from adding records to the table. Nor does it prevent other sessions from modifying other records.

Serializable

In a transaction, the result of a read operation is a record that has been committed by other transactions prior to the start of the transaction, and SQL Server implements this isolation level by locking the table during the entire transaction. At this isolation level, all DML operations on this table are not allowed, that is, waiting for the transaction to end, thus ensuring that the results of the two read operations in one transaction are certainly the same.

　isolation level and implementation mechanism in 2.Oracle

In Oracle, there is no READ UNCOMMITTED and repeatable read isolation level so that one session is not allowed in Oracle to read data modification results that are not committed by other transactions, thereby avoiding read errors that occur due to transaction rollback. The Read committed and serializable levels in Oracle are similar in meaning to SQL Server, but they are implemented in a very different way.

In Oracle, there are so-called rollback segments (oracle9i versions) or undo segments (Oracle9i versions), and Oracle, when modifying data records, deposits the results of these records before they are modified to the rollback or undo section, because of this mechanism, Oracle's implementation of the transaction isolation level is distinct from SQL Server. In Oracle, the read operation does not hinder the update operation, nor does the update operation hinder the read operation, so that, at various isolation levels in Oracle, the read operation will not wait for the update transaction to end and the update operation will not wait for the read operation in the other transaction. This is also an advantage of Oracle transaction processing.

The Oracle default setting is the Read Committed isolation level (also known as statement-level isolation) at which a transaction is being DML on a table, and another session reads the record of the table. Oracle will read the record before retrieving the updates that are stored in the roll or undo segment, rather than waiting for the end of the update transaction to be the same as SQL Server.

At the Serializable isolation level (also known as transaction-level isolation), a read operation in a transaction can only read the data results that have been committed before the transaction started. If the record is being modified by another transaction while it is being read, Oracle will look for the corresponding unchanged record in the rollback segment or the undo segment (and the record of the rollback or undo segment before the transaction where the read operation occurs), and the read operation will not wait for the corresponding record to be updated.