Isolation levels for Oracle

2 transaction isolation levels supported by Oracle Read committed and Serializable

The Read committed is the default isolation level.

Isolation levels (Isolation level)

The isolation level defines the degree of isolation between transactions and transactions.

The isolation level and concurrency are contradictory: the higher the degree of isolation, the worse the concurrency of the database, and the lower the degree of isolation, the better the concurrency of the database.

L The Ansi/iso SQL92 standard defines the isolation level for some database operations:

L UNCOMMITTED read (READ UNCOMMITTED)

L Submit Read (Read Committed)

L Repeat read (repeatable read)

L Serialization (Serializable)

L The effects of isolation levels can be reflected through a number of phenomena. These phenomena are:

L Update loss (lost Update): When the system allows two transactions to update the same data at the same time, an update loss occurs.

L Dirty Read (dirty Read): When a transaction reads a change that another transaction has not committed, a dirty read is generated.

L non-duplicate read (nonrepeatable Read): The same query takes place more than once in the same transaction, and a different result set is returned at a time, because of changes or deletions made by other submitting transactions. (A transaction rereads data It has previously read and finds that another committed transaction has or modified th  E data. )

L Phantom (Phantom Read): The same query takes place more than once in the same transaction, and a phantom read occurs each time a different result set is returned due to an insert operation done by another submission transaction. (A transaction reexecutes a query returning a set of rows that satisfies a search condition and finds that another Committ  Ed transaction has inserted additional rows that satisfy the condition. )

L Below is the isolation level and its corresponding possible or impossible phenomenon

	Dirty Read	Nonrepeatable Read	Phantom Read
Read UNCOMMITTED	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
REPEATABLE READ	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible

Isolation levels for Oracle

Oracle provides the Read committed and serializable in the SQL92 standard, while providing read-only for non-SQL92 standards.

L Read Committed:

L This is the Oracle default transaction isolation level.

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

L ensures that no dirty reads are possible, but there may be non repetitive reads and illusions.

L Serializable:

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

L can only see changes committed by other transactions before the start of this transaction and changes made in this transaction.

L guarantee that no repetition reads and illusions will occur.

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

L If there is a transaction that was uncommitted at the beginning of the serializable transaction and the row to be modified by the serializable transaction was modified before the end of the serializable transaction, the serializable transaction will not read the changes. Therefore, an error occurred that could not serialize access. (Alternative interpretation: An error that cannot be serialized if there is another transaction between the beginning and end of the serializable transaction that modifies and commits the modification of the serializable transaction.) ）

L IF a serializable transaction contains data manipulation language (DML) that attempts to update any resource tha T may have been updated in a transaction uncommitted at the start of the serializable, (and the modification was later submitted without rollback), transaction The DML statement fails. The error returned is Ora-08177:cannot serialize access for this transaction.

L         oracle the information in the data block of the n transactions that recently performed the modification operation on the data row, The purpose is to determine whether a transaction that was uncommitted at the beginning of this transaction modifies the row that this transaction will modify. See English: Oracle permits a serializable transaction to modify a data row only if it can determine this prior changes to the RO W were made by transactions this had committed when the serializable transaction. To make this determination efficiently, Oracle uses control information stored in the "Data Block", indicates which rows In the block contain committed and uncommitted changes. In a sense, the blocks contains a recent history of transactions that affected each row into the block. The amount of history this is retained are controlled by the Initrans parameter of CREATE table and ALTER table. Under some circumstances, Oracle may have insufficient history information to determine whether a row has been updated by A "too recent" transaction. This can occur when many transactions concurrently modify the same data block, or does so in a very short period. You can avoid This situation by setting higher values of Initrans for tables that would experience many transactions updating the same B Locks. Doing so'll enable Oracle to allocate sufficient storage in the all blocks to record the history of recent transactions Accessed the block.

L The Initrans parameter:oracle stores control information in all data blocks to manage access by concurrent Tran Sactions. Therefore, if you are set the transaction isolation level to serializable, you must use the ALTER TABLE command to set Initran S to at least 3. This parameter would cause Oracle to allocate sufficient storage in the all blocks to record the history of recent transactions That's accessed the block. Higher values should be used to tables that would undergo many transactions the updating same.

L Read-only:

L Follow transaction-level read consistency and only see changes committed by other transactions before the start of this transaction.

L do not allow DML operations in this transaction.

L Read Only is a subset of serializable. They all avoid non repetitive reads and illusions. The difference is read only in read only, while DML operations can be performed in Serializable.

L Export with consistent = Y Sets the transaction to Read-only.

L Read committed and serializable differences and connections:

l          transaction 1 begins with transaction 2 and remains uncommitted. Transaction 2 wants to modify the rows that are being modified by transaction 1. Transaction 2 waits. If transaction 1 rolls back, transaction 2 (whether Read committed or serializable) makes the modifications it wants. If transaction 1 commits, make the changes it wants when transaction 2 is read Committed, and when transaction 2 is serializable, failure and error "Cannot serialize access" because transaction 2 does not see the changes submitted by transaction 1, and transaction 2 wants to make changes on the basis of a change in the transaction. See English: Both Read committed and serializable transactions use Row-level locking, and Both would wait if they try to change a Row updated by a uncommitted concurrent transaction. The second transaction that tries to update a given row waits for the other transaction to commit or roll its lock. If that is transaction rolls back, the waiting transaction (regardless of it isolation mode) can proceed to change the Previously locked row, as if the other transaction had not existed. However, if the other (blocking) transaction commits and releases their locks, a read committed transaction proceeds with it s intended update. A serializable transaction, however, fails with the error "Cannot SerialiZe access ", because the other transaction has committed a change that is made since the serializable transaction.

L Read committed and serializable can be used in Oracle parallel servers.

L about Set TRANSACTION read Write:read WRITE and Read committed should be the same. In terms of reading, they avoid dirty reads, but they do not achieve duplicate reading. Although there is no documentation that read write is consistent with Read committed in writing, it is clearly written with exclusive locks to avoid loss of updates. In the process of locking, if you encounter a locked resource that cannot be locked, you should wait instead of giving up. This is consistent with Read Committed.

L statement-level read consistency

Oracle guarantees statement-level read consistency, where a statement handles a dataset that is a dataset at a single point in time, the time at which the statement begins.

L A statement does not see the changes that were submitted after it was started.

L for DML statements, it does not see the modification that is made by itself, that is, the DML statement sees that it itself began to execute the previously existing data.

L transaction-level read consistency

L transaction-level read consistency ensures repeatable reading and ensures that no illusions are present.

L Set Isolation Level

L Set the isolation level of a transaction

L SET TRANSACTION Isolation level READ committed;

L SET TRANSACTION Isolation level SERIALIZABLE;

L SET TRANSACTION READ only;

L Set the isolation level for an additional session

L ALTER session SET Isolation_level SERIALIZABLE;

L ALTER Session SET Isolation_level READ committed;