Oracle database Transaction ISOLATION LEVEL introduction _oracle

With two concurrent transactions accessing the same row of database tables, the following three problems may exist:

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

2. non-repeatable reads: Transaction T1 reads a row of records, and immediately after the transaction T2 modifies the record that T1 just read, and then T1 Requery again, and finds that the record is different from the first read, which is called non-repeatable reading.

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

To address these issues, the SQL standard defines the following transaction isolation levels:

READ UNCOMMITTED is allowed for fantasy reading, unreadable reading, and dirty reading. 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 databases will not use this isolation level.

Read Committed allows for fantasy reading, non-repeatable reading, and no dirty reading allowed. A session can read only the results of an update that has been committed by another transaction, otherwise a wait occurs, but other sessions can modify the records read in this transaction without waiting for the end of the transaction, and it is clear that, at this isolation level, two of the same read operations in one transaction may have different results.

Repeatable read allows for fantasy reading and does not allow unreadable and dirty reads. 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 Fantasy, unreadable, and dirty reading are not allowed. 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. The default transaction isolation level defined by the SQL standard is serializable.

Ii. isolation levels and implementation mechanisms in Oracle:

The Oracle database supports both the transaction isolation levels of Read committed and serializable. Therefore, Oracle does not support dirty reads, which means that one session is not allowed in Oracle to read data modification results that are not committed by other transactions, thereby preventing incorrect reads due to transaction rollback .

Oracle rollback segment, when you modify data records, the results of these records are saved to the rollback or undo segment. The Oracle 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 configuration is the Read Committed isolation level (also known as statement-level isolation) at which a transaction is performing a DML operation on a table, while another session performs a read operation on the table's records. 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.

Oracle's SERIALIZABLE Isolation level (also known as transaction-level isolation), read operations in transactions can only read the data results that were committed before the transaction started. If another transaction is performing a modification to the record while it is being read, Oracle will look for the corresponding unmodified record in the rollback segment or the undo segment (and the record of the rollback segment or the undo segment before the transaction where the read operation occurs), and the read operation will not wait for the corresponding record to be updated.

Set the isolation level use set TRANSACTION isolation levels [READ uncommitted| READ committed| Repeatable read| SERIALIZABLE]

The following is an example of an Oracle Setup 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 does not see the data submitted by the T2 after the data is submitted, the fantasy and non repeatable reads are not allowed.

How can you find out what's new in T1 's record? The above T1 and T2 are concurrent executions, and the transaction T2 is already started when the T1 executes the insert, because the T2 level is serializable, so the data set that the T2 queries is the data of the database before the start of the T2 transaction. That is, the impact of the insert and update operations that transaction T1 after the start of a transaction T2 does not affect transaction T2. Now reopen a transaction T3 to see the T1 new record.

The transaction begins when the following events occur:

1, connect to the database, and execute the first DML statement
2, the previous transaction ended, and entered another DML statement