Oracle transaction isolation level

1. Transaction Introduction

Transactions in Oracle11g are automatically started implicitly and do not need to be executed by the user. However, for the end of the transaction, You need to perform the specified operation. Generally, Oracle considers a transaction to be over in the following cases.

(1) execute the Commit statement to submit the transaction

(2) execute the Rollback statement to cancel the transaction.

(3) execute a Data Definition Statement. If the statement is successfully executed, the oracle system will automatically execute the commit command; otherwise, the system will automatically execute the Rollback command.

(4) execute a Data Control Command. After the statement is executed, the oracle system will automatically execute the commit command.

(5) normally disconnect the database and exit the sqlplus environment normally, the oracle system will automatically execute the commit command; otherwise, the system will automatically execute the Rollback command.

2. Commit statement

Commit indicates that the transaction is committed. The oracle system processes the transaction in the following order:

(1) first, record that the current transaction has been committed within the rollback segment, and declare a unique system number (SCN) to uniquely identify the transaction.

(2) then start the log writing process (LGWR) in the background, and write the data in the redo log buffer and the SCN of the current transaction in the SGA area to the redo log file.

(3) the Oracle server then releases the system resources used for transaction processing.

(4) Finally, a notification is displayed, indicating that the transaction has been successfully committed.

3. Rollback statement

Rollback indicates that the transaction is canceled. The oracle system processes the transaction in the following order:

(1) first, use the data in the rollback segment to cancel the changes made to the database.

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

(3) Finally, a notification is displayed, indicating that the transaction has been successfully canceled.

4. Save points

Oracle not only allows the rollback of the entire uncommitted transaction, but also allows the rollback of a part of the transaction, which can be completed by the "save point. During transaction execution, you can separate a long transaction into several parts by creating a storage point. In this way, the user can selectively roll back to a save point, and all operations after the save point will be revoked.

2. Basic Syntax:

Savepoint

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

Rollback to save the vertex name or rollback -- undo all transactions

2 Note:

(1) A transaction can store multiple storage points and cannot roll back to the same storage point twice. Once it is rolled back once, the storage point becomes invalid.

(2) The cost of saving points is resource overhead.

(3) Once a transaction is committed, it cannot be rolled back to any storage point.

5. transaction isolation level

2 Note: The transaction isolation level is defined by the International Standardization Organization, and not all DBMS follow.

2. transaction isolation level defines the degree of isolation between transactions. Isolation levels and concurrency are in conflict. The higher the degree of isolation, the worse the database concurrency; the lower the degree of isolation, the better the database concurrency.

2 errors caused by concurrent operations

(1) dirty read: dirty reads are generated when one transaction reads the modifications not committed by another transaction. (This is not found in oracle)

(2) nonrepeatable read: The same query is performed multiple times in the same transaction. Because of the modifications and deletions made by other commit firms, different result sets are returned each time, in this case, it cannot be read repeatedly.

For example:

Insert into emp (empno, ename) values (9000, 'lilim ')

Sqlplus 2: select * from emp;

Sqlplus 1: delete from emp where empno = 9000

Commit

Sqlplus 2: select * from emp; -- two queries have different records

(3) phantom read: The same query is performed multiple times in the same transaction. Because of the insert operations performed by other committed transactions, different result sets are returned each time. In this case, phantom read occurs.

For example:

Sqlplus 2: select * from emp;

Sqlplus 1: Insert into emp (empno, ename) values (9999, 'lily ');

Commit;

Sqlplus 2: select * from emp; -- two queries have different records

2. Concept of transaction isolation level: isolation level defines the degree of isolation between transactions

Isolation level	Dirty read	Non-repeated read	Phantom read
Read uncommitted)	√	√	√
Read committed)	×	√	√
Repeatable read)	×	×	√
Serializable)	×	×	×

6. Oracle transaction isolation level

Oracle provides read committed and serializable in the SQL92 standard, and non-SQL92 standard read-only. Read-only is a subset of serializable, which avoids non-repeated read and phantom read. The difference is that SQL statements cannot be used for DML operations in read-only.

2 Oracle readcommitted description

(1) This is the default transaction isolation level of oracle.

(2) ensure no dirty read: but there may be non-repeated and phantom read.

2 Oracle serializable description

(3) make the transaction look like a sequential execution

(4) only the changes committed by other transactions before the start of the transaction and the changes made in the event service can be seen.

(5) ensure no dirty reads, non-repeated reads, or Phantom reads

(6) read consistency provided by read-only and DML operations allowed

2 Oracle transaction isolation level settings

(1) set the isolation level of a transaction

Set transaction isolation level readcommitted; -- (default)

Set transaction isolation levelserializable; -- expensive

Set transaction read only;

(2) set the isolation level for the entire session

Alter session set isolation_level = serializable;

Alter session set isolation_level = readcommitted;

7. serializable

2 Introduction: serializable means to make the transaction look like a sequential execution. A transaction takes a snapshot of all the data in the database before it starts. During the transaction execution, only the data in the snapshot and the updates made in the event service can be seen.

Note: sys users cannot run Set transaction isolation level serializable.

2 Example 1:

Sqlplus 2: Settransaction isolation level serializable; -- use commit to ensure that it is the first statement of the transaction.

Select * from emp;

Sqlplus 1: select * from emp;

Delete from e-mapreduce where e-mapreduce = 9999;

Commit;

Select * from emp;

Sqlplus 2: Select * from emp;

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; -- there is no phantom read, and the insert record does not work.