ORACLE Database Transaction isolation level

When two concurrent transactions access the same row in the database table at the same time, there may be three problems:

1,Fantasy read: Transaction T1 reads a statement that specifies the where condition and returns the result set. At this time, transaction T2 inserts a new row, which meets the where condition of T1. Then T1 uses the same condition to query again. The records inserted by T2 are displayed in the result set. This new record is an illusion.

2,Cannot be read repeatedly: Transaction T1 reads a row of records, and then transaction T2 modifies the records that T1 has just read. Then T1 queries again and finds that the records read for the first time are different. This is called non-repeated read.

3,Dirty read: Transaction T1 updates a row of records and has not submitted any modifications. This T2 reads the updated data, and T1 executes the rollback operation to cancel the modification, therefore, the row read by T2 is invalid, that is, dirty data.

1. To solve these problems, the SQL standard defines the following transaction isolation levels:

Read uncommitted, unrepeatable, and dirty reads are allowed.A session can read the uncommitted UPDATE results of other transactions. If the transaction ends with a rollback, the read results may be incorrect, therefore, most databases do not use this isolation level.

Read committed allows fantasies and repeated reads. Dirty reads are not allowed.One sessionOnly the updated results submitted by other transactions can be read. Otherwise, a wait occurs, but other sessions can modify the records read in this transaction without waiting for the transaction to end. Obviously, at this isolation level, two identical read operations in a transaction may have different results.

Repeatable read allows phantom READ and does not allow repeated and dirty READ.In a transaction, if no record is added between two read operations with the same condition, and there are no other update operations, the number of records increases under this query condition, the two read results are the same. In other words, the record read for the first time in a transaction will not be changed during this transaction.SQL Server locks the read records throughout the transaction to achieve this isolation level. In this way, before the transaction ends, other sessions cannot modify the records read in the transaction, however, SQL Server can only wait for the transaction to end, but does not prevent other sessions from adding records to the table or modifying other records.

SERIALIZABLE fantasy reads, non-repeated reads, and dirty reads are not allowed.In a transaction, the read operation results in records committed by other transactions before the transaction starts. SQL Server implements this isolation level by locking the table throughout the transaction. At this isolation level, all DML operations on this table are not allowed, that is, to wait until the transaction ends, this ensures that the results of the two read operations in a transaction are certainly the same.The default transaction isolation level defined by the SQL standard is SERIALIZABLE.

Ii. isolation level and implementation mechanism in Oracle:

The Oracle Database supports read committed and SERIALIZABLE transaction isolation levels. Therefore, Oracle does not support dirty reads. That is, Oracle does not allow a session to read the uncommitted data modification results of other transactions, which prevents incorrect reads due to transaction rollback..

Oracle rollback segments: when modifying data records, the results before these records are modified are stored in the rollback segments or undo segments. Oracle read operations do not impede update operations, and update operations do not impede read operations. In this way, under various isolation levels in Oracle,The read operation does not wait until the update transaction ends.,The update operation does not wait because of the read operation in another transaction.This is also an advantage of Oracle transaction processing.

The default configuration of Oracle is Read Committed isolation level (also called statement-level isolation). At this isolation level, if a transaction is performing DML operations on a table, at this time, another session reads the records of this table, Oracle will read the records stored in the rollback segment or the Undo segment before the update, instead of waiting for the end of the update transaction like SQL Server.

OracleSerializable isolation level (also known as transaction-level isolation). Read operations in a transaction can only read the data results submitted before the transaction starts. If another transaction is modifying the record at the time of reading, then Oracle will find the original unmodified records in the rollback segment or undo segment (and store the records in the rollback segment or undo segment before the transaction where the read operation is located starts ), at this time, the read operation will not wait because the corresponding record is updated.

SET the isolation level using set transaction isolation level [read uncommitted | read committed | repeatable read | SERIALIZABLE]

The following is an example of setting the SERIALIZABLE isolation level in oracle:

The left side is transaction T1, and the right side is transaction T2. Because the T2 level is SERIALIZABLE, even after transaction T1 commits data, transaction T2 still cannot see the data submitted by T1, fantasy and non-repeated read are not allowed.

Then how can we check the newly added records of T1? The above T1 and T2 are executed concurrently. When T1 executes insert, the transaction T2 has started, because the T2 level is SERIALIZABLE, therefore, the dataset queried by T2 is the data of the database before the start of the T2 transaction. That is, the impact of insert and update operations on transaction T1 after transaction T2 does not affect transaction T2. Now you can re-start transaction T3 to see the newly added records of T1.

When the following events occur, the transaction starts:

1. Connect to the database and execute the first DML statement.
2. After the previous transaction ends, another DML statement is entered.