Oracle thing Isolation Level

1. Introduction of affairs

Transactions in oracle11g are implicitly auto-initiated and do not require the user to display execution start transaction statements. However, for the end-of-transaction processing, the user is required to do the specified action. Typically, Oracle considers a transaction to be complete under the following circumstances.

(1) Execute COMMIT statement COMMIT TRANSACTION

(2) Execute ROLLBACK statement undo Transaction

(3) Executes a data definition statement. If the statement executes successfully, the Oracle system automatically executes the commit command, otherwise the rollback command is automatically executed.

(4) A Data Control command is executed, and the Oracle system automatically executes the commit command when the statement is executed.

(5) Normal disconnected database connection, normal exit Sqlplus environment, then the Oracle system will automatically execute the commit command, otherwise, the system will automatically execute the rollback command.

2. Commit statement

The commit represents the commit transaction, which is handled internally by the Oracle system in the following order:

(1) First record the current transaction is committed within the rollback segment, and declare a unique system number (SCN) to uniquely identify the transaction.

(2) The log write process (LGWR) is then started in the background, and the data in the redo log buffer of the SGA area and the SCN of the current transaction are written to the redo log file.

(3) The Oracle server then begins releasing the system resources used by the transaction.

(4) Finally display the notification, telling the user that the transaction has been successfully submitted.

3. Rollback statement

The rollback represents the undo transaction, which is handled internally by the Oracle system in the following order:

(1) First use the data in the rollback segment to undo the changes made to the database.

(2) The Oracle background server process then releases the system resources used by the transaction.

(3) Finally display the notification, telling the user that the transaction has been revoked successfully.

4. Save Point

Oracle not only allows for the rollback of the entire uncommitted transaction, but also allows for part of the fallback transaction, which can be done through a "savepoint". During the execution of a transaction, the user can separate a longer transaction into several parts by establishing a savepoint. This allows the user to selectively fall back to a savepoint, and the actions after that SavePoint will be revoked.

2 Basic Syntax:

SavePoint Save Point Name

..................

Rollback to save point name or Rollback--undo all transactions

2 Description:

(1) Multiple savepoint can be saved in one transaction, not 2 times back to the same savepoint, once back, the savepoint is invalidated.

(2) The cost of setting the SavePoint is resource-cost.

(3) Once a transaction has been committed, it cannot be rolled back to any savepoint.

5. Transaction ISOLATION LEVEL

2 Description: The transaction isolation level is defined by the International Organization for Standardization, not all DBMS compliance.

2 Introduction: The transaction 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 poorer the concurrency of the database, and the lower the degree of isolation, the better the concurrency of the database.

2 errors caused by concurrent operations

(1) Dirty reads (dirty read): Dirty reads occur when one transaction reads a modification that has not yet been committed by another transaction. (No such phenomenon in Oracle)

(2) Non-repeatable read (Nonrepeatable Read): The same query occurs more than once in the same transaction, and each time a different result set is returned because of the modifications and deletions made by the other commit transactions, non-repeatable reads occur at this time.

For example:

Insert into EMP (empno,ename) VALUES (9000, ' Li Ming ')

Sqlplus 2:select * from EMP;

Sqlplus 1:delete from EMP where empno=9000

Commit

Sqlplus 2:select * from EMP; --Two queries with different records

(3) Phantom Read (Phantom Read): The same query occurs more than once in the same transaction, and the phantom reads occur each time a different result set is returned because of an insert operation done by another commit.

For example:

Sqlplus 2:select * from EMP;

Sqlplus 1:insert into EMP (empno, ename) VALUES (9999, ' Li Liang ');

Commit;

Sqlplus 2:select * from EMP; --Two queries with different records

2 The concept of the transaction ISOLATION level: The isolation level defines the degree of isolation between transactions and transactions

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom reading
Unread (Read UNCOMMITTED)	√	√	√
Read submitted (committed)	X	√	√
REPEATABLE READ (Repeatable Read)	X	X	√
Serializable (Serializable)	X	X	X

6. Oracle's transaction ISOLATION LEVEL

Oracle provides read committed and serializable in the SQL92 standard, as well as read-only for non-SQL92 standards. Read-only are subsets of serializable that avoid non-repeatable reads and Phantom reads, except that SQL is not allowed to perform DML operations in Read-only.

2 readcommitted description for Oracle

(1) This is the default transaction isolation level for Oracle

(2) guaranteed not to be dirty read: but may appear non-repeatable read and Phantom read

2 Serializable description for Oracle

(3) Making a transaction look like a sequential execution

(4) You can only see changes that were committed by other transactions and changes made in this transaction before the start of the transaction

(5) Ensure that no dirty reads, non-repeatable reads and Phantom reads are possible

(6) Provides read consistency provided by the read-only firm while allowing DML operations

2 Transaction Isolation level settings for Oracle

(1) Setting the isolation level for a transaction

Set transaction ISOLATION Level readcommitted; --(default)

Set transaction Isolation levelserializable; -Expensive

Set transaction Read only;

(2) Setting the isolation level for the entire session

Alter session Set isolation_level=serializable;

Alter session Set isolation_level=readcommitted;

7, can be serialized

2 Description: Serializable is what makes a transaction look like a sequential execution. The transaction takes a snapshot of all the data in the database before it starts, and only the data in the snapshot and the updates made in this transaction are visible during the execution of the transaction.

NOTE: SYS user cannot run SET TRANSACTION isolation level serializable.

2 Example 1:

Sqlplus 2:settransaction isolation level serializable; --commit to ensure it is the first statement of the transaction

Select * from EMP;

Sqlplus 1:select * from EMP;

Delete from EMP where empno=9999;

Commit;

Select * from EMP;

Sqlplus 2:select * from EMP; --no non-repeatable read, delete record does not work for it

2 Example 2:

Sqlplus 2:settransaction isolation level serializable;

Select * from EMP;

Sqlplus 1:select * from EMP;

Insert into EMP (empno, ename) VALUES (9998, ' Wang Ming ')

Commit;

Select * from EMP;

Sqlplus 2:select * from EMP; --No phantom reading occurred, insert record does not work on it